磐维数据库的权限使用

一、磐维数据库介绍

磐维数据库，简称"PanWeiDB"。是中国移动信息技术中心首个基于中国本土开源数据库打造的面向ICT基础设施的自研数据库产品。其产品内核能力基于华为openGauss开源软件，并进一步提升了系统稳定性。

磐维数据库具有高性能、高可靠、高安全、高兼容等特点，能够满足复杂多变的业务需求。磐维数据库提供了自动化、流程化的解决方案，实现了一键式数据迁移。这种高效的数据迁移方式不仅提高了迁移数据的效率，也降低了操作难度，为用户带来了极大的便利。

二、磐维数据库的权限

数据库中存储着大量重要数据和敏感信息，数据库安全尤为重要，合理的权限分配可以有效地保护数据库系统免受各种威胁的侵害，同时权限的分配需要满足最小化原则。本文将重点介绍磐维数据库中权限的使用。

1、权限分类

在磐维数据库中权限分为两种：系统权限和对象权限。
（1）系统权限：系统权限又称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN、MONADMIN、OPRADMIN、POLADMIN和LOGIN等。系统权限一般通过CREATE/ALTER ROLE语法来指定。其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。但系统权限无法通过ROLE和USER的权限被继承，也无法授予PUBLIC。
系统权限及功能说明如下：

（2）对象权限：指在数据库、模式、表、视图、函数等数据库对象上执行操作的权限，不同的权限与不同的对象类型关联，比如表空间的创建、修改、删除，数据库的连接、创建、删除权限，表的增删改查权限，函数的执行权限等。
数据库对象创建后，进行对象创建的用户就是该对象的所有者。数据库系统管理员具有与对象所有者相同的权限。也就是说对象创建后，默认只有对象所有者或者系统管理员可以查询、修改和销毁对象，以及通过GRANT将对象的权限授予其他用户。
对象权限可以通过角色（ROLE）被继承，这样方便用户将单个的权限打包成一个角色进行权限管理。
对象权限及权限说明如下：

2、权限的授予和回收

（1）系统权限授权/回收：使用CREATE USER、CREATE ROLE、ALTER USER、ALTER ROLE命令授权/回收系统权限。
下面示例是CREATE USER时对用户授权/回收SYSADMIN系统权限：
查看create user的语法，其中option子句中包括了系统权限列表

postgres=# \h create user
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER [IF NOT EXISTS] user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [ EXPIRED ] | DISABLE
 };

