ORA-01031 insufficient privileges while creating database link

My friend has an issue yesterday which took sometime to figure out in important time during a release. Hooe this may help you too.

ORA-01031: insufficient privileges , while creating DB Link

SQL> select * from dba_sys_privs;

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST321 ALTER ANY PROCEDURE NO
TEST321 CREATE DATABASE LINK NO
TEST321 CREATE PUBLIC DATABASE LINK NO
TEST321 CREATE SESSION NO
TEST321 DELETE ANY TABLE NO
TEST321 EXECUTE ANY PROCEDURE NO
TEST321 INSERT ANY TABLE NO
TEST321 SELECT ANY TABLE NO
TEST321 UNLIMITED TABLESPACE NO
TEST321 UPDATE ANY TABLE NO

Even if I grant DBA priv I cant seem to create:

SQL> conn
Enter user-name: / as sysdba
Connected.
SQL> grant dba to TEST321;

Grant succeeded.

SQL> conn TEST321/bet123
Connected.

SQL> create database link test.world connect to PROD321 identified by ***** using 'test.world';
create database link TEST.world connect to PROD321 identified by *****
*
ERROR at line 1:
ORA-01031: insufficient privileges

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

1) There is a logon trigger

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

TRIGGER_NAME  TRIGGER_TYPE     TRIGGERING_EVENT
------------- ---------------- ----------------
T_LOGON       AFTER EVENT      LOGON

SQL>

2) Logon trigger written as, sets any user that logon this database will automatically set to another user TEST

Here is the piece of Trigger code

select text from dba_source where name='T_LOGON';

TRIGGER T_Logon

AFTER LOGON ON DATABASE
--
DECLARE
--
CURSOR c1 IS
.....

EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = TEST';

.....

This means, after immediately TEST321 logs on to the database , the session will be set as TEST

So , Well now the reason, the user TEST321 cannot create objects in TEST and thats why the reason ORA-01031, but question why still despite of granting DBA Role is failing,
again TEST does not have DBA Privs 🙂 we are granting to TEST321.

Now to resolve this, we have to again to set current_schema of our own

SQL> conn TEST321/bet123
Connected.

SQL> alter session set current_schema=TEST321;
Session altered.

SQL> create database link test.world connect to PROD321 identified by ***** using 'test.world';
Database link created.

Hope this helps.