ORA-01031: Insufficient Privileges – Database Link Creation Error – Non Sys User

My friend encountered an issue yesterday that took some time to figure out, especially during a crucial release. I hope this article helps you too.

ORA-01031: insufficient privileges while creating a DB Link:


        SQL> select * from dba_sys_privs;

        

        USERNAME PRIVILEGE ADM

        ------------------------------ ---------------------------------------- ---

        DEEDDBA ALTER ANY PROCEDURE NO

        DEEDDBA CREATE DATABASE LINK NO

        DEEDDBA CREATE PUBLIC DATABASE LINK NO

        DEEDDBA CREATE SESSION NO

        DEEDDBA DELETE ANY TABLE NO

        DEEDDBA EXECUTE ANY PROCEDURE NO

        DEEDDBA INSERT ANY TABLE NO

        DEEDDBA SELECT ANY TABLE NO

        DEEDDBA UNLIMITED TABLESPACE NO

        DEEDDBA UPDATE ANY TABLE NO

Even if the DBA privilege is granted, it seems impossible to create:

        SQL> conn

        Enter user-name: / as sysdba

        Connected.

        SQL> grant dba to DEEDDBA;

        Grant succeeded.

        SQL> conn DEEDDBA/passwd

        Connected.

        SQL> create database link dbadeed.com connect to PROD321 identified by ***** using 'dbadeed.com';

        create database link dbadeed.com connect to PROD321 identified by *****

        *

        ERROR at line 1:

        ORA-01031: insufficient privileges

What could be the reason? After verifying, we found that:

There is a logon trigger:


            SQL> select trigger_name, trigger_type, triggering_event from dba_triggers where trigger_name='SET_CURRENT_SCHEMA';

            

            TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT

            ------------- ---------------- ----------------

            T_LOGON AFTER EVENT SET_CURRENT_SCHEMA

Logon trigger is set to automatically change the user to TEST:

            EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = DBADEEDS_ADMIN';

            .....

This means that after DEEDDBA logs on to the database, the session will be set as TEST. The reason for ORA-01031 is that the user DEEDDBA cannot create objects in TEST since TEST does not have DBA privileges.

To resolve this, you have to set the current_schema manually:

        SQL> conn DEEDDBA/passwd

        Connected.

        SQL> alter session set current_schema=DEEDDBA;

        Session altered.

        SQL> create database link dbadeed.com connect to PROD321 identified by ***** using 'dbadeed.com';

        Database link created.

Conclusion

Encountering an ORA-01031: insufficient privileges error while creating a database link can be a perplexing issue, as my friend experienced during a critical release. In this scenario, even after granting the DBA role, the problem persisted.

This serves as a valuable lesson, emphasizing the importance of understanding database triggers and their impact on user sessions. When facing privilege-related errors, it’s essential to inspect triggers, roles, and user permissions thoroughly to identify and address the root cause effectively.

I hope this article proves beneficial in resolving similar challenges and contributes to a smoother database management experience during critical moments.

DBADEEDS