Database Links

Recently I came across a question on Database Links.

A Database Link, I define as, is an implicit connection from one database to another which is used to reference / extract / update data from/in the remote database.

Here are some "guidelines" I would advise :

1. Check with your IT Security department / Guidelines / SOPs / Standards about whether Database Links are permitted and, if so, what conditions/restrictions apply to them before creating a Database Link.

2. Use "shadow accounts" that can clearly identify where a Link connection came from as the Database Link accounts. That way, a DBA monitoring the remote database can identify a session -- whether for performance tuning or for application maintenance (e.g. if the schema or particular tables are to be taken "offline") or for investigating a "security incident".

3. Such "shadow accounts" have Read Only (or, if really necessary, Update) privileges on *selected* tables only. I see implementations of Database Links that connect to the schema that owns data. That is, in my opinion, not acceptable. Apply the principle of Least Privilege and use a "shadow account".

4. Unless it is explicitly permitted and approved (see point 1), a Database Link in a Test/Devt environment that connects to a Production schema is not to be created. As for UAT/SIT environments, review requirements, get approval and set a limited duration for the availability of the Database Link (have it dropped after the duration).

Here is an example implementation :

The "Remote" database which contains data that is being queried is called "ERPFIN".

The database account that contains the schema is called "SYSADM"

One "Client" database that needs to create a database link is called "FINANLTC"

The account in this database is called "BIZINT". This account needs to query the SYSADM.GL_BALANCES table.

Another "Client" database is called "DASHBRD"

The account in this database is called "SNRMGTVW". This account also needs to query the SYSADM.GL_BALANCES table.

Steps :

1. In "ERPFIN" create two database accounts "FROM_FINANLTC_BIZINT" and "FROM_DASHBRD_SNRMGTVW" with two system privileges "CREATE SESSION" and "CREATE SYNONYM".

2. Grant SELECT on SYSADM.GL_BALANCES to "FROM_FINANLTC_BIZINT" and "FROM_DASHBRD_SNRMGTVW".

3. Create a SYNONYM "GL_BALANCES" for SYSADM.GL_BALANCES in the two accounts "FROM_FINANLTC_BIZINT" and "FROM_DASHBRD_SNRMGTVW"

4. Attempt a local connection ERP_FIN and test that these two accounts can only SELECT from this one table alone and can do nothing else.

5. Setup the TNS connect-string for use in the "FINANLTC" and "DASHBRD" database ORACLE_HOMEs. A database link needs to reference the TNS connect-string from the ORACLE_HOME, not from the client desktop or application server. (Alternatively, include the TNS connect-string "as is" in the USING clause).

6. In FINANLTC and DASHBRD grant CREATE DATABASE LINK to BIZINT and SNRMGTVW respectively.

7. In FINANLTC create a *private* Database Link called "TO_ERPFIN_SYSADM" in the BIZINT schema that connects as "FROM_FINANLTC_BIZINT@ERPFIN" (i.e.

create database link TO_ERPFIN_SYSADM connect to FROM_FINANLTC_BIZINT using 'ERPFIN'; 

8. Similarly, create a private Database Link called "TO_ERPFIN_SYSADM" in the SNRMGTVW schema in the DASHBRD database.

9. Revoke CREATE DATABASE LINK from BIZINT and SNRMGTVW.

Note : If you do not have access to the BIZINT and SNRMGTVW passwords, use the method outlined in my previous post "Creating Database Links".

Benefits :

1. Such a setup allows the DBA in ERPFIN to identify every session and know *where* the session is initiated from (he can pinpoint the database and schema using the DBLink)

2. That information can be used for performance tuning / maintenance / investigations

3. The DBA in the BIZINT and DASHBRD databases can identify where a DBLink connects to

.

.

.