How To Prevent Database Links to Same Database in Oracle

Question

My Main objective is to write a PLSQL Procedure to create database links when host, port, service, username and password is given. There I need to block creation of database links to same database. How can I block the creation of database links to the same database, So that I can inform the user that database links to the same database is not allowed.

Answer 1

In your procedure that is creating the database links, before it actually creates the database link, you can have it check one of these views.

• DBA_DB_LINKS - All DB links defined in the database
• ALL_DB_LINKS - All DB links the current user has access to
• USER_DB_LINKS - All DB links owned by current user

If the link already exists in one of those views either raise an error or just don't create it a second time.

Answer 2

Make sure that the host and service name you're given aren't the same as the database you're running on. You can get this information from the SYS_CONTEXT function, in the 'USERENV' namespace:

SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS SERVICE_NAME,
       SYS_CONTEXT('USERENV', 'SERVER_HOST') AS SERVER_HOST
  FROM DUAL

For example, your procedure might look like this:

CREATE OR REPLACE PROCEDURE CREATE_DB_LINK(pinHost     IN VARCHAR2,
                                           pinPort     IN NUMBER,
                                           pinService  IN VARCHAR2,
                                           pinUsername IN VARCHAR2,
                                           pinPassword IN VARCHAR2)
AS
  strHost      VARCHAR2(100);
  strService   VARCHAR2(100);
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME'),
         SYS_CONTEXT('USERENV', 'SERVER_HOST')
    INTO strService,
         strHost
    FROM DUAL;

  IF pinHost = strHost AND
     pinService = strService
  THEN
    RAISE_APPLICATION_ERROR(-20001, 'Cannot create link to current DB');
  END IF;

  -- rest of code
END CREATE_DB_LINK;

Answer 3

As it is a procedure you wrote, include that check into it.

For example, store list of databases that should be excluded into a table (so that you wouldn't have to hardcode their names into the procedure). Then check whether database - whose parameters you got as parameters - is blacklisted. If so, inform the user that database link can't be created.

How? Either via procedure's OUT parameter, or RAISE_APPLICATION_ERROR.

Answer 4

There is a fatal flaw in your approach: anyone running this procedure with the default invoker's rights would have to have the CREATE DATABASE LINK privilege granted to them for the procedure to work. This would allow them to create the link directly and bypass your procedure entirely.

Alternatively, if the procedure is created to run with owner's rights instead of the default invoker's rights, it would either be creating a database link in another schema where the user wouldn't be able to access it anyway, or you would have to be creating public database links - which shouldn't be used in general and should only, ever be created by a DBA. Period.

In short, the entire concept is flawed and cannot be implemented as you've described. Users will either ignore your procedure, and/or it will create a security nightmare. In general I would agree with the earlier comments by @EdStevens and @MarmiteBomber that database links should only, ever, be created by a DBA. I'll even go one further and suggest that the links should be accessed by use of local views and synonyms in an API schema, and never directly accessed by users.

Access to a database link opens up a world of information about the remote database that most users shouldn't have access to (like ALL_USERS to get a list of every valid user account on the system), and should only ever be a part of a formal, version controlled, data schema. Use of an API to pull data through the link prevents users from making direct queries on the remote data dictionary and enhances the security of both systems.