Caro Colega Carlos Menezes,
Agora ficou bem claro e deu tudo certo.
Obrigado pela dica
Abraços
Aldenicio Lopes
"Carlos A.M. Menezes" <[EMAIL PROTECTED]> escreveu:
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: [email protected] [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]
---------------------------------
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]
