Greenplum常用给用户授权语句
1.创建用户并修改密码
-- 创建用户并设置密码为123456
create role username with login password '123456';
-- 修改用户密码
alter role username with password '123456';
-- username:用户名
2.给用户schema的所有权限
-- 创建数据库
create schema schemaname;
-- 授权(数据库所有权限)
GRANT all ON schema schemaname TO username;
grant all on schema schemaname to username;
grant all on schema schemaname to username;
-- username:用户的名字
-- schemaname:schema 的名字
3.表与函数的权限
grant select on tablename to username;
grant all on function functionName to username;
alter function functionName owner to username;
-- tablename:表的名字
-- username:用户的名字
-- functionName:function的名字
4.查询用户拥有的表的权限
select * from information_schema.table_privileges where grantee='username' and table_schema='schemaname' and table_name='tablename';
select DISTINCT * from (
select col.table_schema||'.'||col.table_name as tablename from information_schema.columns col where
col.table_schema='schemaname' and col.table_name like 'tablename'
order by col.ordinal_position ) astablename;
-- tablename:表的名字
-- username:用户的名字
-- schemaname:schema 的名字
5.创建授权表的函数语句
5.1 创建授权函数
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
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;
-- GRANT SELECT ON:授权的类型
5.1 授权给用户表的权限
-- 授权
select grant_on_all_tables('schemaname','username');
-- 删除函数
drop FUNCTION grant_on_all_tables(schema text, usr text);
-- username:用户的名字
-- schemaname:schema 的名字
Greenplum常用给用户授权语句
https://www.gmtgo.com/21133.html