openGauss 用户权限统计及权限回收

• 适用范围

• 问题概述

• 解决方案

• 一、环境配置

• 二、用户权限统计(解析acl权限方式)

  • 1、database 权限

  • 2、schema 权限

  • 3、table 权限

  • 4、table column 权限

  • 5、tablespace 权限

  • 6、sequence 权限

  • 7、function 权限

  • 8、procedure 权限

  • 9、view 权限

  • 10、系统权限

  • 11、role 权限

  • 12、any 权限

• 三、用户权限统计(查询information_schema 相关视图方式)

  • 1、table、view 权限

  • 2、table column 权限

  • 3、function、procedure 权限

  • 4、usage 权限

适用范围

操作系统：CentOS 7.6
数据库：openGauss 6.0.1

问题概述

解析opengauss 数据库中acl权限相关字段，如pg_class 中relacl 字段，将其转换成对应的select、update等权限信息，并拼接revoke 相应权限的sql 语句。

解决方案

一、环境配置

二、用户权限统计

(解析acl权限方式)

本案例为在postgres 库中创建用户，然后创建testdb 数据库，后续在testdb 库中创建用户对象并进行相关授权操作。

创建用户testuser:

\c postgrescreate user testuser with password 'Test1234';

创建数据库testdb

create database testdb;\c testdb;

1、database 权限

授予testuser 访问testdb库以及在testdb库创建新schema 的权限

grant connect,create on database testdb to testuser;

查询testuser的database 权限​​​​​​​

with t as (select datname,(aclexplode(datacl)).grantee,(aclexplode(datacl)).privilege_typefrom pg_database)select t.datname,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on database '||t.datname||' from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

2、schema 权限

创建schema1

create schema schema1;

授予testuser 在 schema1 模式创建对象的权限

grant create,usage on schema schema1 to testuser;

查询testuser 的schema 权限​​​​​​​

with t as (select nspname "schema",(aclexplode(nspacl)).grantee,(aclexplode(nspacl)).privilege_typefrom pg_namespace)select t.schema,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on schema '||t.schema||' from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

3、table 权限

创建public.t1 表

create table public.t1 (id int);

授予testuser 对 public.t1 的增删改查权限

grant select,insert,update,delete on table public.t1 to testuser;

查询testuser 的table 权限​​​​​​​

with t as (select na.nspname "schema",cl.relname,(aclexplode(relacl)).grantee,(aclexplode(relacl)).privilege_typefrom pg_class clinner join pg_namespace na on na.oid=cl.relnamespacewhere cl.relkind in ('r','f'))select t.schema,t.relname,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on table '||t.schema||'.'||t.relname||' from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

4、table column 权限

创建public.t2 表

create table public.t2 (id int,name char(5));

授予testuser 对public.t2 表的id列的select、insert 权限

grant select(id),insert(id) on table public.t2 to testuser;

查询testuser 的table column 权限​​​​​​​

with t as (select na.nspname "schema",cl.relname "table_name",attname "column_name",(aclexplode(attacl)).grantee,(aclexplode(attacl)).privilege_typefrom pg_attribute atinner join pg_class cl on cl.oid=at.attrelidinner join pg_namespace na on cl.relnamespace=na.oid)select t.schema,t.table_name,t.column_name,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||'('||t.column_name||') on table '||t.schema||'.'||t.table_name||' from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

5、tablespace 权限

创建tbs1 表空间

create tablespace tbs1 location '/gaussdata/openGauss/tbs';

授予testuser 在tbs1 表空间创建表的权限

grant create on tablespace tbs1 to testuser;

查询testuser 的tablespace 权限​​​​​​​

with t as (select spcname,(aclexplode(spcacl)).grantee,(aclexplode(spcacl)).privilege_typefrom pg_tablespace)select t.spcname,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on tablespace '||t.spcname||' from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

6、sequence 权限

创建public.seq1 序列

create sequence public.seq1;

授予testuser 对 public.seq1 的查询、更新等权限

grant select,update,usage on sequence public.seq1 to testuser;

查询testuser 的sequence 权限​​​​​​​

with t as (select na.nspname "schema",cl.relname,(aclexplode(relacl)).grantee,(aclexplode(relacl)).privilege_typefrom pg_class clinner join pg_namespace na on na.oid=cl.relnamespacewhere cl.relkind in ('S','L'))select t.schema,t.relname,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on sequence '||t.schema||'.'||t.relname||' from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

7、function 权限

创建public.fun1 函数​​​​​​​

create function public.fun1(int,int) returns intas 'select $1+$2;'language sqlimmutablereturns null on null input;

testuser 默认具有fun1 的执行权限
授予testuser 修改fun1属性的权限，但前提是testuser 拥有修改public 中对象的权限​​​​​​​

