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.
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".
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