administer database trigger privilege – knowledge article for the new oracle dba





you are a new oracle dba and you would like to know why a DBA role can bypass  logon triggers that fail where as  normal sessions are prevented  from logging into the database.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2235611

 

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3236035522926

 

Updated December 1, 2005 - a couple of people have pointed out it just takes the 
administer Database Trigger privilege to be immune from a logon trigger failing.
 
Plus, it seems that the trigger owner -- even if it's a regular "Create Session, 
Resource" guy -- never sees the exception in his own "after logon on schema" trigger 
(unless there's a magic parameter lurking that I have yet to stumble on).
 
Bryn Llewellyn who "owns" plsql says in addition to the above that he is filing a 
documentatin bug to get this made much more clear in the docs

 

hi tom

since dba is a role and role is a set of system privileges, may I ask which is the privilege that
makes DBA Role bypass logon trigger errors?

 

Followup   March 30, 2002 – 4pm Central time zone:

it is not a privilege, it is "burned into" the server.  It is the fact that the name of a role the
current user has is "DBA" that bypasses the ability of the trigger to prevent the login.

Just like when you grant DBA or RESOURCE to a user, they automagically get granted UNLIMITED
TABLESPACE -- it's just because of the name of the role.

 

 

username and terminal are neither declared nor set to a value.

see the example above for how to get "terminal".


then read up on sys_context to see what else you can get there (session user, current schema, ip
address, etc).

USER is something else you might be interested in.

 

 

 

 

Subject:

ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors
 

Doc ID:

265012.1

Type:

BULLETIN
 

Modified Date:

08-JUN-2009

Status:

PUBLISHED

Checked for relevance on 10-Dec-2008

PURPOSE
-------
This bulletin explains which effect the system privilege ADMINISTER DATABASE
TRIGGER has on database triggers when errors are raised.
Similarly, ALTER ANY TRIGGER system privilege causes schema logon triggers to be
skipped on errors.

SCOPE & APPLICATION
-------------------
For DBAs who set up database access control using logon triggers.


ADMINISTER DATABASE TRIGGER Privilege Behavior with Database Logon Trigger
--------------------------------------------------------------------------
Logon triggers can be used to mediate database access: when the restrictive
conditions are not met, an application error with a message is raised that
causes the logon to be denied.

    create or replace trigger on_logon
        after logon on database
        begin
        if USER='TEST' then
           raise_application_error(-20002,'LOGON ERROR',true);
        end if;
       end;
    /

If user TEST connects, he is rejected:

    SQL> connect test/test
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20002: LOGON ERROR
    ORA-06512: at line 21

    Warning: You are no longer connected to ORACLE.

However, we need to keep at least one user who can still connect when there is
a problem : a fallback mechanism must exist where an administrative user is
exempt from such errors of a prohibited connection.

Any user granted the ADMINISTER DATABASE TRIGGER system privilege can still
connect : instead of getting the error causing the session to be terminated,
the error is recorded in the alert.log and a trace file in user_dump_dest.

    SQL> connect / as sysdba
    Connected.
    SQL> grant ADMINISTER DATABASE TRIGGER to TEST;
    Grant succeeded.

    SQL> connect test/test
    Connected.

In alert.log :

    Fri Mar  5 12:17:08 2004
    Errors in file /ots2/app/oracle/admin/v920/udump/v920_ora_7682.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20002: LOGON ERROR
    ORA-06512: at line 21

In trace file :

    *** SESSION ID:(15.76) 2004-03-05 12:17:08.750
    Skipped error 604 during the execution of SYS.ON_LOGON
    *** 2004-03-05 12:17:08.768
    ksedmp: internal or fatal error
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20002: LOGON ERROR
    ORA-06512: at line 21

The ADMINISTER DATABASE TRIGGER is by default granted to the following users and
roles (are not listed the options' schemas) :

    SQL> select grantee from dba_sys_privs
      2  where privilege='ADMINISTER DATABASE TRIGGER';

    GRANTEE
    ------------------------------
    DBA                            --> role
    SYS                            --> user
    IMP_FULL_DATABASE              --> role
    EXFSYS                         --> user


ALTER ANY TIGGER Privilege Behavior with Schema Logon Trigger
-------------------------------------------------------------
Similarly, if the logon trigger is on SCHEMA and the current user is not the
owner, ALTER ANY TRIGGER privilege is required to be able to connect.

    connect system/manager

    create or replace trigger on_logon
    after logon on TEST.schema
    begin
       raise_application_error(-20002,'LOGON ERROR',true);
    end;
    /

    SQL> connect test/test
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20002: LOGON ERROR
    ORA-06512: at line 2

    Warning: You are no longer connected to ORACLE.

    SQL> conn system/manager
    Connected.
    SQL> grant alter any trigger to test;
    Grant succeeded.

    SQL> conn test/test
    Connected.

Triggers owned by SYS and O7_DICTIONARY_ACCESSIBILITY
-----------------------------------------------------

Note that in the above example, the schema trigger is actually owned by user SYSTEM,
this means when user TEST has the ALTER ANY TRIGGER privilege, the trigger can be altered.
As of version 9i, it is O7_DICTIONARY_ACCESSIBILITY parameter that governs/protects the
access to SYS objects, this means that if a schema trigger is owned by user SYS, even if the
user has the ALTER ANY TRIGGER privilege, the logon would still fail as this privilege
is not sufficient to alter SYS objects unless O7_DICTIONARY_ACCESSIBILITY = true.
For security reasons, Oracle recommends that you use this setting only with great caution.


RELATED DOCUMENTS
-----------------
Note 120712.1 Database or Logon Event Trigger becomes Invalid: Who can Connect?
Note 220491.1 How to Prevent Users From Log Into a Database Within Defined Periods
Note 116636.1 ORA-4098 or ORA-4045 logging on to database having AFTER LOGON
                event trigger


.

Subject:

ORA-4098 or ORA-4045 logging on to database having AFTER LOGON event trigger
 

Doc ID:

116636.1

Type:

PROBLEM
 

Modified Date:

21-OCT-2005

Status:

PUBLISHED

Problem Description:
====================

There is an AFTER LOGON event trigger created in the database, and the trigger
is ENABLED. The AFTER LOGON trigger is a new triggering event for Oracle8i,
which fires when a user logs on.

When logging in to the database, you get the following errors:

  ORA-04098: trigger '<TRIGGER_NAME>' is invalid and failed re-validation

or

  ORA-04045: errors during recompilation/revalidation of <OWNER>.<TRIGGER_NAME>


Solution Description:
=====================

These errors are seen if the trigger becomes invalid. Disable or recompile
the trigger to make it a VALID status.

Example:
--------

  SQL> connect internal
  or
  SQL> connect <user> AS SYSDBA
  -- You MUST connect as internal/SYSDBA to avoid executing the trigger


  -- try to recompile the trigger first
  SQL> ALTER TRIGGER <schema>.<trigger_name> COMPILE ;

  -- If recompilation fails or if you are still not able to connect, disable
  -- the trigger

  SQL> ALTER TRIGGER <schema>.<trigger_name> DISABLE ;


Explanation:
============

Care must be taken with AFTER LOGON triggers, because if they become invalid or
fail to fire due to some other error, such as running out of space in a logging
table, it prevents the users (even DBA users) from logging in. The only way to
resolve this error would be to connect with SYSDBA privileges and disable this
trigger. You can then investigate this trigger and make the necessary
corrections.

 

Author: admin