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 GENEVA_ADMIN.SCHEMA
BEGIN
  if (sys_context('USERENV', 'SESSION_USER') = 'GENEVA_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 GENEVA_ADMIN

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



When created in AJAY db user, it was unable to restrict the osuser oracle9i from logging into GENEVA_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, GENEVA_ADMIN & AJAY

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

A different scenario have been attempted where AJAY schema has been restricted by creating trigger inside GENEVA_ADMIN

create or replace trigger restrictuser after LOGON ON AJAY.SCHEMA
BEGIN
if (sys_context('USERENV', 'SESSION_USER') = 'AJAY' 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='AJAY' and privilege like '%ANY%TRIGGER%';
revoke ALTER ANY TRIGGER from AJAY;



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

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

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

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

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

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
GENEVA_ADMIN                   DBA                            NO  YES
GENEVA_ADMIN                   IMP_FULL_DATABASE              NO  YES

SQL> SQL> revoke DBA,IMP_FULL_DATABASE from GENEVA_ADMIN;
Revoke succeeded.
SQL> conn ajay
Enter password:
Connected.
SQL> create or replace trigger restrictuser after LOGON ON GENEVA_ADMIN.SCHEMA
  2  BEGIN
  3  if (sys_context('USERENV', 'SESSION_USER') = 'GENEVA_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@ghcr-sit-lvsb#sqlplus geneva_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 (GENEVA_ADMIN here) has resolved the issue.

1 comment:

  1. excellent doc ...we can restrict unwanted user's operations in schema

    ReplyDelete

Popular Posts