[Oracle] Using triggers to restrict user logon by IP addresses

[Oracle] using a trigger to restrict user logon to Oracle is not as convenient as MySQL, and IP restrictions can be directly imposed on users. Oracle must implement user-level IP restrictions, you can use a trigger to achieve this. The following is an example of a trigger:

[sql] create or replace trigger logon_ip_control  after logon on database  declare    ip STRING(30);    user STRING(30);  begin  SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user from dual;  SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip from dual;  if user='EPAY_USER'     THEN        IF ip not in ('192.168.219.20','192.168.219.22')          THEN raise_application_error(-20001,'User '||user||' is not allowed to connect from '||ip);        END IF;  END IF;  end;  /  

 

This trigger imposes IP restrictions on the user EPAY_USER (only '192. 168.219.20 ', '192. 168.219.22' are allowed. If you need to set an IP segment, use % or? For example, '192. 192.% '). The following is a few examples to test: 1) the connection fails to log on from a non-permitted IP address (192.168.219.21 ).

[sql] [oracle@lxdb2 ~]$ sqlplus epay_user@pri    SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 19:23:48 2013    Copyright (c) 1982, 2011, Oracle.  All rights reserved.    Enter password:   ERROR:  ORA-00604: error occurred at recursive SQL level 1  ORA-20001: User EPAY_USER is not allowed to connect from 192.168.219.21  ORA-06512: at line 10  

 

2) log on from the allowed IP address (192.168.219.22) and the connection is successful.

[sql] [oracle@lxdb1 ~]$ sqlplus epay_user    SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 11:24:25 2013    Copyright (c) 1982, 2011, Oracle.  All rights reserved.    Enter password:     Connected to:  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  With the Partitioning, OLAP, Data Mining and Real Application Testing options 

3) login from the Local Machine (192.168.219.23) is not affected by IP restrictions, and the connection is successful.

[sql] [oracle@lxdb1 ~]$ sqlplus epay_user    SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 11:24:25 2013    Copyright (c) 1982, 2011, Oracle.  All rights reserved.    Enter password:     Connected to:  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  With the Partitioning, OLAP, Data Mining and Real Application Testing options