Oracle新增只读用户

其中假设数据提取原用户为：occ

新增的用户名称：db_tableau

--创建用户
create user db_tableau Identified By 123 
default tablespace occ_data  
temporary tablespace occ_data_temp;

grant connect to db_tableau ;
grant create synonym to db_tableau;
grant create session to db_tableau;

--1.查询所有要更新的表-赋权
select 'grant select on '||owner||'.'||object_name||' to db_tableau;'
from dba_objects
where owner in ('OCC') and object_type='TABLE';
--2.更新的表
SELECT 'create or replace SYNONYM db_tableau.' || object_name|| ' FOR ' || owner || '.' || object_name || ';' from dba_objects 
where owner in ('OCC') and object_type='TABLE';
--3.查询所有要更新的视图-赋权
select 'grant select on '||owner||'.'||object_name||' to db_tableau;'
from dba_objects
where owner in ('OCC') and object_type='VIEW';
--4.更新的视图
SELECT 'create or replace SYNONYM db_tableau.' || object_name|| ' FOR ' || owner || '.' || object_name || ';' from dba_objects 
where owner in ('OCC') and object_type='VIEW';