Today I could not understand why I was getting ORA-1031 with create database link.
After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil…
DEMO:
SQL> create user u1 identified by xxx;
User created.
SQL> grant create session, create database link to u2 identified by xxx;
Grant succeeded.
SQL> create trigger evil after logon on database begin
2 execute immediate 'alter session set current_schema=u1';end;
3 /
Trigger created.
SQL>
SQL> conn u2/xxx
Connected.
SQL> create database link l;
create database link l
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> show user
USER is "U2"
SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
-----------------------------------------
U1
SQL> alter session set current_schema=u2;
Session altered.
SQL> create database link l;
Database link created.
The errror seems perfectly reasonable in these circumstances. Why would you ever want a logon triger such as that?
Such trigger seams perfectly reasonable when you have a bunch of reports with “burned in” data source information and you want to quickly switch repositories for all reports without having to edit them.
You could also have one “entry schema name” and then the trigger would set current_schema in regards to different client IP addresses.
Those are just two examples but advanced usage gives you plenty scenarios.
@John
If you do not prefix table names (SELECT * FROM EMP) and you are not in SCOTT schema, you can do alter session set current_schema=scott to always reference tables of Scott without having to be Scott. It is a bit more economical than creating synonyms 🙂 A user could well decide to always do this (after logon on john.schema), or you could do it db wide.