【GaussDB每日一贴】aclexplode函数的使用

查看数据库

在GaussDB中，如何查询一个用户对某个数据库的权限（比如能否连接数据库A，能否在数据库B中创建表）？

--先创建一个数据
gaussdb=# CREATE DATABASE dn_20;
CREATE DATABASE

再进入该数据库创建数据对象

-- 进入数据库后创建数据对象
dn_20=> CREATE TABLE tbl_t1 AS SELECT * FROM pg_class LIMIT 20;
INSERT 0 20
dn_20=> \d
                         List of relations
 Schema |  Name  | Type  | Owner |             Storage
--------+--------+-------+-------+----------------------------------
 public | tbl_t1 | table | root  | {orientation=row,compression=no}

再创建一个用户，并授权CONNECT,CREATE的权限：

dn_20=> CREATE USER user01 PASSWORD 'user01@1234';
CREATE ROLE
-- 做简单的授权
CREATE CONNECT,CREATE ON DATABASE dn_20 TO user01;

通过pg_database表中datacl的这一列，可以看到该数据库的访问权限

gaussdb=# select datname,datacl from pg_database where datname='dn_20';
 datname |                         datacl
---------+---------------------------------------------------------
 dn_20   | {=Tc/rdsAdmin,rdsAdmin=CTc/rdsAdmin,user01=Cc/rdsAdmin}
(1 row)

但是这样很不直观，可以使用aclexplode这个函数

gaussdb=# select datname,aclexplode(datacl) from pg_database where datname='dn_20';
 datname |      aclexplode
---------+----------------------
 dn_20   | (10,0,TEMPORARY,f)
 dn_20   | (10,0,CONNECT,f)
 dn_20   | (10,10,CREATE,f)
 dn_20   | (10,10,TEMPORARY,f)
 dn_20   | (10,10,CONNECT,f)
 dn_20   | (10,40975,CREATE,f)
 dn_20   | (10,40975,CONNECT,f)

--- 继续展开
gaussdb=# select datname,aclexplode(datacl).grantee, aclexplode(datacl).privilege_type from pg_database where datname='dn_20';
 datname | grantee | privilege_type
---------+---------+----------------
 dn_20   |       0 | TEMPORARY
 dn_20   |       0 | CONNECT
 dn_20   |      10 | CREATE
 dn_20   |      10 | TEMPORARY
 dn_20   |      10 | CONNECT
 dn_20   |   40975 | CREATE
 dn_20   |   40975 | CONNECT

其中，grantee就是用户的oid，因此就能通过这个字段来关联用户表

SELECT pr.rolname,t.*
FROM
(select datname,aclexplode(datacl).grantee, 
 aclexplode(datacl).privilege_type 
 from pg_database )t,pg_roles pr
WHERE t.grantee=pr.oid and t.datname='dn_20';


 rolname  | datname | grantee | privilege_type
----------+---------+---------+----------------
 rdsAdmin | dn_20   |      10 | CREATE
 rdsAdmin | dn_20   |      10 | TEMPORARY
 rdsAdmin | dn_20   |      10 | CONNECT
 user01   | dn_20   |   40975 | CREATE
 user01   | dn_20   |   40975 | CONNECT

这样就能看到用户对数据库所拥有的权限

查看模式的权限

同理也可以看到用户对schema的权限

-- 在数据库里创建schema
dn_20=> CREATE SCHEMA test20;
CREATE SCHEMA
dn_20=> GRANT USAGE ON SCHEMA test20 TO user01;
GRANT

使用aclexplode函数得出具体权限

SELECT pr.rolname,t.*
FROM
 (select nspname,aclexplode(nspacl).privilege_type,aclexplode(nspacl).grantee from pg_namespace) t,pg_roles pr
 WHERE t.grantee=pr.oid
 AND t.nspname='test20';
 
 rolname | nspname | privilege_type | grantee
---------+---------+----------------+---------
 root    | test20  | USAGE          |   16776
 root    | test20  | CREATE         |   16776
 user01  | test20  | USAGE          |   40975