GaussDB用户权限管理
- 系统权限
- 系统权限查看
- 系统权限授予
- 角色权限
- 角色权限查看
- 角色权限授予
- 对象权限
- 对象权限查看
- 对象权限授予
GaussDB数据库中的权限管理可以分为三个级别：
- 系统权限：包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN等权限。
- 角色权限：将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下，每个角色或用户都可视为拥有一个或多个数据库权限的集合。
- 对象权限：将数据库对象（表和视图、指定字段、数据库、函数、模式等）的相关权限授予特定角色或用户。
🐘与权限相关的的系统视图命名大都以ADM_、DB_和MY_前缀开头，且同时位于pg_catalog和sys模式下。
查看数据库中的用户：
gaussdb=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------
scottmark100 | Sysadmin | {}
rdsAdmin | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
rdsBackup | Operatoradmin, Persistence | {}
rdsMetric | Monitoradmin, Persistence | {}
rdsRepl | Replication, Persistence | {}
root | Create role, Create DB, Sysadmin, Monitoradmin | {gs_role_copy_files,gs_role_signal_backend,gs_role_tablespace,gs_role_replication,gs_role_account_lock,gs_role_pldebugger}
wolong | | {}
--查看所有用户信息
gaussdb=# select user_id,username,default_tablespace,created,lock_date,expiry_date,account_status from adm_users;
user_id | username | default_tablespace | created | lock_date | expiry_date | account_status
---------+--------------+--------------------+-------------------------------+-------------------------------+-------------+----------------
16721 | rdsRepl | pg_default | 2024-12-27 14:41:06.750074+08 | 2024-12-27 14:41:06.760944+08 | | 0
16717 | rdsBackup | pg_default | 2024-12-27 14:41:05.473989+08 | 2024-12-27 14:41:05.485726+08 | | 0
16725 | root | pg_default | 2024-12-27 14:41:08.02417+08 | 2024-12-27 14:41:08.035402+08 | | 0
10 | rdsAdmin | pg_default | 2024-12-27 14:40:59.840255+08 | | |
19408 | scottmark100 | pg_default | 2025-01-10 09:15:19.449045+08 | 2025-01-10 09:15:19.469667+08 | | 0
16729 | rdsMetric | pg_default | 2024-12-27 14:41:09.371189+08 | 2024-12-27 14:41:09.381896+08 | | 0
21713 | wolong | pg_default | 2025-01-20 14:30:35.220454+08 | 2025-01-20 14:30:35.238048+08 | | 0
(7 rows)
📖 系统视图adm_users中的字段含义：
account_status：NULL表示该用户是拥有最高权限的初始系统管理员（rdsAdmin），0表示用户状态正常，1表示该用户由于登录失败次数超过阈值被锁定了一段时间，2表示该用户被管理员锁定。lock_date：默认显示用户的创建日期（即created字段的值）。如果该用户被锁定过则显示账户被锁定的日期。初始系统管理员该字段为NULL。
系统权限
系统权限查看
查看用户的系统权限：
--查看所有/任意指定用户的系统权限
tpcc=# select * from adm_sys_privs where grantee='scottmark100';
grantee | privilege | admin_option | common | inherited
--------------+----------------+--------------+--------+-----------
scottmark100 | rolinherit | NO | |
scottmark100 | rolcanlogin | NO | |
scottmark100 | rolsystemadmin | NO | |
(3 rows)
--查看自己拥有的系统权限
tpcc=> select * from my_sys_privs;
grantee | privilege | admin_option | common | inherited
---------+-------------+--------------+--------+-----------
wolong | rolinherit | NO | |
wolong | rolcanlogin | NO | |
(2 rows)
视图adm_sys_privs只有管理员可以查看，其中privilege列会显示用户的系统权限或ANY权限。
- 系统权限包括：rolsuper、rolinherit、rolcreaterole、rolcreatedb、rolcatupdate、rolcanlogin、rolreplication、rolauditadmin、rolsystemadmin、roluseft、rolmonitoradmin、roloperatoradmin、rolpolicyadmin。
- ANY权限包括：create/alter/drop/update/insert/delete/select any table、create/execute any function、create/execute any package、create/alter/drop any type、create/alter/drop/select any sequence、create/alter/drop any index、create/alter/drop any trigger、create/drop any synonym。
查看所有用户的ANY权限：
tpcc=# select * from gs_db_privileges;
rolename | privilege_type | admin_option
--------------+------------------+--------------
r2 | select any table | no
scottmark100 | create any table | no
(2 rows)
系统权限授予
授予或撤销一般系统权限，使用CREATE、ALTER语法：
--在创建用户的同时授予系统权限（with关键字可省略）
create user scottmark100 with createdb;
--添加/删除用户的系统权限
alter user scottmark100 sysadmin;
alter user scottmark100 nosysadmin;
SYSADMIN系统权限也可以通过ALL PRIVILEGES关键字授予或撤销授权：
grant all privileges to scottmark100;
revoke all privileges from scottmark100;
授予或撤销ANY权限，使用GRANT、REVOKE语法：
\c db4ora --ANY权限仅对当前数据库内的对象生效
grant create any table to scottmark100;
revoke create any table from scottmark100;
⭐️ 总结：系统权限和ANY权限有如下注意事项：
- 系统权限通过
CREATE/ALTER ROLE/USER来授予。SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。 - 系统权限不能通过ROLE和USER的权限被继承，也不能授予PUBLIC。
- ANY权限可以通过角色被继承，但不能授予PUBLIC。
- ANY权限只对执行授权语句时所处数据库内的对象生效。
- 即使被授予ANY权限，也无法对私有用户下的对象进行增删改查。
- 拥有CREATE ANY权限的用户，在非自己的模式下创建table、function、package、type、sequence、index时，被创建对象的属主是该模式的所有者。创建者需要对被创建对象进行增删改查等操作时，还需要对应的额外授权。
- 拥有CREATE ANY权限的用户，在非自己的模式下创建trigger和synonym时，被创建对象的属主是创建者。
- 对于ANY权限，授权时如果有WITH ADMIN OPTION，被授权的用户就可以将该ANY权限转授给其他用户或角色，也可以从其他用户和角色回收ANY权限。
角色权限
角色权限查看
查看用户的角色权限：
--查看所有/任意指定用户的角色权限
tpcc=# select * from adm_role_privs;
grantee | granted_role | admin_option | delegate_option | default_role | os_granted | common | inherited
--------------+------------------------+--------------+-----------------+--------------+------------+--------+-----------
root | gs_role_copy_files | YES | | | | |
root | gs_role_signal_backend | YES | | | | |
root | gs_role_tablespace | YES | | | | |
root | gs_role_replication | YES | | | | |
root | gs_role_account_lock | YES | | | | |
root | gs_role_pldebugger | YES | | | | |
scottmark100 | r1 | NO | | | | |
(7 rows)
--查看自己拥有的角色权限
tpcc=> select * from my_role_privs;
grantee | granted_role | admin_option | delegate_option | default_role | os_granted | common | inherited
--------------+--------------+--------------+-----------------+--------------+------------+--------+-----------
scottmark100 | r1 | NO | | | | |
(1 row)
角色权限授予
创建角色：
create role r1 with login identified by 'GaussDB101'; --角色r1有login权限
grant select on wolong.caocao to r1;
create role r2 identified by 'GaussDB101'; --角色r2无login权限
grant select any table to r2;
通过CREATE ROLE创建的角色，如果有login权限则记录到adm_users视图中，否则记录到adm_roles中。
tpcc=# select user_id,username,default_tablespace,created,lock_date,expiry_date,account_status from adm_users order by created;
user_id | username | default_tablespace | created | lock_date | expiry_date | account_status
---------+--------------+--------------------+-------------------------------+-------------------------------+-------------+----------------
10 | rdsAdmin | pg_default | 2024-12-27 14:40:59.840255+08 | | |
16717 | rdsBackup | pg_default | 2024-12-27 14:41:05.473989+08 | 2024-12-27 14:41:05.485726+08 | | 0
16721 | rdsRepl | pg_default | 2024-12-27 14:41:06.750074+08 | 2024-12-27 14:41:06.760944+08 | | 0
16725 | root | pg_default | 2024-12-27 14:41:08.02417+08 | 2024-12-27 14:41:08.035402+08 | | 0
16729 | rdsMetric | pg_default | 2024-12-27 14:41:09.371189+08 | 2024-12-27 14:41:09.381896+08 | | 0
19408 | scottmark100 | pg_default | 2025-01-10 09:15:19.449045+08 | 2025-01-10 09:15:19.469667+08 | | 0
21713 | wolong | pg_default | 2025-01-20 14:30:35.220454+08 | 2025-01-20 14:30:35.238048+08 | | 0
22357 | r1 | pg_default | 2025-01-23 12:57:09.872753+08 | 2025-01-23 12:57:09.883918+08 | | 0
(8 rows)
tpcc=# select * from adm_roles;
role | role_id | authentication_type | common | oracle_maintained | inherited | implicit | external_name
-------------------------------+---------+---------------------+--------+-------------------+-----------+----------+---------------
gs_role_copy_files | 1044 | password | | | | |
gs_role_signal_backend | 1045 | password | | | | |
gs_role_tablespace | 1046 | password | | | | |
gs_role_replication | 1047 | password | | | | |
gs_role_account_lock | 1048 | password | | | | |
gs_role_pldebugger | 1055 | password | | | | |
gs_role_directory_create | 1056 | password | | | | |
gs_role_directory_drop | 1059 | password | | | | |
gs_role_public_dblink_drop | 1060 | password | | | | |
gs_role_public_dblink_alter | 1061 | password | | | | |
gs_role_seclabel | 1062 | password | | | | |
gs_role_public_synonym_create | 1063 | password | | | | |
gs_role_public_synonym_drop | 1064 | password | | | | |
r2 | 22360 | password | | | | |
(14 rows)
角色授权和移除权限：
grant r1 to scottmark100;
--授权角色并赋予转授权限
grant r1 to scottmark100 with admin option;
--移除角色，不影响直接授予的权限，也不做级联移除
revoke r1 from scottmark100;
revoke r1 from scottmark100 retrict;
--级联移除权限（慎用！！！）
revoke r1 from scottmark100 cascade;
--仅移除r1角色的转授权限，不移除r1角色权限
revoke admin option for r1 from scottmark100;
注：经测试REVOKE CASCADE的级联删除可能会起不到作用。官方文档也说使用不当时可能会导致删除了不想删除的权限，或者是任何权限都没有删除。
⭐️ 总结：角色权限有如下注意事项：
- 对于角色授权，当声明了WITH ADMIN OPTION，被授权的用户可以将该角色转授给其他角色或用户，以及从其他用户或角色回收该角色权限。当授权的角色或用户发生变更或被撤销时，所有继承该角色或用户权限的用户拥有的权限都会随之发生变更。
- 拥有CREATEROLE权限的角色可以赋予或者撤销任何非系统管理员角色的权限。
- 数据库系统管理员可以给任何角色或用户授予/撤销任何权限。
- 以
gs_role_开头的角色名作为数据库的内置角色保留名，禁止新建以gs_role_开头的用户、角色、模式。
对象权限
GaussDB数据库中，database是数据库对象的物理集合，不同database之间的资源完全隔离（除部分共享对象外）。Database是对业务的物理隔离，不同database之间的对象不能相互访问。
同一个database中，通过schema来实现对数据库对象的逻辑隔离。通过权限管理可以实现在同一个会话中对不同schema下对象的访问和操作管理。
对象权限查看
查看用户的所有对象权限（包括表、视图、字段、索引、模式等）：
--查看所有/任意指定用户的对象权限
tpcc=# select grantee,owner,table_name,type,grantor,privilege,grantable from adm_tab_privs where grantee='scottmark100';
grantee | owner | table_name | type | grantor | privilege | grantable
--------------+--------+------------+--------+---------+-----------+-----------
scottmark100 | wolong | name | COLUMN | wolong | SELECT | NO
scottmark100 | wolong | name | COLUMN | wolong | COMMENT | NO
scottmark100 | wolong | age | COLUMN | wolong | SELECT | NO
scottmark100 | wolong | age | COLUMN | wolong | COMMENT | NO
scottmark100 | wolong | caocao | table | wolong | INSERT | NO
scottmark100 | wolong | caocao | table | wolong | SELECT | YES
scottmark100 | wolong | caocao | table | wolong | UPDATE | NO
scottmark100 | wolong | caocao | table | wolong | DELETE | NO
(8 rows)
--查看当前用户的对象权限
tpcc=> select grantee,table_schema,table_name,type,privilege,grantor,grantable from db_tab_privs where grantee<>'public';
grantee | table_schema | table_name | type | privilege | grantor | grantable
--------------+--------------+------------+--------+-----------+---------+-----------
scottmark100 | | name | COLUMN | SELECT | wolong | NO
scottmark100 | | name | COLUMN | COMMENT | wolong | NO
scottmark100 | | age | COLUMN | SELECT | wolong | NO
scottmark100 | | age | COLUMN | COMMENT | wolong | NO
scottmark100 | wolong | caocao | table | INSERT | wolong | NO
scottmark100 | wolong | caocao | table | SELECT | wolong | YES
scottmark100 | wolong | caocao | table | UPDATE | wolong | NO
scottmark100 | wolong | caocao | table | DELETE | wolong | NO
(8 rows)
单独查看列的授权信息：
--查看所有/任意指定用户的列字段权限
select grantee,owner,table_schema,table_name,column_name,privilege,grantor,grantable from adm_col_privs;
--查看当前用户的列权限
select grantee,owner,table_schema,table_name,column_name,privilege,grantor,grantable from db_col_privs;
注：视图db_tab_privs和db_col_privs会包含已经启用的角色以及public角色作为grantee时的列权限信息。
查看用户是否拥有对某个对象的特定权限：
tpcc=# select * from has_table_privilege('scottmark100','bmsql_item','insert');
has_table_privilege
---------------------
t
(1 row)
对象权限授予
将Schema中的表或者视图对象授权给其他用户或角色时，需要将表或视图所属Schema的USAGE权限同时授予该用户或角色。否则用户或角色将只能看到这些对象的名称，并不能实际进行对象访问。
grant usage on schema wolong to scottmark100;
表或视图授权：
--语法
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE | VACUUM | ALTER | DROP } [, ...]
| ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
--示例
grant insert,update,delete,select on wolong.caocao to scottmark100;
列（字段）授权：
--语法
GRANT { {{ SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )} [, ...]
| ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
--示例
grant select(name,age),comment (name),update (age) on wolong.caocao to scottmark100;
函数授权：
--语法
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
--示例
grant execute on function func_add_sql(integer, integer) to scottmark100;
序列授权：
--语法
GRANT { { SELECT | UPDATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
--示例
grant update on sequence seq1 to scottmark100;
模式授权（慎用！！！）：
--语法
GRANT { { CREATE | USAGE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
--示例：将模式wolong的访问权限授权给scottmark100，并授予在wolong下创建对象的权限
grant usage,create on schema wolong to scottmark100;
⭐️ 总结：对象权限的注意事项：
- 通过GRANT语句授权对象权限时，如果有声明WITH GRANT OPTION，则被授权的用户也可以把该权限授予其他用户和角色。
- 默认情况下，所有角色都拥有当前数据库中public模式的USAGE权限，但是普通用户没有在public模式下的CREATE权限。
References
【1】https://docs.mogdb.io/zh/mogdb/v5.0/2-managing-users-and-their-permissions
【2】https://support.huaweicloud.com/sqlreference-dws/dws_06_0250.html
【3】https://support.huaweicloud.com/dws_faq/dws_03_0200.html
