更新时间:2022-08-16 GMT+08:00

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

给指定用户赋予某张表的权限主要通过以下语法实现,本章主要介绍常见的几种场景,包括只读(SELECT)、插入(INSERT)、改写(UPDATE)和超级权限。

语法格式

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 表u1.t1的表权限分类

用户名

用户类型

Grant授权语句

查询

插入

修改

删除

u1

所有者

-

u2

只读用户

1
GRANT SELECT ON u1.t1 TO u2;

x

x

x

u3

插入用户

1
GRANT INSERT ON u1.t1 TO u3;

x

x

x

u4

改写用户

1
GRANT SELECT,UPDATE ON u1.t1 TO u4;  
须知:

授予UPDATE权限必须同时授予SELECT权限,否则会出现信息泄露。

x

x

u5

超级用户

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}';
    

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

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

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

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

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

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

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

    1
    GRANT SELECT ON u1.t1 TO u2;
    

  5. 打开窗口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;
    

  6. 切回窗口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,可以对表进行查询、插入、改写和删除
    

  7. 打开窗口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;
    

  8. 打开窗口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;
    

  9. 打开窗口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;
    

  10. 最后切回窗口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拥有所有权限。