oracle触发器不允许修改数据库,【学习笔记】Oracle使用触发器来控制限制IP地址连接数据库...

天萃荷净

开发DBA反映需求，在连接Oracle数据库的Ip地址进行限制，通过触发器限制IP地址登陆数据库，对具有dba权限用户无效。

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

1，测试触发器

oracleplus>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

oracleplus>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权限的用户我们可以使用的方式

oracleplus>drop trigger chk_ip;

Trigger dropped.

oracleplus>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]

本文固定链接: http://www.htz.pw/2013/06/20/%e9%80%9a%e8%bf%87%e8%a7%a6%e5%8f%91%e5%99%a8%e9%99%90%e5%88%b6ip%e5%9c%b0%e5%9d%80%e7%99%bb%e9%99%86%e6%95%b0%e6%8d%ae%e5%ba%93%ef%bc%8c%e5%af%b9%e5%85%b7%e6%9c%89dba%e6%9d%83%e9%99%90%e7%94%a8-2.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle使用触发器来控制限制IP地址连接数据库