认真就输

          今天应客户要求，让测试主机不能连接生产的数据库，首先想到了通过listener来搞定，修改sqlnet.ora文件，然后重新reload一次listener，但是客户有所担心，万一reload的时候，监听不能连接的问题，于是想到通过防火墙，但是客户告诉，通过防火墙，不可能，再想到了通过触发器，但是在测试的时候，有dba权限的用户无效。下面是整个测试过程，测试环境：OS:RHEL 4.8  DB:10.2.0.4.10

1，测试触发器

www.htz.pw >create or replace trigger chk_ip
  2    after logon on database
  3  declare
  4    ipaddr VARCHAR2(30);
  5  begin
  6    select sys_context('userenv', 'ip_address') into ipaddr from dual;
  7    if ipaddr ='192.168.111.44' then
  8      raise_application_error('-20001', 'you can not logon by scott');
  9    end if;
 10  end chk_ip;
 11  /

Trigger created.

2，远程用户登陆成功

[oracle@migrate ~]$ sqlplus scott/oracle@orcl10g

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 20 20:07:04 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: you can not logon by scott
ORA-06512: at line 6


Enter user-name: 

已经成功拒绝了scott用户登陆

3，授予dba权限给scott

www.htz.pw >grant dba to scott;

Grant succeeded.
[oracle@migrate ~]$ sqlplus scott/oracle@orcl10g

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 20 20:10:36 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select sys_context('userenv', 'ip_address')  from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
192.168.111.44
这里已经成功登陆了。说明具有dba权限的用户无效。

4，具有dba权限的用户我们可以使用的方式

www.htz.pw >drop trigger chk_ip;

Trigger dropped.

www.htz.pw >create or replace trigger chk_ip
  2    after logon on scott.schema
  3  declare
  4    ipaddr VARCHAR2(30);
  5  begin
  6    select sys_context('userenv', 'ip_address') into ipaddr from dual;
  7    if ipaddr ='192.168.111.44' then
  8      raise_application_error('-20001', 'you can not logon by scott');
  9    end if;
 10  end chk_ip;
 11  /

Trigger created.

[oracle@migrate ~]$ sqlplus scott/oracle@orcl10g

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 20 20:21:45 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: you can not logon by scott
ORA-06512: at line 6
这里成功拒绝scott用户的登陆。但是如果有多少用户就比较麻烦

5，下面是同事用户的另外一种方式

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;
/

下面是官方给的说明

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.
Connecting as DBA Does not Fire RAISE_APPLICATION_ERROR in a AFTER LOGON ON DATABASE TRIGGER [ID 226058.1]