Data Base Links

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 Query 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.  PUBLIC DATABASE LINK , created for the user group to PUBLIC.

Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu

10g


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

DB Links connect to the remote database in one of the three methods; Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu

10g


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 CONNECT TO

IDENTIFIED BY

USING 'tns_alias_name' ;

SQL> CREATE PUBLIC DATABASE LINK CONNECT TO

IDENTIFIED BY

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

USER1 USER1_DBLINK USER1

HOST orcltest



10g

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

PUBLIC USER2_DBLINK

USER2

HOST 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

1000 green

DEPT oracle


10g


Public Fixed User Link Vs Private Fixed User Link create

database link link1

TIGER using 'orcltest';

connect to

SCOTT identified by

(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  db2 If 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 Database SQL> select name from v$database; NAME ORCLTEST SQL> show parameter service_name; NAME service_names

TYPE string

VALUE orcltest


10g


SQL> show user; USER is "SCOTT" SQL> select * from tab; TNAME

TABTYPE

DEPT

TABLE

EMP

TABLE

BONUS

TABLE

SALGRADE

TABLE

TAB2

TABLE

SAMP

TABLE

CLUSTERID

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 Database user1 ( 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; USER is "USER1"



10g

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; Commit complete.


10g


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 CONNECT TO IDENTIFIED BY

USING ‘’ ;


10g


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


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


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


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 Connect to scott identified by tiger AUTHENTICATED BY IDENTIFIED BY 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

The SHARED keyword have no relation to users who may use the link.


10g


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 user 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


CONNECTED USER LINK (10g / 11g)

TO create 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 USING ‘’; 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'; Database link created.


10g


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


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 CONNECT TO CURRENT_USER

USING ‘’;

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 USING '[remote_database]'; SQL> CREATE PUBLIC DATABASE LINK CONNECT TO CURRENT_USER using '[remote_database]';


10g


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


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 SQL> DROP database link testlink; SQL> DROP PUBLIC DATABASE LINK ; SQL> DROP PUBLIC DATABASE LINK testlink1


10g


Revoke the Database link SQL > revoke create database link from ; 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.

DATABASE LINKS

OWNERSHIP

SECURITY CONTEXT

- PUBLIC

- FIXED USER

- PRIVATE

- CONNECTED USER

- GLOBAL

SHARING

- SHARED - NON-SHARED

- 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.  Requires CREATE PUBLIC DATABASE LINK SYSTEM PRIVILEGE. Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu

10g


DATABASE LINK VIEWS  V$DBLINK 

 GV$DBLINK

 ALL_DB_LINKS

USER_DB_LINKS

DBA_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

TAB1

TABTYPE

CLUSTERID

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


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

FIRSTLINK

U1

PASSWORDX

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 and process controls. Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu

10g


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 linux> $ vi listener.ora


10g


linux> $ vi tnsnames..ora


Data Base Links

Recommend Documents