Gauss DB（DWS）权限管理场景总结
GaussDB(DWS) 使用默认权限机制，数据库对象创建后，进行对象创建的用户就是该对象的所有者。集群安装后的默认情况下，未开启三权分立，数据库系统管理员具有与对象所有者相同的权限。也就是说对象创建后，默认只有对象所有者或者系统管理员可以查询、修改和销毁对象，以及通过GRANT将对象的权限授予其他用户。为使其他用户能够使用对象，必须向用户或包含该用户的角色授予必要的权限。
说在前面：
- 每个实例可以多个db，每个db有自己的owner，每个db下可以建立多个schema，每个schema有自己的owner，每个schema下可以创建多张表，每张表都有自己的owner。
- db owner不一定能操作其下面的某个schema。
- schema owner不一定能操作其下面的某张表。
- 支持以下的权限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE和USAGE。不同的权限与不同的对象类型关联。
- 要撤消已经授予的权限，可以使用REVOKE。对象所有者的权限（例如ALTER、 DROP、GRANT和REVOKE）是隐式的，无法授予或撤消。即只要拥有对象就可以执行对象所有者的这些隐式权限。对象所有者可以撤消自己的普通权限，例如，使表对自己以及其他人只读。
- 系统表和系统视图要么只对系统管理员可见，要么对所有用户可见。标识了需要系统管理员权限的系统表和视图只有系统管理员可以查询。
- 数据库提供对象隔离的特性，对象隔离特性开启时，用户只能查看有权限访问的对象(表、视图、字段、函数)，系统管理员不受影响。
- 系统视图中涉及用户、角色及权限相关的视图有 pg_user、pg_authid、pg_roles、pg_auth_members，其中pg_user只存储用户信息；pg_authid存储有关数据库认证标识符（角色）的信息、pg_roles视图提供访问数据库角色的相关信息;pg_auth_members存储角色的成员关系，即某个角色组包含了哪些其他角色。
- 一个用户实际上就是一个rolcanlogin标志被设置的角色。任何角色（不管rolcanlogin设置与否）都能够把其他角色作为成员。在一个集群中只有一份pg_authid，不是每个数据库有一份。需要有系统管理员权限才可以访问此系统表。
场景案例：
场景一：查询数据对象上用户或角色的权限情况（对象维度）
查询方法：
| SELECT relname,relacl FROM pg_class where relname='tablename'; |
示例：
注释：
rolename=xxxx/yyyy --赋予一个角色的权限
=xxxx/yyyy --赋予public的权限
xxxx表示赋予的权限，yyyy表示授予该权限的角色。
参数说明：
场景二：查询用户及角色具有的权限情况（用户维度）
查询方法：
| select * from information_schema.table_privileges where grantee='user'; |
示例：
注释：
grantor :赋权用户
grantee：被赋权用户
table_catalog：数据库名
table_schema：模式名
table_name：对象名
privilege_type：被赋予的权限
场景三：创建新用户，把归属其他用户的对象的权限赋予新用户
赋权顺序：数据库-->模式-->对象
赋权方法：
| --首先赋予用户数据库的连接权限（此权限默认开启） grant CONNECT ON DATABASE <database> to <user>; -- 赋予用户指定schema的usage权限 GRANT USAGE ON SCHEMA <schema> TO <user>; --赋予用户当前schema下所有表及视图的增删改查权限 GRANT SELECT,INSERT,UPDATE,DELETE,truncate ON ALL TABLES IN SCHEMA <schema> to <user>; --赋予用户当前schema下后续新建的表及视图的增删改查权限（默认情况下匹配schema归属用户所建的表的权限） alter default privileges in schema <schema> grant SELECT,INSERT,UPDATE,DELETE,truncate on tables to <user>; --赋予用户当前schema下后续新建的表及视图的增删改查权限（指定用户所建的表的权限） alter default privileges for user <user> in schema <schema> grant SELECT,INSERT,UPDATE,DELETE,truncate on tables to <user>; --赋予用户当前schema下所有函数及存储过程的执行权限 --赋予用户当前schema下所有序列的查询及使用权限 |
场景四：只读用户赋权
赋权顺序：数据库-->模式-->对象
赋权方法：
| --首先赋予用户数据库的连接权限（此权限默认开启） grant CONNECT ON DATABASE <database> to <user>; -- 赋予用户指定schema的usage权限 GRANT USAGE ON SCHEMA <schema> TO <user>; --只赋予用户当前schema下所有表及视图的查询权限 GRANT SELECT ON ALL TABLES IN SCHEMA <schema> to <user>; --赋予用户当前schema下后续新建的表及视图的增删改查权限（默认情况下匹配schema归属用户所建的表的权限） alter default privileges in schema <schema> grant SELECT,INSERT,UPDATE,DELETE,truncate on tables to <user>; --赋予用户当前schema下后续新建的表及视图的增删改查权限（指定用户所建的表的权限） alter default privileges for user <user> in schema <schema> grant SELECT,INSERT,UPDATE,DELETE,truncate on tables to <user>; --赋予用户当前schema下所有函数及存储过程的执行权限 --赋予用户当前schema下所有序列的查询及使用权限 |
场景五：用户/角色的权限赋给其他用户/角色
创建角色，赋予了login权限，则相当于创建了用户，如果没有赋予login权限，则这个角色只能在pg_roles里面看到，而在pg_user里面看不到。
实际业务场景中可以把用户/角色的权限继承给另一个用户/角色，赋予语法：
| grant u1 to u2; |
查询某个用户/角色是否继承了其他用户、角色的权限，方法如下：
| select * from pg_roles where rolname in ('u1','u2','u3'); |
rolinherit字段为t表示改角色继承了其他角色的权限。
查询用户/角色之间继承关系，查询方法如下：
| select m.rolname,m.member as member,b2.rolname as grantor,m.admin_option |
示例：
注释：
rolname ：拥有成员的角色名。
member: 属于成员的角色名。
grantor : 赋予此成员关系的角色名。
admin_option: 如果有权限可以把ROLEID角色的成员关系赋予其他角色，则为真。
场景六：获取用户对Schema 权限信息
实际使用场景中，例如业务迁移中，用户需要统计出哪些用户对当前schema具有权限，请参照下述方法查询：
通过查询PG_NAMESPACE系统视图中nspacl字段获取用户对schema的权限信息：例如：
如下图，user01用户对myschema具有Usage权限及CREATE权限，readuser用户对myschema具有Usage权限。
当用户及schema数量很多时，梳理权限信息科参照以下脚本：
| SELECT pn.nspname AS schema_name, COALESCE(NULLIF(role.name, ''::name), 'PUBLIC'::name) AS grantee, "substring"( CASE WHEN "position"(split_part(split_part(','::text || array_to_string(pn.nspacl, ','::text), (','::text || role.name::text) || '='::text, 2), '/'::text, 1), 'U'::text) > 0 THEN ',USAGE'::text ELSE ''::text END || CASE WHEN "position"(split_part(split_part(','::text || array_to_string(pn.nspacl, ','::text), (','::text || role.name::text) || '='::text, 2), '/'::text, 1), 'C'::text) > 0 THEN ',CREATE'::text ELSE ''::text END, 2, 10000) AS privilege_type FROM pg_namespace pn, ( SELECT pg_roles.rolname AS name FROM pg_roles UNION ALL SELECT ''::name AS name) role WHERE replace((','::text || array_to_string(pn.nspacl, ','::text)),E'\"'::text,''::text) ~~ (('%,'::text || role.name::text) || '=%'::text) AND pn.nspowner > 1::oid; |
通过脚本可查询每个schema与用户权限的对应信息。
示例
- 点赞
- 收藏
- 关注作者
评论（0）