Supprimer un DB Link appartenant à un autre user

Si vous êtes DBA de prod  il est fort probable que pour supprimer des objets d'un schéma applicatif d'une base de données vous utilisiez un compte nominatif avec le role DBA, voir même  le compte SYS. La plupart du temps les mots de passe des schémas applicatifs ne sont pas connus des DBAs.

Presque tous les objets d'un schéma peuvent être supprimés avec un autre compte ayant le role DBA en préfixant l'objet par le nom du USER propriétaire. J'ai dit "presque" car ce n'est pas vrai pour les DB Links et les jobs. En effet, il est impossible même pour le user SYS de supprimer un DB Link privé ou un job appartenant à un autre USER.

Voici un exemple concret d'un DB Link que j'ai eu à supprimer ce matin:

SQL>select OWNER,DB_LINK from dba_db_links where DB_LINK='DBL_TEST';

OWNER DB_LINK
------------------------------ ------------------------------
ASDRVMAT DBL_TEST

Le DB Link DBL_TEST appartient au schema ASDRVMAT.
Si, en me connectant en sysdba, j'essaye de le supprimer en préfixant le nom du db link par le nom du schéma propriétaire j'obtiens l'erreur suivante:

 DROP DATABASE LINK ASDRVMAT.DBL_TEST;

ERREUR à la ligne 1 :
ORA-02024: lien de base de données introuvable

Oracle ne trouve pas le db link car il interprète "ASDRVMAT.DBL_TEST" comme étant le nom du db link.

En modifiant le schéma par défaut au niveau de la session j'obtiens un autre message d'erreur indiquant que je ne suis pas autorisé à supprimer le db link en question:

alter session set current_schema=ASDRVMAT;
DROP DATABASE LINK DBL_TEST;

ERREUR à la ligne 1 :
ORA-01031: privilèges insuffisants

Il n'est donc pas possible de supprimer un db link privé appartenant à un autre user sauf bien sûr en se connectant avec le user propriétaire directement.
D'ailleurs la doc Oracle est claire à ce sujet:

Restriction on Dropping Database Links You cannot drop a database link in another user's schema, and you cannot qualify dblink with the name of a schema, because periods are permitted in names of database links

La doc Oracle ne donne pas de solution de contournement mais comme souvent dans Oracle il existe un moyen officieux d'arriver à ses fins.
Ici la solution va consister à utiliser le package  non documenté DBMS_SYS_SQL.
En effet, dans ce package on a la fonction PARSE_AS_USER qui permet d’exécuter une commande SQL en tant que n'importe quel autre user.
Voici le script que j'utilise et que j'ai appelé drop_db_link.sql dans ma toolbox:

declare
    sql_text   varchar2(1000);
    cur     number;
    user_id     number;
    res      number;

begin

    select
            u.user_id into user_id
   from    dba_users u
   where   u.username = '&schema_name';
   
     sql_text := 'drop database link '||'&db_link';
     cur := SYS.DBMS_SYS_SQL.open_cursor;
     
     SYS.DBMS_SYS_SQL.parse_as_user(
           c => cur,
           statement => sql_text,
           language_flag => DBMS_SQL.native,
           userID => user_id  );
           
     res := SYS.DBMS_SYS_SQL.execute(cur);

     SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/

Ce script prend en paramètre le nom du schéma propriétaire et le nom du db link à dropper.
Grace au nom du schéma propriétaire il récupère le USER_ID qui sera utilisé comme un des paramètres de la fonction  SYS.DBMS_SYS_SQL.parse_as_user.
On peut ainsi exécuter la commande DROP DATABASE LINK comme si on était connecté avec le compte propriétaire:

SQL>declare
        sql_text   varchar2(1000);
        cur     number;
        user_id     number;
        res      number;
        --dblk varchar2(30);
begin
        --dblk :=
        select
                        u.user_id into user_id
   from    dba_users u
   where   u.username = '&schema_name';

         sql_text := 'drop database link '||'&db_link';
         cur := SYS.DBMS_SYS_SQL.open_cursor;

         SYS.DBMS_SYS_SQL.parse_as_user(
                   c => cur,
                   statement => sql_text,
                   language_flag => DBMS_SQL.native,
                   userID => user_id  );

         res := SYS.DBMS_SYS_SQL.execute(cur);

         SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/  
Entrez une valeur pour schema_name : ASDRVMAT
ancien  12 :    where   u.username = '&schema_name';
nouveau  12 :    where   u.username = 'ASDRVMAT';
Entrez une valeur pour db_link : DBL_TEST
ancien  14 :     sql_text := 'drop database link '||'&db_link';
nouveau  14 :    sql_text := 'drop database link '||'DBL_TEST';

Procédure PL/SQL terminée avec succès.

Après avoir exécuté le script on peut vérifier que le db link a bien été supprimé:

SQL>select OWNER,DB_LINK from dba_db_links where DB_LINK='DBL_TEST';
 
aucune ligne sélectionnée