Setting A Logon Security Message In Oracle

Security is the watchword for almost all businesses today, especially for computer systems and databases. For legal reasons, any ‘snooping’ the administrators may do on the users logged into such systems needs to be disclosed in a security banner. Such banners are commonplace in UNIX/Linux systems but are rarely found for database logins; I suppose it’s presumed that such users will be logging into the database server first, where such a message is prominently displayed. Many connections may be remote, however, and in those cases the server-side disclaimer is never shown. Let’s look at how Oracle addresses this issue.

For UNIX/Linux systems such a banner is placed in a file located in the /etc directory named motd, for Message Of The Day. At login, this file is read and displayed on the users terminal to inform them that Big Brother is watching. A sample motd file is shown below:

********************************************************************************
WARNING! This computer system is the property of Happy Pants Overall Overhaulers
and may be accessed only by authorized users for Legitimate business purposes. 
Unauthorized use of this system is strictly prohibited and may be subject to 
criminal prosecution.  Happy Pants Overall Overhaulers may monitor any activity 
or communication on the  system and retrieve any information stored within the
system.  Users should have no expectation of privacy as to any communication
on or information stored within the system, including information stored locally
on the hard drive or other media in use with this unit (e.g., floppy disks,
PDAs and other hand-held peripherals, CD-ROMs, etc.)  Unauthorized or
improper use of this system may result in administrative disciplinary
action and civil and criminal penalties.  By continuing to use
this system you indicate your awareness of and consent to these terms and
conditions of use. LOG OFF IMMEDIATELY if you do not agree to the conditions
stated in this warning.
********************************************************************************

This provides all of the necessary text (and some additional text) to inform users that they are being monitored and any activity that may be suspicious can be investigated. It also provides notification that there are penalties for such behavior. Unfortunately, Oracle can’t display that much text at login; we can edit the message to provide the necessary notification in fewer words. Let’s look at a version of that notice that can be displayed by Oracle:

WARNING! This database is the property of Happy Pants Overall Overhaulers and may be accessed only by 
authorized users for Legitimate business purposes. Unauthorized use of this database is strictly prohibited 
and may be subject to criminal prosecution.  Happy Pants Overall Overhaulers may monitor any activity  
in the database.  By continuing to use this database you indicate your consent to these conditions 
of use. LOG OFF IMMEDIATELY if you do not agree to the conditions stated in this warning.

It’s considerably smaller (around 500 characters, the limit that Oracle can display through SQL*Plus as a banner) but it does provide the necessary notification to the users. So how does one configure Oracle to display such text? It requires edits to the sqlnet.ora by adding a parameter named SEC_USER_AUDIT_ACTION_BANNER, then a shutdown and startup of the database to read the supplied file. Looking at a sqlnet.ora file properly configured to display the security banner we see:

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)
SEC_USER_AUDIT_ACTION_BANNER=C:appsec_banner.txt

We’re set; after stopping and starting the database, logins now produce the following output:

C:>sqlplus bing

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 10:25:14 2017

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

Enter password:
WARNING! This database is the property of Happy Pants Overall Overhaulers and may be accessed only by
authorized users for Legitimate business purposes. Unauthorized use of this database is strictly prohibited
and may be subject to criminal prosecution.  Happy Pants Overall Overhaulers may monitor any activity
in the database.  By continuing to use this database you indicate your consent to these conditions
of use. LOG OFF IMMEDIATELY if you do not agree to the conditions stated in this warning.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Lest you think this only applies to local logins Oracle will display the same text for remote connections:

C:>sqlplus bing@smookie

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 10:25:14 2017

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

Enter password:
WARNING! This database is the property of Happy Pants Overall Overhaulers and may be accessed only by
authorized users for Legitimate business purposes. Unauthorized use of this database is strictly prohibited
and may be subject to criminal prosecution.  Happy Pants Overall Overhaulers may monitor any activity
in the database.  By continuing to use this database you indicate your consent to these conditions
of use. LOG OFF IMMEDIATELY if you do not agree to the conditions stated in this warning.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Validated users are now notified that they are being ‘snooped upon’ while connected to this database, so they had best watch their step. The text can be changed if necessary, but any edits to the security banner file will require a database shutdown and subsequent startup so the modified text is displayed. Like the pfile and/or spfile, Oracle reads this file only at startup; any changes won’t be reflected in the message until the database is bounced.

Given the current security concerns for enterprise computing systems having such a message displayed may not deter a malicious user, presuming one can get through the additional firewall security and server hardening necessary to make such systems as hack-proof as possible. Yes, there can be attacks from within, and simply having a security banner displayed without following through on the auditing and monitoring won’t do much good. Configuring such a banner is the last step in implementing a robust security policy. The security banner is a legal necessity in order to successfully prosecute malicious user access, unauthorized user access or both (remember that attacks can come from within, which makes the access authorized but malicious or damaging).

Keeping systems and databases secure is a major concern; having the ability to notify users of monitoring activities protects the business and, hopefully, keeps the users in line.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles