1、创建新用户
create user macs identified by 123456 default tablespace pdsci;
2、授权connect //没有无法登陆 和 synonym //同义词
grant connect to macs;
grant create synonym to macs;
3、授权查询表的权限
grant select on PDSCI.M_TMP to t1;
4、创建同义词 (查询的时候就不需要使用A.表名,直接使用同义词查询)
create or replace SYNONYM MACS.M_TMP FOR PDSCI.M_TMP;
5、完成。测试查询
备注:
1、批量执行授权多表查询权限的查询语句
select 'grant select on '||owner||'.'||object_name||' to t1;'
from dba_objects
where owner in ('PDSCI')
and object_type='TABLE';
2、批量执行创建同义词查询语句
SELECT 'create or replace SYNONYM MACS.' || object_name|| ' FOR ' || owner || '.' || object_name|| ';'
from dba_objects
where owner in ('PDSCI')
and object_type='TABLE';
联系客服