Security Message

On generic Linux OS we’re able to add some stuff in file “/etc/motd” in order to give you a security WARNING or a FUNNY logo. Of course you can read my this post to find out something. Then in Oracle how could I archive the same effect when logging on to the command line window of SQL*Plus? After reading David Fitzjarrell‘s article “Setting A Logon Security Message In Oracle” I know the answer.

David mentioned a parameter “SEC_USER_AUDIT_ACTION_BANNER” which has been added in file “sqlnet.ora” that is located in the “$ORACLE_HOME/network/admin” directory on his post. Next I search that parameter on Oracle Docs and have understood it has existed from version 11g and later. The following 2 screenshots can also indicate it.

SEC_USER_AUDIT_ACTION_BANNER_7.3_10.2

SEC_USER_AUDIT_ACTION_BANNER_11gR1

Here I quote an example configuring the value of “SEC_USER_AUDIT_ACTION_BANNER” on file “sqlnet.ora” from Oracle Net Services Administrator’s Guide on 18c.


To specify a text file containing the banner contents that warn the user about possible user action auditing.

Name of the file for which the database owner has read permissions.

SEC_USER_AUDIT_ACTION_BANNER=/…/sec_banner.txt


Via the above description I believe you’ve comprehended its concept. Now I will use the following several steps to perform them on my own oracle 18c test environment.

  • creating a TXT file “sec_banner.txt”
  • adding this line “SEC_USER_AUDIT_ACTION_BANNER = specific location of sec_banner.txt” to file “sqlnet.ora”
  • restarting (“shutdown immediate” first and next “startup”) Oracle database

Firstly I need create a TXT file “sec_banner.txt” on directory “security” (creating which in advance in order to save “sec_banner.txt” to a fixed and clear place) that is located on “$ORACLE_BASE/admin/ora18c”. My operation process is as follows.

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

[oracle@xxxx ~]$ base
[oracle@xxxx oracle]$ ls
admin  audit  cfgtoollogs  checkpoints  diag  oradata  oraInventory  product
[oracle@xxxx oracle]$
[oracle@xxxx oracle]$ cd admin
[oracle@xxxx admin]$
[oracle@xxxx admin]$ ls
ora18c
[oracle@xxxx admin]$ cd ora18c
[oracle@xxxx ora18c]$
[oracle@xxxx ora18c]$ ls
adump  dpdump  pfile  scripts  xdb_wallet
[oracle@xxxx ora18c]$
[oracle@xxxx ora18c]$ mkdir -p security
[oracle@xxxx ora18c]$
[oracle@xxxx ora18c]$ cd security
[oracle@xxxx security]$
[oracle@xxxx security]$ vi sec_banner.txt

*******************************************************************************
*                                                                             *
*                            *********************                            *
*                            *                   *                            *
*                            *   !!!WARNING!!!   *                            *
*                            *                   *                            *
*                            *********************                            *
*                                                                             *
*******************************************************************************
*                                                                             *
*    This database 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. 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.                                                         *
*                                                                             *
*******************************************************************************
~
~
~
"sec_banner.txt" 19L, 1441C written

[oracle@xxxx security]$ pwd
/opt/oracle/admin/ora18c/security

[oracle@xxxx security]$ ls -lrht
total 4.0K
-rw-r--r-- 1 oracle oinstall 1.5K Nov 19 14:56 sec_banner.txt

Secondly adding “SEC_USER_AUDIT_ACTION_BANNER = …/sec_banner.txt” to “sqlnet.ora”.

[oracle@xxxx security]$ home
[oracle@xxxx dbhome_1]$
[oracle@xxxx dbhome_1]$ cd network/admin/
[oracle@xxxx admin]$ ls
listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora
[oracle@xxxx admin]$ vi sqlnet.ora
# sqlnet.ora Network Configuration File: /opt/oracle/product/18c/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

SEC_USER_AUDIT_ACTION_BANNER = /opt/oracle/admin/ora18c/security/sec_banner.txt
~
~
~
"sqlnet.ora" 7L, 276C written

The last step I go to restart Oracle.

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Nov 21 10:38:41 2019
Version 18.3.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 8.1068E+10 bytes
Fixed Size                 29901800 bytes
Variable Size            1.4227E+10 bytes
Database Buffers         6.6572E+10 bytes
Redo Buffers              238530560 bytes
Database mounted.
Database opened.
SQL>
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

This time I logged into SQL*Plus again on SYS schema you’ll see the previous SECURITY MESSAGE I added.

[oracle@xxxx security]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Nov 21 10:54:24 2019
Version 18.3.0.0.0

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

*******************************************************************************
*                                                                             *
*                            *********************                            *
*                            *                   *                            *
*                            *   !!!WARNING!!!   *                            *
*                            *                   *                            *
*                            *********************                            *
*                                                                             *
*******************************************************************************
*                                                                             *
*    This database 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. 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 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> 

I hope this post will help my fellow DBAs on their Oracle Security aspect.

Good luck!

Addendum

If you try to test my previous case on Oracle 11gR2 you’ll get an odd phenomenon as follows.

*******************************************************************************
*                                                                             *
*                            *********************                            *
*                            *                   *                            *
*                            *   !!!WARNING!!!   *                            *
*                            *                   *                            *
*

I only re-modify the content of my security message like the followig description.

[oracle@xxxxx security]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 12:53:32 2019

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

!!!WARNING!!!

This database 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. 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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SYS@xxxxx>

I guess it seems like to only show total 7-8 lines stuff on 11g.

1 thought on “Security Message

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.