oracle数据库新增只读用户,创建只读用户一般密码稍简单,因此需要修改策略文件
1.–查询策略文件
select * from dba_profiles where profile=‘DEFAULT’;
2.–变更密码策略
alter profile default limit password_verify_function null;
3.–创建用户read,密码123456
create user read identified by 123456;
4.–授权
grant connect to READ;
grant create synonym to READ;
grant create session to READ;
5.生成相关表的只读sql
select ‘grant select on ‘||owner||’.’||object_name||’ to cexim;’
from dba_objects
where owner in (‘EIBCNK’)
and object_type=‘TABLE’ and ;
执行sql
6.去掉另外一个用户的前缀
select ‘create or replace SYNONYM cexim.’ || object_name|| ’ FOR ’ || owner || ‘.’ || object_name|| ‘;’
from dba_objects
where owner in (‘EIBCNK’)
and object_type=‘TABLE’;
执行sql
7.–恢复密码策略
alter profile default limit password_verify_function verify_function;