where option can be:
{SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {SSOADMIN | NOSSOADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
| RESOURCE POOL 'respool'

创建testuser用户时授予sysadmin权限：

postgres=# create user testuser with sysadmin  IDENTIFIED BY 'test%1234';
CREATE ROLE

查看testuser用户的权限

回收sysadmin权限

postgres=# alter user testuser nosysadmin;
ALTER ROLE

再次查看testuser用户的权限

（2）对象权限授权/回收：使用GRANT/REVOKE命令授予/回收对象权限。
下面示例是对testuser2用户授权/回收testtab表的增删改查对象权限：
查看testuser模式下的表
postgres=> \dt
List of relations
Schema | Name | Type | Owner | Storage
----------±--------±------±---------±-----------------------------------------------
testuser | testtab | table | testuser | {orientation=row,compression=no,fillfactor=80}
(1 row)
把testuser.testtab表的 insert,update,delete,select权限授予testuser2用户

postgres=> grant insert,update,delete,select  on table testuser.testtab to testuser2;
GRANT

查看testuser2用户的权限

从testuser2用户回收testuser.testtab表的 insert,update,delete,select权限

postgres=> revoke insert,update,delete,select on table testuser.testtab from testuser2;
REVOKE

再次查看testuser2用户的权限

3、查看用户拥有的权限常用sql语句

新建testuser用户

postgres=# create user testuser  IDENTIFIED BY 'test%1234';
CREATE ROLE

查看用户在数据库中拥有的权限

select a.datname,b.rolname,string_agg(a.pri_t,',') from (select datname,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='用户名' group by a.datname,b.rolname;

结果如下：

查看用户在schema中拥有的权限

select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname='用户名' group by a.nspname,b.rolname;

结果如下：

查看用户拥有的table权限

select table_name,table_schema,grantee,string_agg(privilege_type,',') from information_schema.table_privileges where grantee='用户名' group by table_name,table_schema,grantee;

结果如下：

三、生产环境中授权参考示例1

假设现在有这样的生产环境：存在业务用户yewuuser，普通用户user1
数据库yewudb（owner是yewuuser），
数据库yewudb下有schemaa，schemab。
需求：普通用户user1需要访问模式schemab下的所有对象（包括表、视图等）的所有权限，怎么实现？

Sql语句参考：
使用panweidb用户登录yewudb数据库进行授权

GRANT USAGE,CREATE ON SCHEMA schemab TO user1; 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA  schemab TO user1;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA  schemab TO user1;
GRANT EXECUTE  ON ALL FUNCTIONS IN SCHEMA schemab TO user1;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemab GRANT ALL PRIVILEGES ON TABLES TO user1;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemab GRANT ALL PRIVILEGES ON SEQUENCES TO user1;

结果如下：

也可以通过角色统一授权，适用于给多个用户授权相同权限的场景，方便权限统一集中管理。

create user  user3    IDENTIFIED BY 'user%1234';
create role role1  IDENTIFIED BY 'user%1234';
GRANT USAGE,CREATE ON SCHEMA schemab TO role1; 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA  schemab TO role1;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA  schemab TO role1;
GRANT EXECUTE  ON ALL FUNCTIONS IN SCHEMA schemab TO role1;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemab GRANT ALL PRIVILEGES ON TABLES TO role1;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemab GRANT ALL PRIVILEGES ON SEQUENCES TO role1;
#将角色统一授权给用户user3
grant role1 to user3;

结果如下：

四、生产环境中授权参考示例2

假设现在有这样的生产环境：业务用户yewuuser，普通用户user2
数据库yewudb（owner是yewuuser），
数据库yewudb下有schemaa，schemab。
需求：普通用户user2需要访问模式schemaa下的所有对象（包括表、视图等）的增删改查权限，怎么实现？

Sql语句参考：
使用panweidb用户登录yewudb数据库进行授权

GRANT USAGE,CREATE ON SCHEMA schemaa TO user2;
GRANT SELECT,INSERT,UPDATE,DELETE  ON ALL TABLES IN SCHEMA  schemaa TO user2;
GRANT SELECT  ON ALL SEQUENCES IN SCHEMA schemaa TO user2;
GRANT EXECUTE  ON ALL FUNCTIONS IN SCHEMA schemaa TO user2;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemaa GRANT ALL PRIVILEGES ON TABLES TO user2;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemaa GRANT ALL PRIVILEGES ON SEQUENCES TO user2;

结果如下：

也可以通过角色统一授权，适用于给多个用户授权相同权限的场景，方便权限统一集中管理。

create role role2  IDENTIFIED BY 'user%1234';
GRANT USAGE,CREATE ON SCHEMA schemaa TO role2; 
GRANT SELECT,INSERT,UPDATE,DELETE  ON ALL TABLES IN SCHEMA  schemaa TO role2;
GRANT SELECT  ON ALL SEQUENCES IN SCHEMA schemaa TO role2;
GRANT EXECUTE  ON ALL FUNCTIONS IN SCHEMA schemaa TO role2;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemaa GRANT ALL PRIVILEGES ON TABLES TO role2;
ALTER DEFAULT PRIVILEGES FOR USER yewuuser IN SCHEMA  schemaa GRANT ALL PRIVILEGES ON SEQUENCES TO role2;
#将角色统一授权给用户user4
grant role2 to user4;

结果如下：