ORACLE不同用户互相访问（DBLINK）

前提：R_JOB,tablespacetest存在于SYSTEM用户,scott用户也想访问。

1.DBLINK

oracle database link可分为下⾯三类：
1. private：创建的是⽤户级别的dblink，只有创建该dblink的⽤户才可以使⽤这个dblink来访问远程的数据库，同时也只有该⽤户可以
删除这个dblink。
2. public：创建的是数据库级别的dblink，本地数据库中所有的⽤户数据库访问权限的⽤户或者pl/sql程序都能使⽤这个dblink。
3. global：创建的是⽹络级别的dblink，这是对于oracle network⽽⾔的。
 

1.1 创建DBLINK

--如果没有创建权限需要赋予创建权限
grant create public database link to system;

--删除已有的publicDBLINK
drop public database  link TESTLINKsystem01;
 
---创建publicDBLINK写法一
create public database link TESTLINKsystem01 
connect to system 
identified by sys
USING'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
         (HOST = 127.0.0.1)
         (PORT = 1521))
       (CONNECT_DATA = (SERVER = DEDICATED)
             (SERVICE_NAME=orcl)))';
             
---创建publicDBLINK写法二
create public database link TESTLINKsystem01
connect to system 
identified by sys
USING'(127.0.0.1/orcl)';

1.2查询DBLINK 

1.2.1 表tablespacetest

---tablespacetest
select *from tablespacetest@testlinksystem02.regress.rdbms.dev.us.oracle.com;

 结果：

 1.2.2 表r_job

select *from r_job@testlinksystem02.regress.rdbms.dev.us.oracle.com
 

 执行发现有CLOB的字段类型的表会报错：

---可执行
create table demo_a as  select * from r_job@testlinksystem02.regress.rdbms.dev.us.oracle.com;

执行以上语句可以将R_JOB中的数据转移到DEMO_A表中。 

2.赋予权限（内容引用网络）

Oracle授权A用户查询B用户的所有表

需求：

新建的用户userA，要授权给他访问用户scott的所有表

有三种两方法：

1)

SQL> conn / as sysdba;

SQL> grant select any table on userA

这种方法的缺点是授予的权限过大，userA不仅可以访问scott下的所有表，也可以访问其他用户包括sys,system下的所有表。

2)

SQL> conn scott/tiger;

SQL> select 'GRANT SELECT ON' || table_name || 'to userA;'  from user_tables

得到的结果如下

grant select on emp to userA;

grant select on dept to userA;

grant select on bonus to userA;

grant select on loc to userA;

再把上面得到的结果逐一执行一遍:

SQL> grant select on emp to userA;

SQL> grant select on dept to userA;

SQL> grant select on bonus to userA;

SQL> grant select on loc to userA;

这种方法的缺点是要执行比较多的语句，如果有100个表，就得执行100个grant语句；

另外scott新建的表不在被授权的范围内，新建的表要想被userA访问，也得执行grant语句:

grant select on 新建的表 to userA;

(3)使用游标

先创建两个用户

SQL> create user test1 identified by oracle;

User created.

SQL> create user test2 identified by oracle;

User created.

授权

SQL> grant connect, resource to test1;

Grant succeeded.

SQL> grant connect, resource to test2;

Grant succeeded.

在test2下建立一个表作测试用

SQL> conn test2/oracle;

Connected.

SQL> create table t(id number);

Table created.

创建角色并用游标给角色授权

SQL> conn /as sysdba;

Connected.

SQL> create role select_all_test2_tab;

Role created

SQL>

declare

CURSOR c_tabname is select table_name from dba_tables where owner = 'TEST2';

v_tabname dba_tables.table_name%TYPE;

sqlstr    VARCHAR2(200);

begin

open c_tabname;

loop

fetch c_tabname into v_tabname;

exit when c_tabname%NOTFOUND;

sqlstr := 'grant select on test2.' || v_tabname ||' to select_all_test2_tab';

execute immediate sqlstr;

end loop;

close c_tabname;

end;

/

PL/SQL procedure successfully completed.

把角色授权给test1

SQL> grant select_all_test2_tab to test1;

Grant succeeded.

尝试用test1访问test2的表

SQL> conn test1/oracle;

Connected.

SQL> select * from test2.t;

no rows selected

在test2下新建表

SQL> conn test2/oracle;

Connected.

SQL> create table ta(id number);

Table created.

尝试用test1访问新建的表

SQL> conn test1/oracle;

Connected.

SQL> select * from test2.ta;

select * from test2.ta

*

ERROR at line 1:

ORA-00942: table or view does not exist

结论：与第二种方案相比，用这种方式不需逐一把test2下的表授权给test1访问，但test2新建的表无法被test1访问。

赋予权限方法来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30373263/viewspace-2123445/，如需转载，请注明出处，否则将追究法律责任。