Oracle database 创建只读账号（新建用户与只读用户）

创建用户

drop user zdba;
create user zdba identified by zyz123456;
grant create session to zdba  ;     #连接权限
grant select any dictionary to zdba ;    #查看所有系统视图权限
 
1
2
3
4
5

---

创建只读账号

create user fd_or identified by zyz123456;

grant connect to fd_or;
1
2
3

根据对某些用户的对象只读生成授权语句

对于表与视图分配select权限

#对于表与视图分配select权限

select 'grant select on '||owner||'.'||object_name||' to fd_or;'
from dba_objects
where  object_type in ('TABLE','VIEW') AND 
owner  in (select username from dba_users where username like 'BUF_%' or username like 'ODS_%'  OR username  in ('DW2','DW2_READ','HDM_DEV') );
1
2
3
4
5
6

对于物化视图分配select权限

#对于物化视图分配select权限
select 'grant select on '||owner||'.'||object_name||' to fd_or;'
from dba_objects
where  object_type in ('MATERIALIZED VIEW') AND 
owner  in (select username from dba_users where username like 'BUF_%' or username like 'ODS_%'  OR username  in ('DW2','DW2_READ','HDM_DEV') );
1
2
3
4
5

对于序列分配select权限

#对于序列分配select权限
select 'grant select on '||owner||'.'||object_name||' to fd_or;'
from dba_objects
where  object_type in ('SEQUENCE') AND 
owner  in (select username from dba_users where username like 'BUF_%' or username like 'ODS_%'  OR username  in ('DW2','DW2_READ','HDM_DEV') );
1
2
3
4
5

对于过程与函数分配debug权限

#对于过程与函数分配debug权限
select 'grant debug on '||owner||'.'||object_name||' to fd_or;'
from dba_objects 
where   object_type  in ('PROCEDURE','FUNCTION') and 
owner  in (select username from dba_users where username like 'BUF_%' or username like 'ODS_%'  OR username  in ('DW2','DW2_READ','HDM_DEV') );
1
2
3
4
5

---

dba_objects 可以查看所有用户的所有对象