简单权限管理

创建角色

创建角色。

角色是拥有数据库对象和权限的实体。在不同的环境中角色可以认为是一个用户,一个组或者兼顾两者。

注意事项

  • 在数据库中添加一个新角色,角色无登录权限。
  • 创建角色的用户必须具备CREATE ROLE的权限或者是系统管理员。

语法格式

  1. CREATE ROLE role_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' | DISABLE };

其中角色信息设置子句option语法为:

  1. {SYSADMIN | NOSYSADMIN}
  2. | {AUDITADMIN | NOAUDITADMIN}
  3. | {CREATEDB | NOCREATEDB}
  4. | {USEFT | NOUSEFT}
  5. | {CREATEROLE | NOCREATEROLE}
  6. | {INHERIT | NOINHERIT}
  7. | {LOGIN | NOLOGIN}
  8. | {REPLICATION | NOREPLICATION}
  9. | {INDEPENDENT | NOINDEPENDENT}
  10. | {VCADMIN | NOVCADMIN}
  11. | CONNECTION LIMIT connlimit
  12. | VALID BEGIN 'timestamp'
  13. | VALID UNTIL 'timestamp'
  14. | RESOURCE POOL 'respool'
  15. | PERM SPACE 'spacelimit'
  16. | TEMP SPACE 'tmpspacelimit'
  17. | SPILL SPACE 'spillspacelimit'
  18. | IN ROLE role_name [, ...]
  19. | IN GROUP role_name [, ...]
  20. | ROLE role_name [, ...]
  21. | ADMIN rol e_name [, ...]
  22. | USER role_name [, ...]
  23. | SYSID uid
  24. | DEFAULT TABLESPACE tablespace_name
  25. | PROFILE DEFAULT
  26. | PROFILE profile_name
  27. | PGUSER

示例

  1. --创建一个角色,名为manager,密码为Bigdata@123
  2. openGauss=# CREATE ROLE manager IDENTIFIED BY 'Bigdata@123';
  3. --创建一个角色,从201511日开始生效,到202611日失效。
  4. openGauss=# CREATE ROLE miriam WITH LOGIN PASSWORD 'Bigdata@123' VALID BEGIN '2015-01-01' VALID UNTIL '2026-01-01';
  5. --修改角色manager的密码为abcd@123
  6. openGauss=# ALTER ROLE manager IDENTIFIED BY 'abcd@123' REPLACE 'Bigdata@123';
  7. --修改角色manager为系统管理员。
  8. openGauss=# ALTER ROLE manager SYSADMIN;
  9. --删除角色manager
  10. openGauss=# DROP ROLE manager;
  11. --删除角色miriam
  12. openGauss=# DROP ROLE miriam;

创建用户

创建一个用户。

注意事项

  • 通过CREATE USER创建的用户,默认具有LOGIN权限。
  • 通过CREATE USER创建用户的同时系统会在执行该命令的数据库中,为该用户创建一个同名的SCHEMA;其他数据库中,则不自动创建同名的SCHEMA;用户可使用CREATE SCHEMA命令,分别在其他数据库中,为该用户创建同名SCHEMA。
  • 系统管理员在普通用户同名SCHEMA下创建的对象,所有者为SCHEMA的同名用户(非系统管理员)。

语法格式

  1. CREATE USER user_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' | DISABLE };

其中option子句用于设置权限及属性等信息。

  1. {SYSADMIN | NOSYSADMIN}
  2. | {AUDITADMIN | NOAUDITADMIN}
  3. | {CREATEDB | NOCREATEDB}
  4. | {USEFT | NOUSEFT}
  5. | {CREATEROLE | NOCREATEROLE}
  6. | {INHERIT | NOINHERIT}
  7. | {LOGIN | NOLOGIN}
  8. | {REPLICATION | NOREPLICATION}
  9. | {INDEPENDENT | NOINDEPENDENT}
  10. | {VCADMIN | NOVCADMIN}
  11. | CONNECTION LIMIT connlimit
  12. | VALID BEGIN 'timestamp'
  13. | VALID UNTIL 'timestamp'
  14. | RESOURCE POOL 'respool'
  15. | PERM SPACE 'spacelimit'
  16. | TEMP SPACE 'tmpspacelimit'
  17. | SPILL SPACE 'spillspacelimit'
  18. | IN ROLE role_name [, ...]
  19. | IN GROUP role_name [, ...]
  20. | ROLE role_name [, ...]
  21. | ADMIN role_name [, ...]
  22. | USER role_name [, ...]
  23. | SYSID uid
  24. | DEFAULT TABLESPACE tablespace_name
  25. | PROFILE DEFAULT
  26. | PROFILE profile_name
  27. | PGUSER

