Using Oracle triggers to implement IP restrictions user logon

In Oracle, not as convenient as MySQL, can be directly on the User IP restrictions, Oracle to implement user-level IP restrictions, you can use triggers to bypass the implementation, the following is an example of a trigger:

Create or replace trigger Logon_ip_control after  
logon on database
declare
  IP STRING;  
  User STRING (a);  
Begin
SELECT sys_context (' USERENV ', ' Session_user ') into the USER from dual;  
SELECT sys_context (' USERENV ', ' ip_address ') into the IP from dual;  
If user= ' Epay_user ' 
  THEN
      if IP not in (' 192.168.219.20 ', ' 192.168.219.22 ')    
      THEN ( -20001, ' User ' | | user| | ' isn't allowed to connect from 

' | | IP);  
      End IF;  
End IF;  
End;  
/

The trigger has IP restrictions on the user epay_user (only allow ' 192.168.219.20 ', ' 192.168.219.22 ', if you need to set the IP segment, use% or? Instead, such as ' 192.168.219.% ').

Let's look at a few examples to test:

1 login from non-allowed IP address (192.168.219.21), Connection failed

[Oracle@lxdb2 ~]$ sqlplus epay_user@pri sql*plus:release 11.2.0.3.0 Production on  
      
Wed June 3 19:23:48 2013  
      
Copyrig HT (c) 1982, Oracle.  All rights reserved.  
      
Enter Password:   
ERROR:  
ora-00604:error occurred at recursive SQL Level 1  
ora-20001:user Epay_user Owed to connect from 192.168.219.21  
Ora-06512:at Line 10

2 from Allow IP address login (192.168.219.22), the connection is successful

[ORACLE@LXDB1 ~]$ sqlplus epay_user sql*plus:release 11.2.0.3.0 Production on  
      
Wed June 3 11:24:25 2013  
      
Copyright (c) 1982, 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 from the local login (192.168.219.23) is not affected by IP restrictions, the connection is successful

[ORACLE@LXDB1 ~]$ sqlplus epay_user sql*plus:release 11.2.0.3.0 Production on  
      
Wed June 3 11:24:25 2013  
      
Copyright ( c) 1982, Oracle.  All rights reserved.  
      
Enter Password:   
      
Connected to:  
Oracle Database 11g Enterprise Edition release 11.2.0.3.0-64bit Production  
Wi Th the partitioning, OLAP, Data Mining and real application testing options

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/