利用同义词解决oracle用户访问其它schema的对象

baidus

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

8244

不知该说些什么。。。。。。就是谢谢

mm111222

如果恐龙是人，那人是什么？

漂亮蓝影

有竞争才有进步嘛

fish3129

我抢、我抢、我抢沙发~

liyao20060101

我是个凑数的。。。

xiaowei8782088

人生自古谁无死，啊个拉屎不用纸!