Oracle – Audit Trail (Logins, Connect Info).

Oracle – Audit Trail (Logins, Connect Info)
May 20, 2019.

**Notes: All scripts have been validated/used with Oracle 11g Ent Edition running on Oracle Linux.

The audit trail lets you see important session information, including who has connected to the database, how long for and where
failed logon attempts have come from. This information is essential for security.

The first step is to turn the audit trail on. This needs to be done in the database’s init.ora file. Add the following parameter to the init.ora file:

audit_trail=DB
or
*.audit_trail=’db_extended’
The extended will give you a more granular audit info even for SYSDBA sessions. The blog below link explains the differences.

https://jhdba.wordpress.com/2014/07/15/the-value-of-audit_traildbextended/

Please note that both types of audit info are going to the DB, not to an external flat file.
The database needs to be shutdown and started up again for the parameter to be read.

Validating the audit option is enabled:
 select value
 from v$parameter
 where name = ‘audit_trail’;

Enable Connection Auditing
SQL>audit connect;

The audit trail should now log every logon and logoff to the database

Querying the Audit Trail
The connection audit trial can be queried via the dba_audit_sessions view.
Some examples.

Count of Connects by User in the Last 7 Days.
SELECT das.username,
        COUNT(*) logonCount
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
  GROUP BY das.username;

Number of Minutes Connected By User in the Last 7 Days.
SELECT das.username,
        ROUND(SUM((NVL(das.logoff_time,SYSDATE)-das.timestamp)*1440)) connectMins
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
  GROUP BY das.username;

Unsuccessful Logon Attempts in the Last 7 Days.
SELECT das.username,
        das.os_username,
        das.terminal,
        TO_CHAR(das.timeStamp,’DD Mon YYYY HH24:MI’) timestamp,
        das.returnCode
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode != 0;

The above is particularly useful in triaging locked service accounts if they are used in scheduled reports. Also useful is identifying errant/unauthorized user accounts.

Connections Made Out Of Working Hours in the Last Week
SELECT das.username,
        TO_CHAR(das.timestamp,’DD Mon YYYY HH24:MI:SS’)   logontime,
        TO_CHAR(das.logoff_time,’DD Mon YYYY HH24:MI:SS’) logofftime
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
    AND NOT (    TO_NUMBER(TO_CHAR(das.timestamp,’D’)) < 6
             AND TO_NUMBER(TO_CHAR(das.timestamp,’HH24MI’)) BETWEEN 800 AND 1800 )
  ORDER BY das.username;

Prakash.

Oracle Linux: Setting the AUDIT_SYSLOG_LEVEL Parameter.Oracle 11g. RHEL6 (onwards).

Setting the AUDIT_SYSLOG_LEVEL Parameter. Oracle 11g. REHL6 (Onwards).
May 5, 2019

APPLIES TO:
Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
ALL platforms.

ISSUE:
Because of Infosec dictate we are required to port/export our DB logs to OS rsyslog.
Example: If the ‘Connect’ audit trail is enabled in the DB, the requirement would be to write these connect logs to OS rsyslogs.

SOLUTION:
AUDIT_SYSLOG_LEVEL parameter. When the AUDIT_TRAIL parameter is set to OS, writes DB audit records to the system audit log using the rsyslog utility.

To enable syslog auditing for all the users (privileged or not privileged), you assign a value of OS to the AUDIT_TRAIL initialization parameter, as described in “Setting the AUDIT_TRAIL Initialization Parameter”. 
You assign to the AUDIT_SYSLOG_LEVEL parameter a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority. The facility argument describes the part of the operating system that is logging the message while the priority argument defines the severity of the message.
The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the rsyslog.conf file in order to determine where to log information.
For example, the following statement identifies the facility as local1 with a priority level of warning:

AUDIT_SYSLOG_LEVEL=local1.warning
Setting the AUDIT_SYSLOG_LEVEL initialization parameter to the default value (NONE) will result in DBAs gaining access to the OS audit records.

To enable syslog auditing, follow these steps:
Assign a value of OS to the AUDIT_TRAIL initialization parameter:

For example:
SQL> ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
Options available for us:
Values:< BR>NONE

Disables standard auditing.
This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant.
If you created the database using Database Configuration Assistant, then the default is db.

OS
Directs all audit records to an operating system file. Oracle recommends that you use the OS setting, particularly if you are using an ultra-secure database configuration.

DB
Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.
If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

DB, EXTENDED
Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.
If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

XML
Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

