Oracle LOGON trigger not working

Issue: Oracle Logon trigger based on schema or database level working only from sys & not working from any other schema

Trigger Code:

create or replace trigger restrictuser

  after LOGON ON ADMIN.SCHEMA

BEGIN

  if (sys_context('USERENV', 'SESSION_USER') = 'ADMIN' and

     sys_context('USERENV', 'OS_USER') = 'oracle9i') then

    RAISE_APPLICATION_ERROR(-20001, 'Sorry, you are not allowed here!');

  END if;

END;

Requirement:

Trigger should be created in non-sys schema to restrict OS_USER oracle9i from logging into DB user ADMIN

Issue faced:

Trigger found to be working perfectly & restricting oracle9i user when created in sys db user as below

When created in TEST db user, it was unable to restrict the osuser oracle9i from logging into ADMIN db user as below

Analysis/Solution:

Analysis has begun by comparing role (DBA_ROLE_PRIVS) & sys privileges (DBA_SYS_PRIVS)  between all three DB users i.e. SYS, ADMIN & TEST

In multiple attempts compilation from sys was always giving proper outcome but from TEST schema it wasn’t.

A different scenario have been attempted where TEST schema has been restricted by creating trigger inside ADMIN

create or replace trigger restrictuser after LOGON ON TEST.SCHEMA

BEGIN

if (sys_context('USERENV', 'SESSION_USER') = 'TEST' and sys_context('USERENV','OS_USER') = 'oracle9i') then

RAISE_APPLICATION_ERROR(-20001, 'Sorry, you are not allowed here!');

END if;

END;

select * from dba_sys_privs where grantee='TEST' and privilege like '%ANY%TRIGGER%';

revoke ALTER ANY TRIGGER from TEST;

The ALTER ANY TRIGGER system privilege found to be causing issue while restriction, revoking it has restricted oracle9i osuser from logging into TEST schema.

Noting ALTER ANY TRIGGER privilege behaviour, have resumed to actual requirement wherein ADMIN to be restricted by a trigger owned by TEST from osuser oracle9i.

Even after revoking ALTER ANY TRIGGER privilege from ADMIN trigger was not restricting oracle9i osuser from logging into ADMIN

After some more investigation, roles DBA & IMP_FULL_DATABASE   found to be causing issue, revoking them from ADMIN has resolved the issue

SQL> select * from dba_role_privs where grantee='ADMIN' and GRANTED_ROLE in ('DBA','IMP_FULL_DATABASE');

GRANTEE                        GRANTED_ROLE                   ADM DEF

------------------------------ ------------------------------ --- ---

ADMIN                   DBA                            NO  YES
ADMIN                   IMP_FULL_DATABASE              NO  YES

SQL> SQL> revoke DBA,IMP_FULL_DATABASE from ADMIN;

Revoke succeeded.

SQL> conn test

Enter password:

Connected.

SQL> create or replace trigger restrictuser after LOGON ON ADMIN.SCHEMA

  2  BEGIN

  3  if (sys_context('USERENV', 'SESSION_USER') = 'ADMIN' and sys_context('USERENV','OS_USER') = 'oracle9i') then

  4  RAISE_APPLICATION_ERROR(-20001, 'Sorry, you are not allowed here!');

  5  END if;

  6  END;

  7  /

Trigger created.

SQL> !

oracle9i@sit-db#sqlplus admin

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Feb 4 19:15:33 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter password:

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Sorry, you are not allowed here!

ORA-06512: at line 3

Conclusion:

Sys Privilege ALTER ANY TRIGGER sys privs & role DBA, IMP_FULL_DATABASE were suppressing the restriction set by the trigger. Revoking the Sys & Role privilege from db user (ADMIN here) has resolved the issue.