Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "ORA-28050 - Can I drop the SYSTEM User?"] [Next entry: "Oracles Free TNS Firewall - VALIDNODE_CHECKING"]

Add A SQL*Net Security Banner And Audit Notice

I would have to say whilst I see security banners on customers Unix boxes when I am allowed to log in as part of a security audit I canot ever remember seeing a security banner when I log into a customer database using SQL*Plus or our database security scanner PFCLScan or indeed any other tool. It is possible to add one and to therefore make sure that anyone who accesses your systems using SQL*Net is told that they can only access if they have permission and also to be told that they are being audited.

Oracle supports adding an Unauthorized access banner and an audit action banner. Lets see how this works. First connect to my 11.2.0.4 database as normal and check if we have a banner and show the version:

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 12:36:21 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>

OK, no banners, I didn't set them up yet. First lets connect to the Unix/Linux box and create the banner text files:

[root@oel1124 ~]# su - oracle
[oracle@oel1124 ~]$ cd $ORACLE_HOME/network/admin
[oracle@oel1124 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@oel1124 admin]$ vi unauth.txt
[oracle@oel1124 admin]$ vi audit.txt
[oracle@oel1124 admin]$ cat unauth.txt
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.
[oracle@oel1124 admin]$ cat audit.txt
This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.
[oracle@oel1124 admin]$

Now lets update the sqlnet.ora file to reference the text files for the banners. I created my two text files in the $ORACLE_HOME/network/admin directory in the same location as my sqlnet.ora file BUT you can put the files anywhere that the Oracle software owner has access to read the files. So next let's update the 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

SEC_USER_AUDIT_ACTION_BANNER=/u01/app/oracle/product/11.2.0/db_1/network/admin/audit.txt
SEC_USER_UNAUTHORIZED_ACCESS_BANNER=/u01/app/oracle/product/11.2.0/db_1/network/admin/unauth.txt
[oracle@oel1124 admin]$

The banner text for each parameter can be at most 512 bytes. So the banner message needs to be fairly succinct but as there are two of them we can have 1024 bytes of message and that should be enough for most people.

So, how to make it work? We can restart the listener:

[oracle@oel1124 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2020 13:04:23

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@oel1124 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2020 13:04:29

Copyright (c) 1991, 2013, Oracle. All rights reserved.

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 30-MAR-2020 13:04:29
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
[oracle@oel1124 admin]$
[oracle@oel1124 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2020 13:05: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 30-MAR-2020 13:04:29
Uptime 0 days 0 hr. 0 min. 35 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]$

So, when we connect to the database with SQL*Plus do we get the banners:

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 13:09:19 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL>

The short answer is no. We must restart the database for these parameters to take affect. This is odd in my opinion as these are network settings so it would have made more sense for these to take effect when the listener is restarted but never mind. Lets restart the database:

[oracle@oel1124 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 13:12:47 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.

ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
Database mounted.
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.

Database opened.
SQL>

This is interesting. Both banner texts are displayed in the order, Unauthorised banner and then audit banner text. The texts are displayed twice on startup. I was connected already AS SYSDBA and shutdown the database. I didn't reconnect but the banners displayed before the instance was started and then again after the instance started. Not sure why twice and also why display when no connection was made. Now lets connect with SQL*Plus from a client and see what happens:

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 30 13:19:38 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL>

Now we get both banners. What happens if I use SQL*Plus in silent mode:

C:\_aa\PB\bin>sqlplus -S system/oracle1@//192.168.56.85:1521/bfora.localdomain


^C
C:\_aa\PB\bin>

Nothing; it hangs and never times out. the -S works when there is no banner files but not when there is? is this is a bug? If we use SQL*Plus with -V then it does work and only shows the version:

C:\_aa\PB\bin>sqlplus -V system/oracle1@//192.168.56.85:1521/bfora.localdomain

SQL*Plus: Release 11.2.0.4.0 Production


C:\_aa\PB\bin>

If we do a connect from within SQL*Plus rather than a connect on the command line:

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
No unauthorised access is allowed to this system. You must have permission and a valid account and password provided by facilities and authorised by the Managing Director.

This system is Copyright PeteFinnigan.com Limited (c) 2020. All rights reserved.

This system is protected by PeteFinnigan.com Limited.

All actions in this system by YOU are audited and those audit trails may be used to enforce security restrictions.

Connected.
SQL>

This works the same as a connection on the command line.

What about other tools. We can create a project in PFCLScan our database vulnerability scanner that uses OCI to connect to the database and we can check the engine logs to see if the banner works:

...
[2020 Mar 30 12:28:42] Oscan: [-] Test database connection
[2020 Mar 30 12:28:42] Oscan: Attached to [//192.168.56.85:1521/bfora.localdomain]
[2020 Mar 30 12:28:42] Oscan: Connected to [//192.168.56.85:1521/bfora.localdomain] as [system ]
...

The above is a section from the plugin log where the database connection is tested. What about actually connecting during the scan:


[2020 Mar 30 12:30:49] Oscan: Connect to the database....
[2020 Mar 30 12:30:49] Oscan: Server Attached to [//192.168.56.85:1521/bfora.localdomain]
[2020 Mar 30 12:30:49] Oscan: Connected to [//192.168.56.85:1521/bfora.localdomain] as [system]
...

No, again it doesn't show any banner in the OCI connection. So we would need to make a modification to our OCI code to display the banners. We would need to use OCI_AttrGet(…) and use the OCI_ATTR_ACCESS_BANNER and OCI_ATTR_AUDIT_BANNER attributes to get the banners if they exist and display them in our OCI code.

So in summary we can set banners for unauthorised access and also audit trail notification but there is no mechanism to answer a question to accept the banner and then log in and it is too easy to ignore then banner in SQL*Plus with a -V or cause a hang with -S. Also unless your write code into your tools then OCI does not display these banners by default.

OK, by for now from WHF!!