10g
Managing Database Links | DB LINK
DATABASE LINKS
The central concept in distributed database system is Database Link. A dblink allows (client) users to access data on remote database. A connection between from one database to another in Same host.
A connection between two physical database servers. i.e., (from an oracle database server to another database server).
Remember the link is ONE-WAY-COMMUNICATION that means , I have two databases orclprod=(A), and orcltest=(B).
If I create a db link from orclprod (A) database to orcltest (B) database, then A can access information’s from B but by using same link B cannot access the information from A.
Why Database Links ?
The great advantage of database link is (it allows) users to access another users objects in a remote database. ( TO
Q
uery and DML Operations).Three types of Database Links
Public database link Private database link Global database link If db link is public, then all users in the database have access.
If db link is private, only that user having access (who has created the link). PRIVATE DATABASE LINK , created on behalf of a specific user.
10g
Managing Database Links | DB LINK
Public Vs Private Vs Global DB LINKS
PRIVATE DBLINK is more secure than PUBLIC/GLOBAL link ,
because only the owner of the private db_ link or PL/SQL subprograms in the schema can use this link to access database objects.
PUBLIC DBLINK creates DATABASE WIDE link ,
All users and PL/SQL sub programs in the database can use the link to access database objects.
GLOBAL DBLINK (centralized) creates NETWORK WIDE link ,
Users and PL/SQL subprograms in any database can use a global link to access objects.
POINTS TO NOTE :
When many users require an access path to remote oracle database, Oracle recommends to create PUBLIC database link for all users.
When Oracle uses a directory server, an administrator can easily manage global database links for all databases (DB LINK is centralized).
Database Users of DB LINKS - (Security Context)
When creating the db link , need to determine which user should connect to the remote database to access the data.
FIXED USER CURRENT USER CONNECTED USER
10g
Managing Database Links | DB LINK
FIXED USER LINK
Whose USERID/PASSWORD is part of the link definition. Users connect using the USERNAME/PASSWORD referenced. Every time the link connects with the same USERID/PASSWORD.
SQL> CREATE DATABASE LINK <db_link>
CONNECT TO <user_name> IDENTIFIED BY <password> USING 'tns_alias_name' ;
SQL> CREATE PUBLIC DATABASE LINK <db_link> CONNECT TO <user_name> IDENTIFIED BY <password> USING 'tns_alias_name' ;
SQL> show user;
USER is "USER1"
SQL> create database link user1_dblink connect to user1 identified by user1 using 'orcltest';
Database link created.
SQL> select owner , db_link , username , host from dba_db_links 2 where username='USER1';
OWNER DB_LINK USERNAME HOST
10g
Managing Database Links | DB LINK
SQL> show user;
USER is "USER2"
SQL> create public database link user2_dblink connect to user2 identified by user2 using 'orcltest';
Database link created.
SQL> select owner , db_link , username , host from dba_db_links where username='USER2';
OWNER DB_LINK USERNAME HOST
PUBLIC USER2_DBLINK USER2 orcltest
Accessing user2 objects from user1 using public dblink SQL> show user;
USER is "USER1"
SQL> insert into user2.tab2@user2_dblink values(1000,'green','orcl');
1 row created.
SQL> update user2.tab2@user2_dblink SET dept='oracle';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from user2.tab2@user2_dblink; NO NAME DEPT
10g
Managing Database Links | DB LINK
Public Fixed User Link Vs Private Fixed User Link
create database link link1 connect to SCOTT identified by TIGER using 'orcltest'; (Private Fixed)
create Public database link link2 connect to SCOTT identified by TIGER using 'orcltest'; (Public Fixed)
link1 and link2 using net_service_name orcltest as well as
scott using passowd tiger.
Database link from one Database to another in Same-Host
orclprod db1 and orcltest db2If ORCLPROD needs to see ORCLTEST, then in ORCLPROD, create a database link pointing to ORCLTEST. DBLINKS acts as bridges between two databases. They are usable in SOURCE DATABASE to create a link in (target) database.
Checking
orcltest
DatabaseSQL> select name from v$database; NAME
ORCLTEST
SQL> show parameter service_name;
NAME TYPE VALUE
10g
Managing Database Links | DB LINK
SQL> show user;
USER is "SCOTT"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE TAB2 TABLE SAMP TABLE 6 rows selected.
SAMP table is exist in ‘orcltest’ database. I want to access (samp) table. using dblink from ‘orclprod’ database. So I create a dblink in ORCLPROD, pointing to ORCLTEST.
In
orclprod
Databaseuser1 ( exist in ORCLPROD database ) trying to access samp table from ORCLTEST database using dblink.
SQL> grant create database link to user1;
Grant succeeded.
SQL> show user;
10g
Managing Database Links | DB LINK
SQL> create database link testlink connect to scott identified by tiger
using 'orcltest';
Database link created.
SQL> select * from scott. samp@testlink; DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
DDL OPERATIONS using testlink
SQL> insert into scott.samp@testlink values(60 ,'IT', 'NEWJERSY');
1 row created.
SQL> delete from scott.samp@testlink where DNAME='IT';
1 row deleted.
SQL> commit;
10g
Managing Database Links | DB LINK
Creating DATABASE LINKS (PUBIC Vs PRIVATE )
To Create db_link , Should need following Permission ;
Create database Link
To create a Private Database Link , should have CREATE DATABASE LINK system privilege.
To Create Public db_link , Should need following Permission ;
Create Public Database Link Drop Public Database Link
To create a Public Database Link , should have the CREATE PUBLIC DATABASE LINK system privilege.
How to check ( If a database link is Private or Public ?
If the value of the OWNER column of the view (dba_db_links) is PUBLIC , It is Public database link. All other values indicate it is Private database link.
CREATING PUBLIC DATABASE LINK
To create a public database link , use the keyword PUBLIC.
A Public database link , that can be used by any user.
Create [PUBLIC] DATABASE LINK <db_link_name> CONNECT TO <user_name>
10g
Managing Database Links | DB LINK
Create PUBLIC DATABASE LINK SAMPLE CONNECT TO rose
IDENTIFIED BY rose USING ‘testdb’
If we omit PUBLIC option , the database link is PRIVATE.
Notice : How the tnsnames.ora alias goes inside single quotes.
Creating PRIVATE DATABASE LINKS
SQL> CREATE DATABASE LINK mylink
CONNECT TO scott IDENTIFIED BY tiger USING 'orcl' ;
To Create a Fixed user database link , (a fixed user's username and password ) must be specified to connect to the remote database.
SQL> SHOW USER
USER is "ROSE"
SQL> CREATE PUBLIC DATABASE LINK mylink1
CONNECT TO rose IDENTIFIED BY rose USING 'orcl' ;
Using this fixed database link , all users in the database , can access.
SQL> SELECT * from rose.tab1@mylink1
FIXED USER LINKS always having USERNAME/PASSWORD associated with the connect string. The username/password are stored in data dictionary tables. If we omit public keyword , then the database link is private.
10g
Managing Database Links | DB LINK
Privileges for Creating Database Links
The following table illustrates which privileges are required on which database for which type of link ;
Privilege Database Required For
CREATE DATABASE LINK Local Private database link
CREATE PUBLIC DATABASE LINK Local Public database link
CREATE SESSION Remote Any type of database link
ROLE_SYS_PRIVS
Describes System Privileges granted to roles. Information is provided only about roles to which the user has access i.e.
Privileges assigned to roles and available to the currently logged user. So, we can query (ROLE _SYS _PRIVS) view , which privileges currently available for logged user. Let’s Check with two users ( SYS and ROSE ).
SQL> show user;
USER is "SYS"
SQL> SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS WHERE PRIVILEGE IN ( 'CREATE SESSION',
'CREATE DATABASE LINK' , 'CREATE PUBLIC DATABASE LINK') Database Link Privileges
CREATE SESSION
CREATE PUBLIC DATABASE LINK CREATE DATABASE LINK
10g
Managing Database Links | DB LINK
Checking from user “ROSE” SQL> show user;
USER is "ROSE"
SQL> SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS WHERE PRIVILEGE IN ( 'CREATE SESSION',
'CREATE DATABASE LINK' , 'CREATE PUBLIC DATABASE LINK') Database Link Privileges
CREATE SESSION
Shared Database Links
When a local database is connected to a remote database through a database link. The link is shared because multiple client processes can use the same link simultaneously. Shared database link between local server process and the remote database.
Different users accessing the same schema objects through a database link.
CREATE SHARED DATABASE LINK
SHARED (keyword) controls how many network connections are potentially created between the local and remote databases, it has nothing to do with permissions.
SHARED to use a single network connection to create a public database link that can be shared among multiple users.
10g
Managing Database Links | DB LINK
PUBLIC (SHARED OR NOT) All users can use the database link.
SHARED (PUBLIC OR NOT) Different sessions use the same connection to the remote database through this database link.
POINTS TO NOTE :
SHARED is used to share the same connection from different sessions using the same database link. Its purpose is to limit the number of connections coming from an external database.
PUBLIC (SHARED OR NOT) All users can use the database link.
To create a Shared database link , use the keyword SHARED in the CREATE DATABASE LINK statement.
Creating a Fixed User Shared Link
SQL> create SHARED [public] database link <dblink_name> Connect to scott identified by tiger
AUTHENTICATED BY <user> IDENTIFIED BY <password> using 'tns_alias';
When using the keyword SHARED , the clause AUTHENTICATED BY is required. The schema specified in the AUTHENTICATED BY clause
must exist in the remote database and must be granted at least the CREATE SESSION privilege
10g
Managing Database Links | DB LINK
Specify SHARED to create a database link that can be shared by multiple sessions using a single network connection from the source database to the target database.
After a connection is made with shared db_link , operations on the remote database proceed with privileges of the CONNECT TO user or CURRENT_USER not by authenticated schema.
A shared PUBLIC DB-LINK with authentication uses a single network connection to create a PUBLIC database link that can be shared between multiple users with more security. This DB-Link is available only with the
multi-threaded server configuration.
Security Options for DB - LINKS
A dblink defines a communication path from one database to another. When we create a Private or Public database link , we should determine the link will establish connections to specific one i.e. (creating fixed user ,
current user , and connected user database links ). CONNECT TO CLAUSE (FIXED USER)
If we specify CONNECT TO
us
er IDENTIFIED BY password , then the database link connects with specified USERNAME/PASSWORD. so,. we can say FIXED USER LINK.CONNECTED USER and CURRENT USER database links do NOT include any credentials in the definition of the link.
10g
Managing Database Links | DB LINK
CONNECTED USER LINK (10g / 11g)
T
Ocreate a connected user database link , (omit the CONNECT TO clause) This link always connects as the currently connected user. Users connected as themselves which means , they must have an account on the remote database with same (username/password) as their account on the local database.
A local user accessing a database link in which no fixed username and password have been specified. A database link uses the user name and password of each user who is connected to the database.
SQL> CREATE PUBLIC DATABASE LINK <db_link> USING ‘<SERVICE_NAME>’;
SQL> CREATE PUBLIC DATABASE LINK redwood USING ‘orcl’ ;
Any user connected to the local database can use the redwood dblink. The connected user in the local database who uses the database link determines the remote schema. If scott is the connected user and uses the database link, then the database link connects to the remote schema scott.
SQL> conn u1/u1
Connected.
SQL> create public database link linktest using 'orcl';
10g
Managing Database Links | DB LINK
SQL> conn scott/tiger
Connected.
SQL> select * from scott.tab1@linktest;
NO NAME DEPT ***** ***** ****** ***** ***** ******
Any user connected to the local database can use the linktest dblink. SQL> conn hr/hr@orcl
Connected.
SQL> select * from hr.regions@testlink;
REGION_ID REGION_NAME *** *****
*** *****
Dblink testlink used by user (hr and scott) but the dblink was created by user ‘u1’ as Public. No USERNAME/PASSWORD have been supplied when creating connected user database link.
If we omit , (CONNECT TO user IDENTIFIED BY password ) and (CONNECT TO CURRENT_USER ) clauses , then the database link connects to the remote database as the locally connected user so, we can say CONNECTED USER LINK.
10g
Managing Database Links | DB LINK
CURRENT USER LINK (11g)
Current user links are an aspect of the Oracle Advance Security Option. To use current user database link , the current user must be a global user i.e. Authenticated by the Oracle Security Server.. This method is very secure and is part of Oracle Advanced Security.
SQL> CREATE DATABASE LINK <db_link>
CONNECT TO CURRENT_USER
USING ‘<SERVICE_NAME>’;
The user who issues this statement must be a global user registered with the LDAP directory service.
POINTS TO NOTE
Connected user & Current user database links don’t include any credentials in the definition of the link.
Example for Connected User link and Current User Link
Connected user and Current user database links don’t include any credentials in the definition of the link.
SQL> CREATE PUBLIC DATABASE LINK <link_name> USING
'[remote_database]';
SQL> CREATE PUBLIC DATABASE LINK <link_name> CONNECT TO CURRENT_USER using '[remote_database]';
10g
Managing Database Links | DB LINK
What is ORA-02082
A loopback dblink must have a connection qualifier
SQL> Select * from global_name;
ORCLPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM
Global name of the database is orclprod (db_name) and the default
db_domain is REGRESS.RDBMS.DEV.US.ORACLE.COM.
I am trying to create a database link named orclprod (without any domain) it takes name as ORCLPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM
(original database link name + default domain) which is equivalent to
global_name of the database and thus error occurred, (see screenshot) because dblink name must not be equal to the global database name.
10g
Managing Database Links | DB LINK
SQL> alter database rename global_name to prod;
Database altered.
SQL> Select * from global_name;
PROD.REGRESS.RDBMS.DEV.US.ORACLE.COM
CLOSING AND DROPING DB-LINK
STANDARD LINK Vs PUBLIC LINK
SQL> DROP database link <link_name> SQL> DROP database link testlink;
SQL> DROP PUBLIC DATABASE LINK <link_name>; SQL> DROP PUBLIC DATABASE LINK testlink1
10g
Managing Database Links | DB LINK
Revoke the Database link
SQL > revoke create database link from <user_name>;
SQL > revoke create database link from scott ; DBLINK - POINTS TO REMEMBER
DB-LINK S have Three main Dimensions
A DB-LINK is a pointer from one database to another.
OWNERSHIP SECURITY CONTEXT SHARING - PUBLIC - FIXED USER - SHARED - PRIVATE - CONNECTED USER - NON-SHARED - GLOBAL - CURRENT USER
PRIVATE DATABASE LINKS are created in one’s own schema. Requires CREATE DATABASE LINK SYSTEM PRIVILEGE.
PUBLIC DB LINKS are on the PUBLIC schema - Used by any user.
10g
Managing Database Links | DB LINK
DATABASE LINK VIEWS
V$DBLINK GV$DBLINK ALL_DB_LINKS
DBA_DB_LINKS USER_DB_LINKS QUERYING DB- LINKS
SQL> select db_link, owner , nvl(username, '*******') username, host, to_char(created , 'DD-MM-YYYY HH24:MI:SS') created from dba_db_links order by host, owner, db_link;
DB - Link Security Issues ( DIS – ADVANTAGES)
When we create database link , DBA role users will able to do anything in database. i.e. who gains access to a database link can execute queries with the privileges of the DBLINK account .
SQL> conn u1/password@orcltest
Connected.
SQL> create database link firstlink connect to 2 u1 identified by u1 using 'orcltest';
Database link created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
TAB1 TABLE
PRIVATE DATABASE LINK created by user ‘u1’. Using sys.link$ view we can trace password value i.e. password is saved as ‘HASH’.
10g
Managing Database Links | DB LINK
POINTS TO NOTE :
In 10g R2 sys.link$ in oracle contains a new column PASSWORDX that contains the “ Encrypted Database Link Passwor ”. Let’s check.
Oracle does NOT store the actual password and it is HASHED, not encrypted. A HASH is one-way; can't get the clear text from the hash.
SQL> select name , userID, PASSWORDX from sys.link$ where name='FIRSTLINK';
NAME USERID PASSWORDX
FIRSTLINK U1 0571BFFD549D9B6824DFDF888A9EFCE10A
SQL> conn scott/tiger@orcltest
Connected.
SQL> create public database link firstlink1 2 connect to
3 u1 identified by values '0571BFFD549D9B6824DFDF888A9EFCE10A';
Database link created.
SQL> select * from u1.tab1@firstlink2; NO NAME DEPT
**** ****** ****** **** ****** ******
Security Issue of using Database link. Imagine what could be happened next. DATABASE LINKS can be made secure through a combination of technical
10g
Managing Database Links | DB LINK
Technical and Process Controls for DB - LINKS Use Private Database links, instead of Public links.
Use accounts with minimal privileges to access the database link.
Restrict the IP addresses from which the dblink connection may originate.
Assign different accounts for dblinks from different sources - This allows better auditing and tracking.
TNSNAMES.ORA FILE and LISTENER.ORA FILE linux> $ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
linux> $ ls -l
-rw-r--r-- 1 oracle oinstall 587 May 12 08:06 listener.ora -rw-r--r-- 1 oracle oinstall 694 May 22 15:28 tnsnames.ora
10g
