我要评论前言
创建用户后对数据库对象的读写权限进行修改
使用步骤
1、创建用户
create user cspassword ‘123123';
2、回收default privileges权限
–all tables alter default privileges for user csrevoke all on tables from public;
4、创建数据库
create database wan encoding ‘utf8' template template0;
5、回收其它库的连接权限
select datname from sys_database; select ‘revoke all on database ' || datname || ' from public;' from sys_database where datname not in(‘test',‘template0',‘template1'); select ‘revoke all on database ' || datname || ' from cs;' from sys_database where datname not in(‘test',‘template0',‘template1');
6、赋权数据库权限
grant all on database wan to cs;
7、赋权增，删，改，查
grant select,update,insert,delete,truncate,references,trigger on all tables in schema public to cs;
8、对新建表拥有权限
alter default privileges in schema sch_kcm grant select,insert,update,delete,truncate,references,trigger on tables to cs;
创建只读用户：
create user readonly with encrypted password ‘readonly';
设置默认事务只读：
alter user readonly set default_transaction_read_only=on;
赋予用户连接数据库mytest的权限：
grant connect on database mytest to readonly;
切换到指定库mytest：
\c mytest system
赋予用户表、序列查看权限，进入指定db运行：
把当前库现有的所有在public这个schema下的表的使用权限赋给用户readonly
grant usage on schema public to readonly;
默认把当前库之后新建在public这个schema下的表的使用权限赋给readonly
alter default privileges in schema public grant select on tables to readonly;
赋予用户readonly所有public下的序列的查看权
grant select on all sequences in schema public to readonly;
赋予用户readonly所有public下的表的select权
grant select on all tables in schema public to readonly;
将刚才赋予的权限一一回收
回收schema的usage权限
revoke usage on schema public from readonly;
回收public下所有表的查询权限：
revoke select on all tables in schema public from readonly;
回收public下所有序列的查询权限
revoke select on all sequences in schema public from readonly;
回收默认权限
alter default privileges in schema public revoke select on tables from readonly;
关闭数据库连接权限
revoke connect on database foo from readonly;
关闭默认只读事务设置
alter user readonly set default_transaction_read_only=off;
查看权限是否为空了
\ddp
通过管理员删除readonly用户：
drop user readonly;
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论