Greenplum 权限基本命令

创建权限问题：

1.1）创建role CREATE ROLE "yfkj" CREATEDB;

1.2) 创建Schema CREATE SCHEMA IF NOT EXISTS fiproduct_v_${tenant};

1.3) 给 schema 赋权 GRANT USAGE ON SCHEMA uorders_v_${tenant} TO yfkj WITH GRANT OPTION;

1.4）function 给表赋权 ：

CREATE OR REPLACE FUNCTION "public"."grant_on_all_tables"(IN schemas text, IN usr text, IN select_or_insert text) RETURNS SETOF "text"
AS $BODY$
declare
r record ;
grantstmt text;
begin
for r in select * from pg_class pc, pg_namespace nsp
where pc.relnamespace = nsp.oid AND pc.relkind='v' AND nsp.nspname = schemas
loop
grantstmt = 'GRANT '||quote_ident(select_or_insert)||' ON "'|| quote_ident(schemas) || '".' || quote_ident(r.relname)|| ' to "' || quote_ident(usr) || '"';
EXECUTE grantstmt;
return next grantstmt;
end loop;
end;
$BODY$
LANGUAGE plpgsql

1.5）给表赋权具体 select grant_on_all_tables('uorders_v_${tenant}','yfkj','select');

1.6）做数仓的时候给某些表做视图处理：

DROP VIEW IF EXISTS "fiproduct_v_${tenant}"."aa_billhistory";
CREATE VIEW "fiproduct_v_${tenant}"."aa_billhistory" AS
SELECT * FROM "fiproduct"."aa_billhistory" t1,
( SELECT id as ids from "fiproduct"."tenant"
where tenantcenter_id='${tenant}') t_id
where t1.tenant_id=t_id.ids;
撤销权限问题：

2.1） 撤销用户在数据库级别的权限 revoke all on database databasename from username;

2.2）撤销所有在schem 中的权限 revoke all on schema schema1,schema2 from username;

2.3）撤销用户在table 上的权限 select 'revoke all on '||table_schema||'.'||table_name||' from username cascade; ' from
information_schema.table_privileges
where grantee='username';

2.4）撤销用户在function 权限 revoke all on function schemaname.functionname from username;

2.5）移除用户角色 drop role if exists username;

2.6）判断角色是否存在：select * from gp_toolkit.gp_roles_assigned where rarolename = 'username'


本文来源：https://blog.csdn.net/czq850114000/article/details/103297293