Tuesday, August 7, 2012

Oracle: How to block access to database by IP address?


Oracle allows to block clients based on their IP address or hostname.
To keep people out of your production database use the following solution.
Solution:
1. Get to $ORACLE_HOME/network/admin
[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$
2. Edit the sqlnet.ora file similar way:
[oracle@localhost admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost, 192.168.11.22)
tcp.excluded_nodes = (192.168.33.44)
[oracle@localhost admin]$
3. Restart the listener.
Now connection to your database from one of the IPs excluded will be prevented.
Example:
Trying to connect mysid Oracle db from ip 192.168.33.44:
C:\>sqlplus myuser/...@mysid
SQL*Plus: Release 11.2.0.1.0 Production on
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name:

1 comment:

SAKET NAIDU said...

how to block database access via db link only?