Colega, Veja se o DOC abaixo resolve seu problema:
Subject: ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors <https://metalink.oracle.com/help/usaeng/Search/search.html#file> Doc ID: Note:265012.1 Type: BULLETIN Last Revision Date: 25-OCT-2005 Status: PUBLISHED 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 are 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 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. 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 scott; Grant succeeded. SQL> conn test/test Connected. RELATED DOCUMENTS ----------------- <https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=120712.1&blackframe=1> Note 120712.1 Database or Logon Event Trigger becomes Invalid: Who can Connect? <https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=220491.1&blackframe=1> Note 220491.1 How to Prevent Users From Log Into a Database Within Defined Periods <https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=116636.1&blackframe=1> Note 116636.1 ORA-4098 or ORA-4045 logging on to database having AFTER LOGON event trigger Abraços, Carlos Alfredo M. de Menezes -----Mensagem original----- De: oracle_br@yahoogrupos.com.br [mailto:[EMAIL PROTECTED] Em nome de Aldenicio Lopes Enviada em: quinta-feira, 7 de dezembro de 2006 11:43 Para: oracle 2 Oracle 2 Cc: oracle 1 Oracle1 Assunto: [oracle_br] Trigger de Logon Boa tarde amigos, A algum tempo atrás postei uma pergunta sobre como impedir um conexão no banco, após satisfazer algumas condições através de uma trigger de logon. Um colega da lista me indicou que colocasse o seguinte: " if <condição> then raise_application_error(-20001, 'Usuario sem Permissao de Acesso!!!'); end if; " Acontece o seguinte, quando faço uma conexão com um usuário que satisfaz a condição da trigger, ele conecta normalmente sem mostrar a mensagem do raise_application. Verifiquei que a mensagem é logada somente no Alert.log, ou seja: Errors in file c:\oracle\admin\test\udump\test_ora_6020.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: Usuario sem Permissao de Acesso!!! ORA-06512: at line 30 Na realidade eu gostaria que essa mensagem aparecesse para o usuário e impedisse qualquer tipo de acesso. Seria possível? Oracle 9.2.0.6 Windows 2003 Server Desde já agradeço Aldenicio Lopes --------------------------------- Yahoo! Search Música para ver e ouvir: You're Beautiful, do James Blunt [As partes desta mensagem que não continham texto foram removidas] [As partes desta mensagem que não continham texto foram removidas]