[Oracle] 如何使用觸發器實現IP限制用戶登錄

在Oracle裡不像MySQL那樣方便可以直接在用戶上進行IP限制Oracle要實現用戶級別的IP限制可以使用觸發器來迂回實現以下就是示例需要的朋友可以參考下  

　　下面是一個觸發器的例子

復制代碼 代碼如下:
create or replace trigger logon_ip_control
after logon on database
declare
  ip STRING();
  user STRING();
begin
SELECT SYS_CONTEXT(USERENVSESSION_USER) into user from dual;
SELECT SYS_CONTEXT(USERENVIP_ADDRESS) into ip from dual;
if user=EPAY_USER
  THEN
      IF ip not in () 
      THEN raise_application_error(User ||user|| is not allowed to connect from ||ip);
      END IF;
END IF;
end;
/

　　
該觸發器對用戶EPAY_USER進行了IP限制（只允許如果需要設置IP段用%或?代替即可如%‘）
下面看幾個例子測試一下
）從非允許IP地址登陸 （）連接失敗

復制代碼 代碼如下:
[oracle@lxdb ~]$ sqlplus epay_user@pri
SQL*Plus: Release Production on Wed Jul ::
Copyright (c) Oracle  All rights reserved
Enter password:
ERROR:
ORA: error occurred at recursive SQL level
ORA: User EPAY_USER is not allowed to connect from
ORA: at line

　　
）從允許IP地址登陸（）連接成功

復制代碼 代碼如下:
[oracle@lxdb ~]$ sqlplus epay_user
SQL*Plus: Release Production on Wed Jul ::
Copyright (c) Oracle  All rights reserved
Enter password:
Connected to:
Oracle Database g Enterprise Edition Release bit Production
With the Partitioning OLAP Data Mining and Real Application Testing options

　　
）從本地登陸(）不受IP限制影響連接成功

復制代碼 代碼如下:

　　
[oracle@lxdb ~]$ sqlplus epay_user
SQL*Plus: Release Production on Wed Jul ::
Copyright (c) Oracle  All rights reserved
Enter password:
Connected to:
Oracle Database g Enterprise Edition Release bit Production
With the Partitioning OLAP Data Mining and Real Application Testing options

From:http://tw.wingwit.com/Article/program/Oracle/201311/19023.html