XML, EXTENDED
Performs all actions of AUDIT_TRAIL=xml, and includes SQL text and SQL bind information in the audit trail.
Also set the AUDIT_SYSLOG_LEVELparameter.

SQL> ALTER SYSTEM SET AUDIT_SYSLOG_LEVEL=”local1.warning” SCOPE=SPFILE;
Set the AUDIT_SYSLOG_LEVEL parameter to specify a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority.
facility: Describes the part of the operating system that is logging the message. Accepted values are user, local0–local7, syslog, daemon, kern, mail, auth, lpr, news,uucp, andcron.

The local0–local7 values are predefined tags that enable you to sort the syslog message into categories. These categories can be log files or other destinations that the syslog utility can access.

priority: Defines the severity of the message. Accepted values are notice, info, debug, warning, err, crit, alert, and emerg.

The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the syslog.conf file to determine where to log the information.  The decision where to write the syslog entries does not belong to the Oracle services, but to the syslog daemon.

For example, the following statement identifies the facility as local1 with a priority level of warning:

AUDIT_SYSLOG_LEVEL=local1.warning

Add the audit file destination to the rsyslog configuration file /etc/rsyslog.conf.

For example, assuming you had set the AUDIT_SYSLOG_LEVEL to local1.warning, enter the following:

local1.warning    /var/log/audit.log

This setting logs all warning messages to the /var/log/audit.log file.

Comment: separate the entries in syslogd.conf by using TAB rather than spaces, otherwise it may not work for all syslogd versions, so the above would really be:

local1.warning<tab><tab>/var/log/audit.log

Also pre-create the file as follows (as root):

# touch /var/log/audit.log

The facility line for your messages in the file rsyslog.conf should appear before the “catch all” setting and you should include appropriate .none entry to “catch all” also.

Once the changes are made to the rsyslog.conf, restart the rsyslog service.

#systemctl restart rsyslog.servic

If you get a message like so:
Redirecting to /bin/systemctl restart syslog.service
Failed to restart syslog.service: Unit not found.

Then you are actually running rsyslog.
Make sure the changes are made to /etc/rsyslog.conf
#service rsyslog restart

Prakash

Oracle: PSU and CPU Patching for Oracle Databases (11.2.0.4 – Oracle Linux)

Oracle: PSU and CPU patching for Oracle Databases (11.2.0.4- Oracle Linux)
May 4, 2019

Oracle provides Critical Patch Updates every qtr.
The ‘readme’ doc that can be downloaded with the patches has all the notes to install the patches.These notes contain just specific info related to Oracle 11.2.0.4 and Oracle Linux.

The dates for these updates, in 2018/2019, are:
October 16, 2018
January 15, 2019
April 16, 2019
July 16, 2019

We are going to look at PSU (Patch Set Update) and CPU (Critical Patch Update) released on July 17, 2018
for Linux x86-64 system and Oracle Version 11.2.0.4.0.

1: PSU Patch:
Patch 27734982: DATABASE PATCH SET UPDATE 11.2.0.4.180717
You will need oracle Support.
https://support.oracle.com/epmos/faces/PatchHome

https://support.oracle.com/epmos/faces/PatchHome?_adf.ctrl-state=io0p5enpi_251&_afrLoop=472556696747336

Download the complete ‘Read Me’ document for expanded instructions when you are downloading the patch. Below are shown some important steps for Installation.

A: Environment checks:
Ensure that the $PATH definition has the following executables: make, ar, ld, and nm.
The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH definition as follows:
export PATH=$PATH:/usr/ccs/bin

B: One-off Patch Conflict Detection and resolution:
Determine whether any currently installed one-off patches conflict with the PSU patch as follows:
unzip p27734982_112040_.zip
cd 27734982
opatch prereq CheckConflictAgainstOHWithDetail -ph ./

The report will indicate the patches that conflict with PSU 27734982 and the patches for which PSU 27734982 is a superset.
Note that Oracle proactively provides PSU one-off patches for common conflicts with this patch.

C: Patch Installation:
*** Ensure that you shut down all the services running from the Oracle home (listner, DB, whatever)

  1. If you are using a Data Guard Physical Standby database, you must install this patch on both the primary database and the physical standby database, as described by My Oracle Support Document 278641.1.
  2. If this is an Oracle RAC environment, install the PSU patch using the OPatch rolling (no downtime) installation method as the PSU patch is rolling Oracle RAC installable. Refer to My Oracle Support Document 244241.1 Rolling Patch – OPatch Support for RAC.
  3. If this is not a Oracle RAC environment, shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator’s Guide.
  4. Rollback any patches found during the One-off Patch Conflict Detection.
  5. Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:
  6. unzip p27734982_112040_.zip
  7. cd 27734982
  8. opatch apply
  9. Install all resolutions to conflicts found during the One-off Patch Conflict Detection.

