Put IP address Restriction in Oracle 10g

Sometimes we have a fear in mind if a person has user name, password, ip address and sid of database then he/she can easily connect to our database and export it or harm the database.

To overcome this fear we have IP Restriction feature in Oracle 10g. We can invite node i.e. only selected nodes can connect to server or we can exclude node i.e. selected nodes can’t connect to server. Steps for it

1.) Choose and collect the IP address from which you want allow your database to connect. Suppose I want my database to be connected from following ips. and etc.

2.) Now go to sqlnet.ora which is in Oracle_home\NETWORK\ADMIN\sqlnet.ora on your database server (keep in mind on the database server).

3.) Edit sqlnet.ora file and add following lines in it.


Use the parameter TCP.VALIDNODE_CHECKING to check for the TCP.INVITED_NODES and TCP.EXCLUDED_NODES to determine which clients to allow or deny access.

By this example we are allowing only given nodes to connect to server. Any system in the world other than this IP’s can’t connect to database server.

4.) Now go to command prompt and reload your listener using. (Windows OS)

Z:\>lsnrctl reload

LSNRCTL for 32-bit Windows: Version - Production on 02-JUL-2009 11:50:44
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=htss27.HTSS.com)(PORT=1521)))
The command completed successfully

For Linux system

Go to bin directory in ORACLE_HOME/bin and then
[oracle@htss06 bin]$ ./lsnrctl reload
LSNRCTL for Linux: Version - Production on 02-JUL-2009 12:25:17
Copyright (c) 1991, 2005, Oracle. All rights reserved.
The command completed successfully

Now your system is ip restricted.

5.) You can verify by trying to connect from any system other then system having this ip.

Z:\>sqlplus neo@neo163
SQL*Plus: Release - Production on Thu Jul 2 13:01:59 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ORA-12537: TNS:connection closed
This is error because this system’s IP is This is not included in invited nodes list.

One task is over if you want opposite of it. I.e. restrict some of the ips to access or connect database.

Go to 3rd step.

Put these lines in sqlnet.ora


And reload your listener as in 4th step.
Now the given IPS can’t connect to the database. Any ip other than these can connect to the database.

1 comment:

  1. Thanks for sharing this article..really appreciate it!!!!!!!!!!!!!!