As I said a few days ago the first step is to stop people connecting to the database who should not be allowed to connect. We must limit the actual people who are allowed to directly connect to the database to just and only just those users who need to. no more. Once these users / people are identified then we can further limit how they can connect (i.e. what tools are allowed) and then further strengthen them with strong passwords and least rights; i.e. only have exactly the right privileges to do their job and no more. This is easy to say but in practice hard to do for many many reasons.
Finally after we control the users and their rights we can then think about data security controls including permissions on tables/views etc and even context based security such as VPD, OLS, DV Realms or hand coded solutions with views and triggers all based on factors such as user id, time, where, when, what etc.
Valid node checking is Oracles free simple TNS firewall that exists in the listener. I have been advising clients and others at talks and presentations and training to use this technology for years. Its free and simple but a little brute force - I.e. it works at the IP level and port (because it works on TNS its tied to the current listener port). It would be better if there was a little more flexibility maybe down to the tool level/ user/ ?? . We can do that level with a login trigger though so all is not lost.
OK, lets test valid node checking. First go to the Linux box and go to the $ORACLE_HOME/network/admin and open the sqlnet.ora file and turn on valid node checking by setting TCP.VALIDNODE_CHECKING=yes and then create an invited nodes list - a white list of IP addresses or Hostnames. This can be done with the TCP.INVITED_NODES parameter. See my box as follows to see that I have added the IP Address of the database server only at this point:
[oracle@oel1124 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/oracle
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.56.85)
[oracle@oel1124 admin]$
Now try and connect remotely from a client PC using SQL*Plus:
C:\_aa\PB\bin>sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:38:01 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL>
This clearly doesn't work as I should not have been able to connect from any IP Address except the database server. Let us find out my IP Address first:
C:\_aa\PB\bin>ipconfig
Windows IP Configuration
Ethernet adapter Ethernet 3:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::a11c:3e6e:4d67:b94a%8
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :
Ethernet adapter Ethernet 4:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::45f6:ee3f:46b4:bd8f%14
Autoconfiguration IPv4 Address. . : 169.254.189.143
Subnet Mask . . . . . . . . . . . : 255.255.0.0
Default Gateway . . . . . . . . . :
Wireless LAN adapter Local Area Connection* 1:
Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :
Wireless LAN adapter Local Area Connection* 2:
Media State . . . . . . . . . . . : Media disconnected
Connection-specific DNS Suffix . :
Wireless LAN adapter Wi-Fi:
Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::8554:bf8f:3b91:e321%13
IPv4 Address. . . . . . . . . . . : 192.168.1.96
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1
C:\_aa\PB\bin>
We have 192.168.56.1 - this is the gateway for Virtual box. so we should not be able to connect as that IP Address is not in the valid node checking invited nodes list. Restart the listener and re-register it:
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:37:13
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> exit
[oracle@oel1124 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:37:18 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> alter system register;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@oel1124 admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:37:28
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:37:13
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Services Summary...
Service "bfora.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
Service "bforaXDB.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
Now try and connect remotely again from 192.168.56.1 and see what happens:
C:\_aa\PB\bin>sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:41:13 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
ERROR:
ORA-12547: TNS:lost contact
SQL>
Now the connection is prevented so we have proved that it works. Go in now and change the sqlnet.ora again to include my IP Address so that I can connect to the database from my SQL*Plus client but no one else can:
[oracle@oel1124 admin]$ vi sqlnet.ora
[oracle@oel1124 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/oracle
TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.56.85,192.168.56.1)
[oracle@oel1124 admin]$
Now restart the listener again:
[oracle@oel1124 admin]$ lsnrctl
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:39:39
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:39:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> exit
[oracle@oel1124 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 31 09:39:54 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> alter system register;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@oel1124 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-MAR-2020 09:40:04
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2020 09:39:46
Uptime 0 days 0 hr. 0 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel1124/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel1124.localdomain)(PORT=1521)))
Services Summary...
Service "bfora.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
Service "bforaXDB.localdomain" has 1 instance(s).
Instance "bfora", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oel1124 admin]$
Now try the remote connection using SQL*plus from my client PC:
SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL>
Of course it works now. There are two points here. Yesterday in the post "Add A SQL*Net Security Banner And Audit Notice" I showed that for the banner parameters SEC_USER_AUDIT_ACTION_BANNER and SEC_USER_UNAUTHORIZED_ACCESS_BANNER in the sqlnet.ora we had to re-start the database for them to take effect. But, for valid node checking we only need to restart the listener. Inconsistency is not good. The second point is that this is virtual box and my true IP Address is in the 192.168.1.* range but I access the database on virtualbox networking via the gateway 192.168.56.1. This is not ideal if clients are going to access a database on a box in a virtual box network as the gateway needed to be added. Ensure that when you use valid node checking that you do not need to allow all access via a gateway as this will defeat the object of it.
OK, hope this helps, bye from WFH
