触发器无法限制DBA角色用户登陆

Connecting as DBA Does not Fire RAISE_APPLICATION_ERROR in a AFTER LOGON ON DATABASE TRIGGER [ID 226058.1]

Oracel Database 11.2:

Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2)
Chapter 9 PL/SQL Triggers
  Exception Handling in Triggers

In most cases, if a trigger runs a statement that raises an exception, and the exception is not handled by an exception handler, then the database rolls back the effects of both the trigger and its triggering statement.

In the following cases, the database rolls back only the effects of the trigger, not the effects of the triggering statement (and logs the error in trace files and the alert log):

    The triggering event is either AFTER STARTUP ON DATABASE or BEFORE SHUTDOWN ON DATABASE.

    The triggering event is AFTER LOGON ON DATABASE and the user has the ADMINISTER DATABASE TRIGGER privilege.

    The triggering event is AFTER LOGON ON SCHEMA and the user either owns the schema or has the ALTER ANY TRIGGER privilege.

关于第三个条件，没验证到，没搞懂是啥子意思。
使用 AFTER LOGON ON ctais2.schema可以实现该问题，但是如果系统有N多个用户，有点麻烦

通过trigger限制特定IP登陆还是不靠谱，竟然连有DBA角色的用户都限制不住

临时使用，想了个替代办法，不抛出异常，而让其卡死在那，发现后手工杀掉
create or replace trigger chk_ip
  after logon on database
declare
  ipaddr VARCHAR2(30);
  e_integrity exception;
  pragma exception_init(e_integrity,-913);
begin
  select sys_context('userenv', 'ip_address') into ipaddr from dual;
  if ipaddr not in ('83.16.16.201') then
    SYS.DBMS_SYSTEM.KSDWRT(2,TO_CHAR(SYSDATE, 'yyyymmdd hh24:mi:ss') ||' ORA-02000 user: ' || USER||' IP: '||ipaddr);
    dbms_lock.sleep(3600*100);
  end if;
end chk_ip;
/

如果不做那么细的限制，最好的办法，还是通过sqlnet.ora

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8242091/viewspace-764406/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-764406/