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 theadminister 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 adocumentatin bug to get this made much more clear in the docs
hi tomsince dba is a role and role is a set of system privileges, may I ask which is the privilege thatmakes 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 thecurrent 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 UNLIMITEDTABLESPACE -- 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, ipaddress, 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-2008PURPOSE-------This bulletin explains which effect the system privilege ADMINISTER DATABASETRIGGER has on database triggers when errors are raised.Similarly, ALTER ANY TRIGGER system privilege causes schema logon triggers to beskipped 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 restrictiveconditions are not met, an application error with a message is raised thatcauses the logon to be denied.create or replace trigger on_logonafter logon on databasebeginif USER='TEST' thenraise_application_error(-20002,'LOGON ERROR',true);end if;end;/If user TEST connects, he is rejected:SQL> connect test/testERROR:ORA-00604: error occurred at recursive SQL level 1ORA-20002: LOGON ERRORORA-06512: at line 21Warning: You are no longer connected to ORACLE.However, we need to keep at least one user who can still connect when there isa problem : a fallback mechanism must exist where an administrative user isexempt from such errors of a prohibited connection.Any user granted the ADMINISTER DATABASE TRIGGER system privilege can stillconnect : 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 sysdbaConnected.SQL> grant ADMINISTER DATABASE TRIGGER to TEST;Grant succeeded.SQL> connect test/testConnected.In alert.log :Fri Mar 5 12:17:08 2004Errors in file /ots2/app/oracle/admin/v920/udump/v920_ora_7682.trc:ORA-00604: error occurred at recursive SQL level 1ORA-20002: LOGON ERRORORA-06512: at line 21In trace file :*** SESSION ID:(15.76) 2004-03-05 12:17:08.750Skipped error 604 during the execution of SYS.ON_LOGON*** 2004-03-05 12:17:08.768ksedmp: internal or fatal errorORA-00604: error occurred at recursive SQL level 1ORA-20002: LOGON ERRORORA-06512: at line 21The ADMINISTER DATABASE TRIGGER is by default granted to the following users androles (are not listed the options' schemas) :SQL> select grantee from dba_sys_privs2 where privilege='ADMINISTER DATABASE TRIGGER';GRANTEE------------------------------DBA --> roleSYS --> userIMP_FULL_DATABASE --> roleEXFSYS --> userALTER ANY TIGGER Privilege Behavior with Schema Logon Trigger-------------------------------------------------------------Similarly, if the logon trigger is on SCHEMA and the current user is not theowner, ALTER ANY TRIGGER privilege is required to be able to connect.connect system/managercreate or replace trigger on_logonafter logon on TEST.schemabeginraise_application_error(-20002,'LOGON ERROR',true);end;/SQL> connect test/testERROR:ORA-00604: error occurred at recursive SQL level 1ORA-20002: LOGON ERRORORA-06512: at line 2Warning: You are no longer connected to ORACLE.SQL> conn system/managerConnected.SQL> grant alter any trigger to test;Grant succeeded.SQL> conn test/testConnected.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 theaccess to SYS objects, this means that if a schema trigger is owned by user SYS, even if theuser has the ALTER ANY TRIGGER privilege, the logon would still fail as this privilegeis 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 PeriodsNote 116636.1 ORA-4098 or ORA-4045 logging on to database having AFTER LOGONevent 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 triggeris 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-validationorORA-04045: errors during recompilation/revalidation of <OWNER>.<TRIGGER_NAME>Solution Description:=====================These errors are seen if the trigger becomes invalid. Disable or recompilethe trigger to make it a VALID status.Example:--------SQL> connect internalorSQL> connect <user> AS SYSDBA-- You MUST connect as internal/SYSDBA to avoid executing the trigger-- try to recompile the trigger firstSQL> ALTER TRIGGER <schema>.<trigger_name> COMPILE ;-- If recompilation fails or if you are still not able to connect, disable-- the triggerSQL> ALTER TRIGGER <schema>.<trigger_name> DISABLE ;Explanation:============Care must be taken with AFTER LOGON triggers, because if they become invalid orfail to fire due to some other error, such as running out of space in a loggingtable, it prevents the users (even DBA users) from logging in. The only way toresolve this error would be to connect with SYSDBA privileges and disable thistrigger. You can then investigate this trigger and make the necessarycorrections.