Create database link and logon trigger

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.

Published by

Laurent Schneider

Oracle Certified Master

3 thoughts on “Create database link and logon trigger”

  1. 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.

  2. @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.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>