In this blog post, I will show different ways to drop a private database link of a different schema without connecting to this schema.
Table of Contents
Public vs Private Database Links
Public database links can be created/dropped by every DBA or by users with the CREATE PUBLIC DATABASE LINK privilege. To create/drop a public database just use the keyword PUBLIC.
SQL> DROP PUBLIC DATABASE LINK pub_link;
Database link dropped.
Btw. when you connect to the database with the administrative privilege SYSOPER, you will be connected to the user PUBLIC and the public database links will appear in the USER_DB_LINKS view. But PUBLIC has no permission to drop them.
SQL> SHOW USER
USER is "PUBLIC"
SQL> SELECT db_link FROM user_db_links;
DB_LINK
---------------------------
PUB_LINK.WORLD
SQL> DROP DATABASE LINK pub_link;
DROP DATABASE LINK pub_link
*
ERROR at line 1:
ORA-01031: insufficient privileges
To create private database links the user needs the CREATE DATABASE LINK privilege. There is no ANY version of this privilege and therefore it is not possible to create a database link in a different schema.
SQL> GRANT CREATE DATABASE LINK TO demo_dbl;
SQL> CONNECT demo_dbl
SQL> SHOW USER
USER is "DEMO_DBL"
SQL> CREATE DATABASE LINK priv_dbl;
Database link created.
DBMS_SYS_SQL
One way to drop a database link in a different schema, DBMS_SYS_SQL can be used. This package allows the execution of a SQL statement in a different schema.
DBMS_SYS_SQL is not officially documented in the PL/SQL reference guide.
SQL> DECLARE
v_cursor INTEGER;
-- Define the DROP statement
v_sql VARCHAR2(256 CHAR) := 'DROP DATABASE LINK "PRIV_DBL"';
v_user_id NUMBER;
BEGIN
-- Get user id for owner of the database link
SELECT user_id INTO v_user_id FROM dba_users WHERE username = 'DEMO_DBL';
-- Open the cursor and run the SQL
v_cursor := DBMS_SYS_SQL.OPEN_CURSOR();
DBMS_SYS_SQL.PARSE_AS_USER(
c => v_cursor,
statement => v_sql,
language_flag => DBMS_SQL.NATIVE,
userid => v_user_id
);
END;
/
DBMS_SCHEDULER
As an alternative, a DBMS Scheduler job can be defined for the owner of the database link. The owner itself does not need to have the privilege to create a job.
SQL> DECLARE
-- Add the owner of the database link to the job name
v_job_name VARCHAR2(256 CHAR) := '"DEMO_DBL"."DROP_PRIV_DBL"';
BEGIN
-- Provide the name of the database link in the job_action parameter
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_job_name,
job_type => 'PLSQL_BLOCK',
job_action => q'[BEGIN EXECUTE IMMEDIATE 'DROP DATABASE LINK priv_dbl'; END;]'
);
DBMS_SCHEDULER.RUN_JOB(job_name => v_job_name);
DBMS_SCHEDULER.DROP_JOB(job_name => v_job_name);
END;
/