Create DBlink
database
Link is an object that defines the path from one database to another database. Database link allows you to query remote tables and execute remote programs. Database is necessary in any distributed environment. Also note that database link is a one-way connection.
Permissions required to create DBlink:
Requires CREATEDATABASELINK permission, if it is to create a public
database link must include CREATEPUBLICDATABASELINK permission. At the same time, you also need to have the create on the remote databasesessionpermissions.
◆Query CREATEDATABASELINK permissions
SQL>
select privilege,role from role_sys_privs where privilege like 'CREATE DATABASE%';
PRIVILEGE ROLE
----------------------------------------
--------------------
CREATE
DATABASE LINK
IMP_FULL_DATABASE
CREATE
DATABASE LINK DBA
◆Query CREATEPUBLICDATABASELINK permissions
SQL>
select privilege,role from role_sys_privs where privilege = 'CREATE
PUBLIC DATABASE LINK';
PRIVILEGE ROLE
----------------------------------------
--------------------
CREATE PUBLIC DATABASE LINK IMP_FULL_DATABASE
CREATE PUBLIC DATABASE LINK DBA
◆Check the permissions of create session:
SQL>
select privilege from role_sys_privs where role in ('CONNECT');
PRIVILEGE
--------------------------------------------------------------------------------
CREATE
SESSION
Create dblink syntax:
CREATE
[SHARED][PUBLIC] database link link_name
[CONNECT TO [user][current_user] IDENTIFIED BY password]
[AUTHENTICATED BY user IDENTIFIED BY password]
[USING 'connect_string']
Parameters and keywords:
SHARED: The function of this keyword is that the created DBlink can be shared by multiple users, and the keyword must be used in conjunction with AUTHENTICATED.
PUBLIC: public dblink can be shared by users.
When the database GLOBAL_NAME=TRUE on the source side, the link name must be the same as the global database name global_name) of the remote database; otherwise, it can be named at will.
CONNECT TO clause:
The connect to clause represents two different user types of dblink:
1．current user
database link
2. connected
user database link
[current_user]: requires that the user must be a global user in the remote database (globaluser). Create a current user database link
Execute a storage object (such as a stored procedure) on a remote database. CURRENT_USER is the user to which this object belongs, not the executing user of this object. Its corresponding permissions are consistent with the user to which the object belongs.
If this storage object contains a security setting (using the AUTHID clause specifically).
For example:
create or replace procedure AAAA
authid current_user
is
begin
end；
At this time, CURRENT_USER becomes the calling user of this object, rather than the user of this object, and its corresponding permissions are also consistent with the calling user.
[user] IDENTIFIED BY password]。
The clause here creates a connected user database link. This clause uses a fixed user and password to connect to the remote database.
[AUTHENTICATED BY user
IDENTIFIED BY password]
A user defined on the target instance, which is used to authenticate on the remote database. The user must exist on the remote database. And this user is just used for authentication and has no other function.
[USING 'connect_string']
Defines the service name on the remote data. If this parameter is defined,oracleThe domain that goes to the database will be added by default
The created dblink can be found in all_db_links, dba_db_links, user_db_links.
SQL> create public database link
dblink_p connect to current_user
2 using 'orcl';
Database link created.
SQL> select owner,DB_LINK,HOST from
dba_db_links;
OWNER
DB_LINK
HOST
---------- ----------------------------------------
----------
PUBLIC
DBLINK_P. orcl
Create connected user database link
SQL> create public database link dblink_f connect to YYP identified by oracle
2 using 'orcl';
Database link created.
SQL>
select owner,DB_LINK,HOST from dba_db_links;
OWNER
DB_LINK
HOST
----------
---------------------------------------- ----------
PUBLIC
DBLINK_F. orcl
SQL> select count(1) from
@dblink_f;
COUNT(1)
----------
14
Create current user database link
The experiment failed to be fully carried out due to the need for a directory server.
*First create a global user.
CREATE USER p_yyp
IDENTIFIED GLOBALLY AS
"CN=scott,OU=divisional,O=sybex,C=US"
Here you need to use enterprise directory service (Oracle Internet Directory).
*Connect to connp_yyp
*Create dblink: create public database link dblink_p connect
to current_user using 'orcl';
Using [AUTHENTICATED
BY user IDENTIFIED BY password]
This authentication clause must be used with shared. The meaning of authentication is that when you create and use this link once, you will authenticate with remote data.
SQL>create shared database link dblink_f2
connect to YYP identified by oracle
AUTHENTICATED BY scott IDENTIFIED BY tiger using 'orcl';
Database link created.
SQL> select owner,DB_LINK,HOST from
dba_db_links;
OWNER
DB_LINK
HOST
----------
---------------------------------------- ----------
PUBLIC
DBLINK_F. orcl
YYP
DBLINK_F2. M orcl
Verification: When using DBLINK_F2, you must be under the yyp user.
SQL> conn yyp/oracle
Connected.
SQL> show user
USER is "YYP"
SQL>
select count(1) from @DBLINK_F2;
COUNT(1)
----------
14
SQL> conn scott
Enter password:
Connected.
SQL> select count(1) from
@DBLINK_F2;
select count(1) from @DBLINK_F2
*
ERROR at line 1:
ORA-02019: connection description for
remote database not found
--à When a non-YYP user, the DBlink is not available, but public can be used
database link。
SQL> select count(1) from
@DBLINK_F;
COUNT(1)
----------
14