1.创建用户
--创建用户test_user
create user test_user with password '1234567';
--授权用户可以连接cnooc库
grant connect on database cnooc to test_user
2.删除用户
--删除用户前,需要删除用户的对象
drop owned by test_user;
drop role test_user;
3.将schema所有权限授予test_user
授予dw_ods所有权限给test_user
grant usage on schema "dw_ods" to test_user;
grant all on schema "dw_ods" to test_user;
grant all on all tables in schema "dw_ods" to test_user;
-- 撤销授权
revokeall on all tables in schema "dw_ods" to test_user;
将test_user的所有权限授给test_user1
-- 授予test_user 所有权限给test_user1
grant test_user to test_user1;
-- 撤销
revoke test_user from test_user1;
--查询test_user所有权限分配给了哪些用户
select rolname from pg_authid
where oid in (
select member from pg_auth_members
where roleid = ( select oid from pg_authid where rolname = 'test_user')
)
3.授予查询权限
-- 授予单个表查询权限dw_ods.t1
grant select on "dw_ods"."t1" to test_user;
-- 授予所有表查询权限
grant select on all tables in schema "dw_ods" to test_user;
-- 对于test_user1后续创建的表,test_user依然有权限查询
alter default privileges for user test_user1 in schema "dw_ods" grant select on tables to test_user;
-- 撤销单个表查询权限dw_ods.t1
revoke select on "dw_ods"."t1" from test_user;
-- 撤销所有表查询权限
revoke select on all tables in schema "dw_ods" from test_user;
-- 撤销
alter default privileges for user test_user1 in schema "dw_ods" revoke select on tables from test_user;
--查询test_user1给哪些用户所有表的操作权限
select a.defaclacl from pg_default_acl a, pg_roles r, pg_namespace n
where a.defaclrole = r.oid and a.defaclnamespace = n.oid
and r.rolname = 'test_user1' and n.nspname = 'dw_ods';
alter default privileges允许你设置将被应用于未来要创建的对象的特权,不会影响分配给已经存在的对象的特权,只能修改用于模式、表(包括视图和外部表)、序列、函数和类型(包括域)的特权.
alter default privileges并不会改变已有用户的权限,只有在有对象创建时,才会根据alter default privileges定义的情况给指定的用户加权限,这个语句有点象是在建对象时加了一个权限触发器。