示例

  1. --创建用户jim,登录密码为Bigdata@123
  2. openGauss=# CREATE USER jim PASSWORD 'Bigdata@123';
  3. --下面语句与上面的等价。
  4. openGauss=# CREATE USER kim IDENTIFIED BY 'Bigdata@123';
  5. --如果创建有“创建数据库”权限的用户,则需要加CREATEDB关键字。
  6. openGauss=# CREATE USER dim CREATEDB PASSWORD 'Bigdata@123';
  7. --将用户jim的登录密码由Bigdata@123修改为Abcd@123
  8. openGauss=# ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'Bigdata@123';
  9. --为用户jim追加CREATEROLE权限。
  10. openGauss=# ALTER USER jim CREATEROLE;
  11. --将enable_seqscan的值设置为on 设置成功后,在下一会话中生效。
  12. openGauss=# ALTER USER jim SET enable_seqscan TO on;
  13. --重置jimenable_seqscan参数。
  14. openGauss=# ALTER USER jim RESET enable_seqscan;
  15. --锁定jim帐户。
  16. openGauss=# ALTER USER jim ACCOUNT LOCK;
  17. --删除用户。
  18. openGauss=# DROP USER kim CASCADE;
  19. openGauss=# DROP USER jim CASCADE;
  20. openGauss=# DROP USER dim CASCADE;

权限赋予

对角色和用户进行授权操作。

使用GRANT命令进行用户授权包括以下三种场景:

  • 将系统权限授权给角色或用户

    系统权限又称为用户属性,包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。

  • 将数据库对象授权给角色或用户

    将数据库对象(表和视图、指定字段、数据库、函数、模式、表空间等)的相关权限授予特定角色或用户。

  • 将角色或用户的权限授权给其他角色或用户

    将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下,每个角色或用户都可视为拥有一个或多个数据库权限的集合。

语法格式

  • 将表或视图的访问权限赋予指定的用户或角色。

    1. GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } [, ...]
    2. | ALL [ PRIVILEGES ] }
    3. ON { [ TABLE ] table_name [, ...]
    4. | ALL TABLES IN SCHEMA schema_name [, ...] }
    5. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    6. [ WITH GRANT OPTION ];
  • 将表中字段的访问权限赋予指定的用户或角色。

    1. GRANT { {{ SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )} [, ...]
    2. | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    3. ON [ TABLE ] table_name [, ...]
    4. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    5. [ WITH GRANT OPTION ];
  • 将数据库的访问权限赋予指定的用户或角色。

    1. GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...]
    2. | ALL [ PRIVILEGES ] }
    3. ON DATABASE database_name [, ...]
    4. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    5. [ WITH GRANT OPTION ];
  • 将域的访问权限赋予指定的用户或角色。

    1. GRANT { USAGE | ALL [ PRIVILEGES ] }
    2. ON DOMAIN domain_name [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];

    简单权限管理 - 图1 说明: 本版本暂时不支持赋予域的访问权限。

  • 将外部数据源的访问权限赋予给指定的用户或角色。

    1. GRANT { USAGE | ALL [ PRIVILEGES ] }
    2. ON FOREIGN DATA WRAPPER fdw_name [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];
  • 将外部服务器的访问权限赋予给指定的用户或角色。

    1. GRANT { USAGE | ALL [ PRIVILEGES ] }
    2. ON FOREIGN SERVER server_name [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];
  • 将函数的访问权限赋予给指定的用户或角色。

    1. GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    2. ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
    3. | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    4. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    5. [ WITH GRANT OPTION ];
  • 将过程语言的访问权限赋予给指定的用户或角色。

    1. GRANT { USAGE | ALL [ PRIVILEGES ] }
    2. ON LANGUAGE lang_name [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];
  • 将大对象的访问权限赋予指定的用户或角色。

    1. GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    2. ON LARGE OBJECT loid [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];

    简单权限管理 - 图2 说明: 本版本暂时不支持大对象。

  • 将模式的访问权限赋予指定的用户或角色。

    1. GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    2. ON SCHEMA schema_name [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];

    简单权限管理 - 图3 说明: 将模式中的表或者视图对象授权给其他用户时,需要将表或视图所属的模式的USAGE权限同时授予该用户,若没有该权限,则只能看到这些对象的名称,并不能实际进行对象访问。

  • 将表空间的访问权限赋予指定的用户或角色。

    1. GRANT { CREATE | ALL [ PRIVILEGES ] }
    2. ON TABLESPACE tablespace_name [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];
  • 将类型的访问权限赋予指定的用户或角色。

    1. GRANT { USAGE | ALL [ PRIVILEGES ] }
    2. ON TYPE type_name [, ...]
    3. TO { [ GROUP ] role_name | PUBLIC } [, ...]
    4. [ WITH GRANT OPTION ];

    简单权限管理 - 图4 说明: 本版本暂时不支持赋予类型的访问权限。

  • 将角色的权限赋予其他用户或角色的语法。

    1. GRANT role_name [, ...]
    2. TO role_name [, ...]
    3. [ WITH ADMIN OPTION ];
  • 将sysadmin权限赋予指定的角色。

    1. GRANT ALL { PRIVILEGES | PRIVILEGE }
    2. TO role_name;