D: Post Patch:
Loading Modified SQL Files into the Database
The following steps load modified SQL files into the database. For an Oracle RAC environment, perform these steps on only one node.

  1. For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
  2. cd $ORACLE_HOME/rdbms/admin
  3. sqlplus /nolog
  4. SQL> CONNECT / AS SYSDBA
  5. SQL> STARTUP
  6. SQL> @catbundle.sql psu apply
  7. SQL> QUIT
    Running the above may make some objects invalid. To correct that:
  8. cd $ORACLE_HOME/rdbms/admin
  9. sqlplus /nolog
  10. SQL> CONNECT / AS SYSDBA
  11. SQL> @utlrp.sql

2: CPU Patch:
Patch 27923163: DATABASE PATCH SET UPDATE 11.2.0.4.180717

Download the complete ‘Read Me’ document for expanded instructions. Below are shown some important steps for Installation.

A: Environment checks:
Ensure that the $PATH definition has the following executables: make, ar, ld, and nm.
The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH definition as follows:
export PATH=$PATH:/usr/ccs/bin

B: One-off Patch Conflict Detection and resolution:
Determine whether any currently installed one-off patches conflict with the PSU patch as follows:
unzip p27923163_11204_.zip
cd 27923163
opatch prereq CheckConflictAgainstOHWithDetail -ph ./

The report will indicate the patches that conflict with CSU 27923163 and the patches for which CSU 27923163 is a superset.
Note that Oracle proactively provides CSU one-off patches for common conflicts with this patch.

C: Patch Installation:
*** Ensure that you shut down all the services running from the Oracle home (listner, DB, whatever)

  1. If you are using a Data Guard Physical Standby database, you must install this patch on both the primary database and the physical standby database, as described by My Oracle Support Document 278641.1.
  2. If this is an Oracle RAC environment, install the PSU patch using the OPatch rolling (no downtime) installation method as the PSU patch is rolling Oracle RAC installable. Refer to My Oracle Support Document 244241.1 Rolling Patch – OPatch Support for RAC.
  3. If this is not a Oracle RAC environment, shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator’s Guide.
  4. Rollback any patches found during the One-off Patch Conflict Detection.
  5. Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:
  6. unzip p27923163_11204_.zip
  7. cd 27923163
  8. $opatch apply
  9. Verify whether the patch has been successfully installed by running the following command:
  10. $ opatch lsinventory
  11. Install all resolutions to conflicts found during the One-off Patch Conflict Detection.

D: Post Patch :
Loading Modified SQL Files into the Database

  1. Install the SQL portion of the patch by running the following command for a single instance environment.
  2. cd $ORACLE_HOME/sqlpatch/27923163
  3. sqlplus /nolog
  4. SQL> CONNECT / AS SYSDBA
  5. SQL> startup upgrade
  6. SQL> @postinstall.sql
  7. SQL> shutdown
  8. SQL> startup
    For an Oracle RAC environment, reload the packages on one of the nodes using the following commands. Make sure no other instance of the database is up on the remote nodes.
    cd $ORACLE_HOME/sqlpatch/27923163
    sqlplus /nolog
    SQL> CONNECT / AS SYSDBA
    SQL> STARTUP
    SQL> alter system set cluster_database=false scope=spfile;
    SQL> SHUTDOWN
    SQL> STARTUP UPGRADE
    SQL> @postinstall.sql
    SQL> alter system set cluster_database=true scope=spfile;
    SQL> SHUTDOWN
    SQL> STARTUP
    Running the above may make some objects invalid. To correct that:
  9. cd $ORACLE_HOME/rdbms/admin
  10. sqlplus /nolog
  11. SQL> CONNECT / AS SYSDBA
  12. SQL> @utlrp.sql

Ref Links:
Patch Set Update (PSU)
https://support.oracle.com/epmos/faces/PatchDetail?_adf.ctrl-state=apoo9q95i_280&patch_name=27338049&releaseId=80112040&patchId=27338049&languageId=0&platformId=278&_afrLoop=57404584173725

Cumulative Patch Update (CPU)
https://support.oracle.com/epmos/faces/ui/patch/PatchDetail.jspx?_afrLoop=462082042173913&parent=DOCUMENT&patchId=26925576&sourceId=2118136.2&_afrWindowMode=0&_adf.ctrl-state=vpz5t9ja9_3204

Prakash