Oracle访问同义词连接超时,利用同义词解决oracle用户访问其它schema的对象

由于应用需要，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