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.
-
2Database links are typically created by DBAs. If you have such a large number of links that you can't manage it this way, that IMO the *loopback* links *will be not the worst problem*. Also passing *passwords* as a procedure parameters is not considered a prefered *security* option. – Marmite Bomber Feb 08 '21 at 14:29
-
2I would just add my vote to @MarmiteBomber. This "requirement" is an astoundingly bad idea from its very conception. If there is a need for a db link, it should be forwarded to the DBA to be created. This is a procedural issue, not a coding issue. – EdStevens Feb 08 '21 at 15:03
4 Answers
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;

- 48,992
- 9
- 77
- 110
-
What about the *malicious* users passing e.g. the `IP address` instead of the *host name* to bypass the check? – Marmite Bomber Feb 08 '21 at 14:35
-
If OP is concerned about this he/she can check the host parameter to see if it's an IP address and either A) verify it doesn't match the IP address of the db server, or B) just reject the cases where the host is an IP address. – Bob Jarvis - Слава Україні Feb 08 '21 at 15:10
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
.

- 131,892
- 15
- 35
- 57
-
No we cannot keep details like that where somebody has to maintain it. These environments creation is dynamic so not practical to do that. – JEJC_JACALK Feb 08 '21 at 11:50
-
So how do you plan to tell your procedure that database link to certain databases shouldn't be created? – Littlefoot Feb 08 '21 at 12:01
-
So create the table for blacklisted databases. When creating an entry for hash the values and store ONLY the hash value. When your routine is called then again call the hash routine and check the result against the database. If found throw an exception. If not found then continue as appropriate. – Belayer Feb 08 '21 at 12:09
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.

- 5,791
- 1
- 8
- 23
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.

- 6,457
- 2
- 6
- 16