磐维数据库的权限使用

一、磐维数据库的权限

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;

结果如下：

四、授权参考实例 3

创建dbid数据库

赋予dbidopr增、删、改、查、建表等权限

赋予dbidqry查询权限

--gsql -r 创建数据库
create database  dbid;
--gsql -d  dbid -r  创建用户并授予建表，增删查改表权限
create user dbidopr identified by "********";
create schema dbid;
GRANT USAGE,CREATE ON SCHEMA dbid TO dbidopr; 
GRANT select,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA  dbid TO dbidopr;
ALTER DEFAULT PRIVILEGES FOR USER omm IN SCHEMA  dbid GRANT select,INSERT,UPDATE,DELETE ON TABLES TO dbidopr;

--gsql -d  dbid -r  创建用户赋予select  on all tables权限
create user dbidqry identified by "*******";
GRANT USAGE ON SCHEMA  dbid TO dbidqry;
grant select  on all tables in schema dbid to dbidqry;
ALTER DEFAULT PRIVILEGES FOR USER omm IN SCHEMA  dbid GRANT select ON TABLES TO dbidqry;
ALTER DEFAULT PRIVILEGES FOR USER dbidopr IN SCHEMA  dbid GRANT select ON TABLES TO dbidqry;

--防止用户创建对象时不加属主，把对象创建到用户默认SCHEMA下，需要删除用户默认SCHEMA
DROP SCHEMA dbidopr;
DROP SCHEMA dbidqry;