Oraclue

Oracle internals, debugging and undocumented features

On Database Trigger and current schema issue

I run into this issue few months ago:

Login as sysdba

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 5 15:13:49 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table test as select * from dba_tables;
create table test as select * from dba_tables
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’

or try to compile invalid objects:

SQL>  @?/rdbms/admin/utlrp.sql
SELECT dbms_registry_sys.time_stamp(‘utlrp_bgn’) as timestamp from dual
*
ERROR at line 1:
ORA-00904: “DBMS_REGISTRY_SYS”.”TIME_STAMP”: invalid identifier

——–

PL/SQL procedure successfully completed.

I got a bunch of errors.It seems that SYSDBA  does not have enough privileges to run this code.

SQL> show user
USER is “SYS”

It took me few minutes before I run

SQL> SELECT sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) FROM dual;

SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)
——————————————————————————–
TEST

and found that SYS is not current schema but user TEST.After looking database triggers I have found
after logon on database trigger:

CREATE OR REPLACE TRIGGER TEST.USER_LOGIN
AFTER LOGON
ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘alter session set current_schema=TEST’;
END user_login;

Now let’s drop trigger and recreate it:

SQL> drop trigger test.user_login;

Trigger dropped.

SQL> CREATE OR REPLACE TRIGGER TEST.USER_LOGIN
AFTER LOGON
ON SCHEMA
BEGIN
EXECUTE IMMEDIATE ‘alter session set current_schema=TEST’;
END user_login;
/  2    3    4    5    6    7

Trigger created.

Now I am using ON SCHEMA trigger instead ON DATABASE trigger.

Connecting as sysdba again:

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 5 15:13:49 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test as select * from dba_tables;

Table created.

SQL> @?/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
SQL> SELECT sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) FROM dual;

SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)
——————————————————————————–
SYS

and current schema user is sysdba what should be.So be careful when using ON DATABASE triggers.Also you can use ON DATABASE trigger but with IF statement inside trigger to limit changes only to specific users.

Something like

CREATE OR REPLACE TRIGGER TEST.USER_LOGIN
AFTER LOGON
ON DATABASE
BEGIN
IF (USER IN (‘TEST’))
THEN
EXECUTE IMMEDIATE ‘alter session set current_schema=TEST’;
END IF;
END user_login;
/

Advertisements

3 responses to “On Database Trigger and current schema issue

  1. suresh January 8, 2010 at 7:21 pm

    it nice ..

  2. Neil Johnson January 17, 2010 at 10:42 pm

    Hi,

    I may be missing something here but is the point of the trigger to set the current schema for users other than TEST. The first new version you created would only fire for connections as TEST which would already have current_schema set correctly. Connections as an application user would not pick up the correct schema.

    Therefore I think the second option you proposed is the correct option.

    Interesting stuff.

    Kind regards

  3. dario boring April 27, 2011 at 5:26 pm

    I though that database triggers did not applied to sysdba users, to avoid that dbas got locked out. It seemed a good idea…..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: