如何给指定用户赋予某张表的权限？

语法格式

1
2
3
4
5
6

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE } [, ...] 
      | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
       | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] 
    [ WITH GRANT OPTION ];

场景介绍

假设当前有用户u1~u5，在系统中有对应的同名Schema u1~u5，各用户的权限管控如下：

• u2作为只读用户，需要表u1.t1的SELECT权限。
• u3作为插入用户，需要表u1.t1的INSERT权限。
• u4作为改写用户，需要表u1.t1的UPDATE权限。
• u5作为拥有所有权限的用户，需要表u1.t1的所有权限。

1

GRANT SELECT ON u1.t1 TO u2;

1

GRANT INSERT ON u1.t1 TO u3;

1

GRANT SELECT,UPDATE ON u1.t1 TO u4;  

1

GRANT ALL PRIVILEGES ON u1.t1 TO u5;

操作步骤

以下将演示不同权限的授权方法和验证过程。

1. 打开窗口1（即dbadmin连接会话窗口，后续不再提示），使用系统管理员dbadmin连接DWS数据库，创建用户u1~u5（系统默认会创建u1~u5的同名SCHEMA）。

   1 2 3 4 5

   CREATE USER u1 PASSWORD '{password}'; CREATE USER u2 PASSWORD '{password}'; CREATE USER u3 PASSWORD '{password}'; CREATE USER u4 PASSWORD '{password}'; CREATE USER u5 PASSWORD '{password}';

2. 在SCHEMA u1下创建表u1.t1。

   1	CREATE TABLE u1.t1 (c1 int, c2 int);

3. 为表中插入两条数据。

   1 2	INSERT INTO u1.t1 VALUES (1,2); INSERT INTO u1.t1 VALUES (1,2);

4. DWS中引入了SCHEMA层概念，如果有SCHEMA，需要先给用户赋予SCHEMA的使用权限。

   1	GRANT USAGE ON SCHEMA u1 TO u2,u3,u4,u5;

5. 给只读用户u2赋予表u1.t1的查询权限。

   1	GRANT SELECT ON u1.t1 TO u2;

6. 打开窗口2（即用户u2连接会话窗口，后续不再提示），使用用户u2连接DWS数据库，验证u2可以查询u1.t1表，但是不能写入和修改，此时u2为只读用户。

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

7. 切回窗口1，分别给u3、u4、u5赋予对应的权限。

   1 2 3

   GRANT INSERT ON u1.t1 TO u3; --插入用户u3，可以插入数据 GRANT SELECT,UPDATE ON u1.t1 TO u4; --改写用户u4，可以修改表 GRANT ALL PRIVILEGES ON u1.t1 TO u5; --拥有所有权限的用户u5，可以对表进行查询、插入、改写和删除

8. 打开窗口3，使用用户u3连接DWS数据库，验证u3可以插入u1.t1，但是不能查询和修改，此时u3为插入用户。

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

9. 打开窗口4，使用用户u4连接DWS数据库，验证u4可以修改u1.t1，同时还可以查询，但是不能插入，此时u4为改写用户。

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

10. 打开窗口5，使用用户u5连接DWS数据库，验证u5可以查询、插入、修改和删除u1.t1，此时u5为拥有所有权限的用户。

    1 2 3 4

    SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; DELETE FROM u1.t1;

    

11. 最后切回窗口1，通过函数has_table_privilege分别查询每个用户的权限。

    1	SELECT * FROM pg_class WHERE relname = 't1';

    返回结果，查看relacl字段，该字段回显结果如下。"rolename=xxxx/yyyy" --表示rolename对该表有xxxx权限，且权限来自yyyy；

    例如下图，与以上验证结果完全一致。

    

    • u1=arwdDxtA/u1，表示u1为owner，拥有所有权限。
    • u2=r/u1，表示u2拥有读权限。
    • u3=a/u1，表示u3拥有插入权限。
    • u4=rw/u1，表示u4拥有读和修改权限。
    • u5=arwdDxtA/u1，表示u5拥有所有权限。