gp集群数据库-创建只读用户并赋查询权限

/ 0评 / 5

CREATE ROLE select_user login password 'Js_gp123!@#';
或者下面都可以:
CREATE USER select_user WITH PASSWORD 'Js_gp123!@#';

把USAGE权限给到select_user
grant usage on schema smartinsight to select_user;

#单独赋查询的权限
GRANT SELECT ON TABLE zz_pm_nr_nsa_nrcellcu_minute20200923 TO select_user;

GP集群虽然和pgsql差不多 但是也有本质区别因此不能使用:
grant select on all tables in schema smartinsight to select_user;这个语句 无效的
详情:https://blog.csdn.net/weixin_42658788/article/details/88040209

解决办法-创建一个函数:

create or replace function grant_on_all_tables(schema text, usr text) 
returns setof text as declare 
   r record ; 
   grantstmt text; 
begin 
   for r in select * from pg_class c, pg_namespace nsp 
       where c.relnamespace = nsp.oid AND c.relkind='r' AND nspname = schema and (relname like '%JS_MR_SERVICE_CELL_MINUTE_%' or relname like '%JS_PM_LTE_EUTRANCELL_MINUTE_%')
   loop 
     grantstmt = 'GRANT SELECT ON "'|| quote_ident(schema) || '"."' || 
quote_ident(r.relname) || '" to "' || quote_ident(usr) || '"'; 

     EXECUTE grantstmt; 
     return next grantstmt; 
   end loop; 
end; language plpgsql;

 

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注