Oracle触发器限制用户登录_限制指定用户连接数据库

某些时候，需要在同一个内网环境下，限制某些PC机器不能直接连接生产的数据库，首先想到了通过listener来搞定，修改sqlnet.ora文件，然后重新reload一次listener，但是客户有所担心，万一reload的时候，监听不能连接的问题，并且监听只能限制IP，不能对用户名，机器名等信息进行限制，于是想到了通过触发器，但是在测试的时候，有dba权限的用户无效。下面是整个测试过程。

测试环境：

OS:RHEL 5.6
DB:11.2.0.4
1，创建触发器

oracleplus>create or replace trigger chk_ip
      after logon on database
    declare
      ipaddr VARCHAR2(30);
    begin
      select sys_context('userenv', 'ip_address') into ipaddr from dual;
      if ipaddr ='192.168.1.90' then
        raise_application_error('-20001', 'you can not logon by scott');
      end if;
   end chk_ip;
   /
   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.1.90

可见这个时候，对具有dba权限用户无效。,这里已经成功登陆了。说明具有dba权限的用户无效。

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

oracleplus>drop trigger chk_ip;

Trigger dropped.

oracleplus>create or replace trigger chk_ip
  2    after logon on scott.schema   --这里改成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.1.90' then
  8      raise_application_error('-20001', 'you can not logon by scott');
  9    end if;
10  end chk_ip;
11  /

Trigger created.

注意：这里改成scott.schema即可触发限制整个用户

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

上面方案是用白名单通行的方式进行设置，判读如果是非合法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]