oracle 用户权限批量授予

1、创建新用户 
create user macs identified by 123456 default tablespace users;
create user yun identified by 123456 default tablespace users;


2、授权connect 和 synonym 
grant connect to yun;
grant create synonym to yun;




3、授权查询表的权限
grant select on MOBILE.* to yun;


4、创建同义词
create or replace SYNONYM YUN.M_TMP FOR PDS.M_TMP;
create or replace SYNONYM YUN.M_TMP FOR PDS.M_TMP;


5、完成。测试查询




备注：
1、批量执行授权多表查询权限的查询语句
select 'grant select on '||owner||'.'||object_name||' to t1;'
from dba_objects
where owner in ('PDS‘)
and object_type='TABLE';


2、批量执行创建同义词查询语句
 SELECT 'create or replace SYNONYM YUNWEI.' || object_name|| ' FOR ' || owner || '.' || object_name|| ';'
 from dba_objects 
 where owner in ('PDS')
 and object_type='TABLE';