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

本文最后更新于 1523 天前，其中的信息可能已经有所发展或是发生改变。

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;

发送评论编辑评论

发送评论 编辑评论

发送评论编辑评论