GP权限控制

GP权限控制

–GP权限控制
–create db/user self_bi role oms/dms/wms
–self_bi为例

psql -d postgres
create user self_bi superuser password ‘’
create database self_bi owner self_bi;
grant all privileges on database self_bi to self_bi;
set search_path=ini_dms,mid_dms,show_dms,ini_oms,mid_oms,show_oms; --for superuser self_bi

psql -Uself_bi
create role dms with login;
alter role dms with password ‘’;
create role oms with login;
alter role oms with password ‘’;
create role wms with login;
alter role wms with password ‘’;

create schema ini_dms;
create schema mid_dms;
create schema show_dms;
create schema ini_oms;
create schema mid_oms;
create schema show_oms;
create schema ini_wms;
create schema mid_wms;
create schema show_wms;

–schema 赋权
alter role dms set search_path=ini_dms,mid_dms,show_dms;
alter role oms set search_path=ini_oms,mid_oms,show_oms;
alter role wms set search_path=ini_wms,mid_wms,show_wms;
grant all on schema ini_dms,mid_dms,show_dms to dms;
grant all on schema ini_oms,mid_oms,show_oms to oms;
grant all on schema ini_wms,mid_wms,show_wms to wms;

–也可以收回all再给予usage，这样不能再create，可以select,update ，
–之后此模式下超级用户再创建的表，需要手动赋予查询操作等权限给予响应用户
REVOKE all on schema ini_dms from dms;
grant USAGE on schema ini_dms to dms;
REVOKE all on schema ini_oms from oms;
grant USAGE on schema ini_oms to oms;
REVOKE all on schema ini_wms from wms;
grant USAGE on schema ini_wms to wms;

–table 赋权
–可以获得其它schema下表读权限
grant USAGE on schema ini_dms to wms; --先赋予表所属schema的usage权限
–usage前提下 select/all如下语句都是只有查询权限，没有insert等
grant select on table ini_dms.test1 to wms;
grant all on table ini_dms.test2 to oms;