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

如何给指定用户赋予某个SCHEMA的权限?

给某个用户授权某个SCHEMA的权限,包括三个场景(本章节针对SCHEMA层级权限,仅演示查询权限,如果需要其他权限,可以参考)《常见问题》中“如何给指定用户赋予某张表的权限?”章节

  • SCHEMA下当前某张表权限。
  • SCHEMA下当前所有表的权限。
  • SCHEMA下未来创建的表的权限。

假设有两个用户u1和u2,对应的同名SCHEMA是u1和u2,用户u2需要访问SCHEMA u1的表(包括当前的某张表、所有表、未来创建的表)。

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

    1
    2
    CREATE USER u1 PASSWORD '{password}';
    CREATE USER u2 PASSWORD '{password}';
    

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

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

  3. 给用户u2赋予SCHEMA u1的访问权限。

    1
    GRANT USAGE ON SCHEMA u1 TO u2;
    

  4. 某张表权限)给用户u2赋予SCHEMA u1下某张表u1.t1的查询权限。

    1
    GRANT SELECT ON u1.t1 TO u2;
    

  5. 打开窗口2(即用户u2连接会话窗口,后续不再提示),使用用户u2连接DWS数据库,验证u2可以查询u1.t1表,但是不能查询u1.t2表。

    1
    2
    SELECT * FROM u1.t1;
    SELECT * FROM u1.t2;
    

  6. 所有表权限)切回窗口1,使用dbadmin给用户u2赋予SCHEMA u1下所有表的查询权限。

    1
    GRANT SELECT ON ALL TABLES IN SCHEMA u1 TO u2;
    

  7. 切回窗口2,再次验证u2可以查询所有表。

    1
    2
    SELECT * FROM u1.t1;
    SELECT * FROM u1.t2;
    

  8. 切回窗口1,创建一张新的表u1.t3。

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

  9. 切回窗口2,验证发现用户u2没有u1.t3的查询权限,说明u2虽然有SCHEMA u1下所有表的访问权限,但是对于赋权后新创建的表还是没有访问权限(即u2对SCHEMA u1未来的表权限是没有的)。

    1
    SELECT * FROM u1.t3;
    

  10. (未来表权限)切回窗口1,给用户u2赋予SCHAMA u1未来创建的表的访问权限,并创建一张新的表u1.t4。

    1
    2
    ALTER DEFAULT PRIVILEGES FOR ROLE u1 IN SCHEMA u1 GRANT SELECT ON TABLES TO u2;
    CREATE TABLE u1.t4 (c1 int, c2 int);
    

  11. 切回窗口2,验证发现用户u2可以访问u1.t4。但是对于之前的u1.t3还是没有访问权限(以上ALTER DEFAULT PRIVILEGES语句的授权范围只是未来创建的表,对于已经创建的表,还是要使用GRANT 单张表重新授权),此时需要参考4重新授权u1.t3即可。

    1
    SELECT * FROM u1.t4;