grant usage,create,alter on schema public to testuser;grant alter on function public.fun1(int,int) to testuser;

查询testuser 的function 权限​​​​​​​

with t as (select pr.oid,na.nspname "schema",pr.proname,(aclexplode(proacl)).grantee,(aclexplode(proacl)).privilege_typefrom pg_proc prinner join pg_namespace na on na.oid=pr.pronamespacewhere pr.prokind='f')select t.schema,t.proname,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on function '||t.schema||'.'||t.proname||'('||pg_get_function_arguments(t.oid)||') from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

8、procedure 权限

创建insert_data_t1 存储过程
实现调用上述创建的seq1 序列，往t1表里插入数据​​​​​​​

create procedure public.insert_data_t1()isbegininsert into public.t1 values(nextval('public.seq1'));end;/

testuser 默认具有insert_data_t1 的执行权限
授予testuser 修改 insert_data_t1 属性的权限，但前提是testuser 拥有修改public 中对象的权限

grant alter on procedure public.insert_data_t1() to testuser;

查询testuser 的procedure 权限​​​​​​​

with t as (select pr.oid,na.nspname "schema",pr.proname,(aclexplode(proacl)).grantee,(aclexplode(proacl)).privilege_typefrom pg_proc prinner join pg_namespace na on na.oid=pr.pronamespacewhere pr.prokind='p')select t.schema,t.proname,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on procedure '||t.schema||'.'||t.proname||'('||pg_get_function_arguments(t.oid)||') from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

9、view 权限

创建view_t1 视图

create view public.view_t1 as select * from public.t1;

授予testuser 查询view_t1 的权限

grant select on table public.view_t1 to testuser;

查询testuser 的view 权限​​​​​​​

with t as (select na.nspname "schema",cl.relname,(aclexplode(relacl)).grantee,(aclexplode(relacl)).privilege_typefrom pg_class clinner join pg_namespace na on na.oid=cl.relnamespacewhere cl.relkind in ('v','m'))select t.schema,t.relname,ro.rolname,t.privilege_type,'revoke '||t.privilege_type||' on table '||t.schema||'.'||t.relname||' from '||ro.rolname||';' as revoke_sqlfrom tinner join pg_roles ro on ro.oid=t.granteewhere ro.rolname='testuser';

10、系统权限

系统权限又称用户属性
查询testuser 的系统权限​​​​​​​

select rolname as user_name,rtrim(decode(rolsuper,'t','SuperUser,')||decode(rolcreaterole,'t','CreateRole,')||decode(rolcreatedb,'t','CreateDB,')||decode(rolcatupdate,'t','CatUpdate,')||decode(rolcanlogin,'t','Login,')||decode(rolreplication,'t','Replication,')||decode(rolauditadmin,'t','AuditAdmin,')||decode(rolsystemadmin,'t','Sysadmin,')||decode(rolmonitoradmin,'t','MonitorAdmin,')||decode(roloperatoradmin,'t','OperatorAdmin,')||decode(rolpolicyadmin,'t','PolicyAdmin'),',') as role_sys_privilegesfrom pg_roles where rolname='testuser';

11、role 权限

创建role1 角色

create role role1 with password 'Test1234';

将role1 角色授予testuser 用户

grant role1 to testuser;

查询testuser 拥有的role 权限​​​​​​​

select ro1.rolname "role",ro2.rolname "user_name",'revoke '||role||' from '||user_name||';' as revoke_sqlfrom pg_auth_members auinner join pg_roles ro1 on ro1.oid=au.roleidinner join pg_roles ro2 on ro2.oid=au.memberwhere user_name='testuser';

12、any 权限

授予testuser 用户create any table,select any table 权限

grant create any table,select any table to testuser;

查询testuser 拥有的any 权限​​​​​​​

select rolname,privilege_type,'revoke '||privilege_type||' from '||rolname||';' as revoke_sqlfrom gs_db_privilege gsinner join pg_roles ro on ro.oid=gs.roleidwhere rolname='testuser';

三、用户权限统计

(查询information_schema 相关视图方式)

修改search_path 参数

set search_path=information_schema;

1、table、view 权限

select * from table_privileges where grantee='testuser' and grantor<>'testuser';

2、table column 权限

注：授予表级select、insert 权限时，同时会将表的全部列的select、insert 权限记录在column_privileges 中。

select * from column_privileges where grantee='testuser' and grantor<>'testuser';

3、function、procedure 权限

​​​​​​​

select * from routine_priv grantee='testuser' and grantor<>'testuser';ileges where

4、usage 权限

select * from usage_privileges where grantee='testuser' and grantor<>'testuser';

本文作者：车金陆，墨天轮数据库管理服务团队技术顾问。