Search My Oracle Blog

Custom Search

23 May, 2010

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

.
.
.


No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016