GaussDB(DWS)表权限案例集锦

1. 假设当前有两个用户tom和jerry，如果想要用户jerry能够对当前tom创建的所有表以及将来创建的表都有查询权限，如何处理：

-- 1. 将用户tom下的同名schema权限赋给jerry
grant usage on schema tom to jerry;

-- 2. 将用户tom已经创建的表的select权限赋给jerry
grant select on all tables in schema tom to jerry;

-- 3. 将用户tom未来在同名schema下创建的表的select权限赋给jerry
-- 注意，其中for user tom是必须的，表示把用户tom将来在schema tom下创建的表的只读权限赋权给jerry
-- 如果不加for user tom，缺省值为当前角色/用户
alter default privileges for user tom in schema tom grant select on tables to jerry;

2. 如何查看某张表当前的权限情况

pg_class中relacl字段表示当前的访问权限，可以从该字段查询当前哪些用户对该表有访问权限，该字段回显结果为：

    rolename=xxxx/yyyy    --表示rolename对该表有xxxx权限，且权限来自yyyy

    =xxxx/yyyy                  -- 表示public对该表有xxxx权限，且权限来自yyyy

pg_class该字段介绍可参考DWS帮助文档：

https://support.huaweicloud.com/devg-dws/dws_04_0449.html#ZH-CN_TOPIC_0161912628__zh-cn_topic_0085032747_zh-cn_topic_0059778035_td89f8f6cc98f4a11a08b3c45d852a6cc

例如：

postgres=> select relname,relowner,relacl from pg_class where relname = 'tom_t1';
 relname | relowner | relacl 
---------+----------+--------
 tom_t1  |    25184 | 
(1 row)

如果relacl字段为空，说明只有管理员账户和该表的创建者对该用户有查询权限；

postgres=> select relname,relowner,relacl from pg_class where relname = 'tom_t1';
 relname | relowner |             relacl             
---------+----------+--------------------------------
 tom_t1  |    25184 | {tom=arwdDxt/tom,jerry=ar/tom}
(1 row)

本例中，relacl字段的含义是，用户tom对该表有全部权限，且权限来自tom；用户jerry对该表有select和insert权限，且该权限来自tom。

权限相关的判断可以通过访问权限查询函数进行判断（has_table_privilege等），例如：

postgres=# select * from has_table_privilege('jerry','test','select');
 has_table_privilege 
---------------------
 t
(1 row)

postgres=# select * from has_table_privilege('tom','test','select');
 has_table_privilege 
---------------------
 f
(1 row)

其中，a表示insert权限，r表示select权限。其他权限参考以下表格：

3. 某张表执行过 grant select on table t1 to public 导致所有用户对该表有读权限，如何针对某个用户回收权限

场景构造：假设当前有两个普通用户use1和use2，当前数据库下有两张表t1和t2， 执行：

grant select on table t1 to public;

用户use1和use2对该表有访问权限，并且新建用户use3后，新用户use3对该表也有访问权限，且执行revoke select on table t1 from use3无效：

test=# 
test=# revoke select on table t1 from use3;
REVOKE
test=# \c - use3
Password for user use3: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test" as user "use3".
test=> 
test=> select * from t1;
 a 
---
(0 rows)

test=> 
test=> select relname, relacl from pg_class where relname = 't1';
 relname |                    relacl                     
---------+-----------------------------------------------
 t1      | {liukunpeng=arwdDxt/liukunpeng,=r/liukunpeng}
(1 row)

这是因为之前执行过 grant select on table t1 to public 这条sql，该sql中关键字public表示该权限要赋予给所有角色，包括以后创建的角色，所以新用户use3对该表也有访问权限。public可以看做是一个隐含定义好的组，它总是包含所有角色。任何角色或用户都将拥有通过GRANT直接赋予的权限和所属组的权限，再加上public的权限。因此，执行完revoke select on table t1 from use3之后，虽然use3用户没有了该表的访问权限（通过该表的relacl字段也可以看到，参考第二个案例的链接权限说明），但是他仍然有public的权限，所以仍能访问该表。

解决方法：需要revoke回public的权限，然后对use3用户的权限单独管控。但是由于revoke回public的权限后可能导致原来能访问该表的用户（use1和use2）无法访问该表，影响现网业务，因此需要先对这些用户执行grant赋予相应权限，然后revoke回public的权限。

test=# --查看所有用户
test=# select * from pg_user where usesysid >= 16384;
 usename | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valbegin | valuntil |   respool    | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit 
---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+-----------------
 jack    |    16408 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
 tom     |    16412 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
 use1    |    16437 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
 use2    |    16441 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
 use3    |    16448 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
(5 rows)

test=# --对原用户执行grant
test=# grant select on table t1 to jack,tom,use1,use2;
GRANT
test=# --回收public的权限
test=# revoke select on table t1 from public;
test=# \c - use3
Password for user use3: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test" as user "use3".
test=> select * from t1;
ERROR:  permission denied for relation t1

可以看到，执行上述操作后，新用户use3的权限已收到管控，原用户use1和use2仍能访问该表，问题解决。

4. 赋予用户schema的all权限后建表仍然报错：ERROR:  current user does not have privilege to role tom

场景如下，有两个用户tom和jerry，jerry想要再tom的同名schema下创建表，于是把该schema的all权限赋给tom，但是创建表时仍然报错：

postgres=# grant all on schema tom to jerry;
GRANT
postgres=# \c - jerry
Password for user jerry: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "jerry".
postgres=> 
postgres=> create table tom.t(a int);
ERROR:  current user does not have privilege to role tom

根据报错内容，jerry需要角色tom的权限，通过以下方式授予：

postgres=# grant tom to jerry;
GRANT ROLE
postgres=# \c - jerry
Password for user jerry: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "jerry".
postgres=> 
postgres=> create table tom.t(a int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
postgres=> 

把角色tom的权限赋予jerry后，建表执行成功。

5. 外表权限报错：permission denied to select from foreign table in security mode

GaussDB(DWS)中，一个用户是否可以访问外表是需要同时修改用户属性的。

可使用ALTER USER或者ALTER ROLE语法指定USEFT参数赋予角色或用户使用外表的权限。参数USEFT | NOUSEFT决定一个新角色或用户是否能操作外表，包括：新建外表、删除外表、修改外表、读写外表等操作。

ALTER USER user_name USEFT;