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;
/