Oracle使用触发器限制IP地址连接数据库

天凯科技

于 2019-10-25 12:20:59 发布

阅读量716

点赞数

分类专栏： ORACLE数据库运维文章标签： Oracle使用触发器限制登录

版权归属：天凯科技

本文链接：https://blog.csdn.net/dbs_service/article/details/102738996

版权

ORACLE数据库运维专栏收录该内容

28 篇文章

订阅专栏

本文介绍如何通过触发器限制特定IP地址登录Oracle数据库的方法，包括针对普通用户和具有DBA权限用户的解决方案，并提供了一种白名单通行的限制方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

很多时候，需要在同一个机房中或内网下，限制测试服务器不能直接连接生产的数据库，首先想到了通过listener来搞定，修改sqlnet.ora文件，然后重新reload一次listener，但是客户有所担心，万一reload的时候，监听不能连接的问题，于是想到通过防火墙，但是客户告诉，通过防火墙，不可能，再想到了通过触发器，但是在测试的时候，有dba权限的用户无效。下面是整个测试解决过程。
测试环境：
OS:RHEL 5.6
DB:10.2.0.4.10
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]