The following is an example of a trigger:
Copy Code code as follows:
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 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 raise_application_error ( -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
Copy Code code as follows:
[Oracle@lxdb2 ~]$ Sqlplus Epay_user@pri
Sql*plus:release 11.2.0.3.0 Production on Wed June 3 19:23:48 2013
Copyright (c) 1982, Oracle. All rights reserved.
Enter Password:
ERROR:
Ora-00604:error occurred at recursive SQL level 1
Ora-20001:user Epay_user isn't allowed 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
Copy Code code as follows:
[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
Copy Code code as follows:
[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
