由于应用需要,aa能访问bb的所有表。
SQL> create user aa identified by oracle;
User created.
SQL> grant connect,resource to aa;
Grant succeeded.
SQL> create user bb identified by oracle;
User created.
SQL> grant connect,resource to bb;
Grant succeeded.
SQL> grant create synonym to bb;
Grant succeeded.
SQL> conn aa/oracle
Connected.
SQL> create table t1(id number);
Table created.
SQL> insert into t1 values(1);
1 row created.
SQL> insert into t1 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> show user;
USER is "AA"
通过下面语句可以产生出授权的批量语句:
SQL> select 'grant select on '||object_name||' to bb;' from user_objects;
'GRANTSELECTON'||OBJECT_NAME||'TOBB;'
------------------------------------------------------------------------------------------------------------------------
grant select on T1 to bb;
SQL> grant select on T1 to bb;
Grant succeeded.
通过下面语句可以批量产生出创建同义词的语句:
SQL> select 'create or replace synonym '||object_name||' for aa.'||object_name||';' from user_objects;
'CREATEORREPLACESYNONYM'||OBJECT_NAME||'FORAA.'||OBJECT_NAME||';'
------------------------------------------------------------------------------------------------------------------------
create or replace synonym T1 for aa.T1;
将上面产生的语句用bb用户执行:
SQL> conn bb/oracle
Connected.
SQL> create or replace synonym T1 for aa.T1;
Synonym created.
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
T1 SYNONYM
SQL> select * from t1;
ID
----------
1
2
转载于:https://blog.51cto.com/chinadm123/1172156