Oracle private dblink和pubic dblink

標籤：

DB : 11.2.0.3.0


Oracle DBLINK 建立分為private 和 public dblink，預設建立的為private ; private dblink 只有建立的schema才能刪除,sys也刪除不了；public dblink 任意schema都可以刪除,只要許可權夠。

一、PRIVATE DBLINK:
收回dba許可權：
SQL> revoke dba from yoon;

Revoke succeeded.

嘗試串連，無法串連：
SQL> conn yoon/yoon
ERROR:
ORA-01045: user YOON lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

串連sys
SQL> conn / as sysdba
Connected.

授權
SQL> grant connect to yoon;

Grant succeeded.

SQL> grant create database link to yoon;

Grant succeeded.

SQL> conn yoon/yoon
Connected.

 
目前使用者：
SQL> show user
USER is "YOON"

建立dblink：
SQL> create database link dblink_yoon connect to yoon identified by yoon using ‘YOON‘;

Database link created.

串連sys使用者：
SQL> conn / as sysdba
Connected.

刪除dblink：
SQL> drop database link dblink_yoon;
drop database link dblink_yoon
                   *
ERROR at line 1:
ORA-02024: database link not found

SQL> show user
USER is "SYS"
SQL> drop database link dblink_yoon ;
drop database link dblink_yoon
                   *
ERROR at line 1:
ORA-02024: database link not found

查看dblink
SQL> select * from dba_db_links;

OWNER      DB_LINK       USERNAME   HOST       CREATED
---------- ------------- ---------- ---------- ---------
YOON       DBLINK_YOON   YOON       YOON       14-NOV-14

串連建立dblink使用者:
SQL> conn yoon/yoon
Connected.

SQL> drop database link dblink_yoon ;

Database link dropped.

二、PUBLIC DBLINK:

SQL> conn / as sysdba
Connected.
SQL> grant dba to yoon;

Grant succeeded.

SQL> conn yoon/yoon
Connected.
SQL> show user
USER is "YOON"
SQL> create public database link dblink_yoon connect to yoon identified by yoon using ‘YOON‘;

Database link created.

SQL> conn / as sysdba
Connected.

SQL> grant dba to yoon;

 

Grant succeeded.

SQL> conn yoon/yoon
Connected.

SQL> show user
USER is "YOON"

SQL> create public database link dblink_yoon connect to yoon identified by yoon using ‘YOON‘;

Database link created.

SQL> conn / as sysdba
Connected.

SQL> select * from dba_db_links;

OWNER      DB_LINK       USERNAME   HOST       CREATED
---------- ------------- ---------- ---------- ---------
PUBLIC     DBLINK_YOON   YOON       YOON       14-NOV-14

 

SQL> drop public database link dblink_yoon;

Database link dropped.

Oracle private dblink和pubic dblink