GreenPlum 用户权限管理

--创建用户tjbb1授权登录并设置密码，限制连接数8个
create role tjbb1 with login password 'Aa123456' connection limit 8;
--将tjbb1添加到tjbb用户组中
alter group tjbb add user tjbb1;
--创建tjbb1的schema(工作空间)
create schema tjbb1;
--将tjbb1的schema的usage和create权限授权给tjbb1
grant usage,create on schema tjbb1 to tjbb1;
--将tjbb1的schema的usage权限授权给public
grant usage on schema tjbb1 to public;

--收回函数默认赋权public的权限
alter default privileges for role tjbb1 revoke execute on functions from public;
--或者修改schema默认权限
alter default privileges in schema tjbb1 revoke execute on functions from public;

--赋权表默认授权public的select权限
alter default privileges for role tjbb1 grant select on tables to public;
--或者直接对修改schema默认权限
alter default privileges in schema tjbb1 grant select on tables to public;

--动态sql函数
create or replace function tjbb1.sp_exec(character varying)
 returns void
 language plpgsql
 security definer
as $function$ 
begin
    execute $1;
end;
$function$
;
--修改权限
alter function tjbb1.sp_exec(character varying) owner to tjbb1;
grant execute on function sp_exec(varchar) to gpload;