示例

示例:将系统权限授权给用户或者角色。

创建名为joe的用户,并将sysadmin权限授权给他。

  1. openGauss=# CREATE USER joe PASSWORD 'Bigdata@123';
  2. openGauss=# GRANT ALL PRIVILEGES TO joe;

授权成功后,用户joe会拥有sysadmin的所有权限。

示例:将对象权限授权给用户或者角色

  1. 撤销joe用户的sysadmin权限,然后将模式tpcds的使用权限和表tpcds.reason的所有权限授权给用户joe。

    1. openGauss=# REVOKE ALL PRIVILEGES FROM joe;
    2. openGauss=# GRANT USAGE ON SCHEMA tpcds TO joe;
    3. openGauss=# GRANT ALL PRIVILEGES ON tpcds.reason TO joe;

    授权成功后,joe用户就拥有了tpcds.reason表的所有权限,包括增删改查等权限。

  2. 将tpcds.reason表中r_reason_sk、r_reason_id、r_reason_desc列的查询权限,r_reason_desc的更新权限授权给joe。

    1. openGauss=# GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;

    授权成功后,用户joe对tpcds.reason表中r_reason_sk、r_reason_id的查询权限会立即生效。如果joe用户需要拥有将这些权限授权给其他用户的权限,可以通过以下语法对joe用户进行授权。

    1. openGauss=# GRANT select (r_reason_sk, r_reason_id) ON tpcds.reason TO joe WITH GRANT OPTION;

    将数据库postgres的连接权限授权给用户joe,并给予其在postgres中创建schema的权限,而且允许joe将此权限授权给其他用户。

    1. openGauss=# GRANT create,connect on database postgres TO joe WITH GRANT OPTION;

    创建角色tpcds_manager,将模式tpcds的访问权限授权给角色tpcds_manager,并授予该角色在tpcds下创建对象的权限,不允许该角色中的用户将权限授权给其他人。

    1. openGauss=# CREATE ROLE tpcds_manager PASSWORD 'Bigdata@123';
    2. openGauss=# GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

    将表空间tpcds_tbspc的所有权限授权给用户joe,但用户joe无法将权限继续授予其他用户。

    1. openGauss=# CREATE TABLESPACE tpcds_tbspc RELATIVE LOCATION 'tablespace/tablespace_1';
    2. openGauss=# GRANT ALL ON TABLESPACE tpcds_tbspc TO joe;

示例:将用户或者角色的权限授权给其他用户或角色。

  1. 创建角色manager,将joe的权限授权给manager,并允许该角色将权限授权给其他人。

    1. openGauss=# CREATE ROLE manager PASSWORD 'Bigdata@123';
    2. openGauss=# GRANT joe TO manager WITH ADMIN OPTION;
  2. 创建用户senior_manager,将用户manager的权限授权给该用户。

    1. openGauss=# CREATE ROLE senior_manager PASSWORD 'Bigdata@123';
    2. openGauss=# GRANT manager TO senior_manager;
  3. 撤销权限,并清理用户。

    1. openGauss=# REVOKE manager FROM joe;
    2. openGauss=# REVOKE senior_manager FROM manager;
    3. openGauss=# DROP USER manager;

示例:撤销上述授予的权限,并清理角色和用户。

  1. openGauss=# REVOKE ALL PRIVILEGES ON tpcds.reason FROM joe;
  2. openGauss=# REVOKE ALL PRIVILEGES ON SCHEMA tpcds FROM joe;
  3. openGauss=# REVOKE ALL ON TABLESPACE tpcds_tbspc FROM joe;
  4. openGauss=# DROP TABLESPACE tpcds_tbspc;
  5. openGauss=# REVOKE USAGE,CREATE ON SCHEMA tpcds FROM tpcds_manager;
  6. openGauss=# DROP ROLE tpcds_manager;
  7. openGauss=# DROP ROLE senior_manager;
  8. openGauss=# DROP USER joe CASCADE;