ProTip: How to Setup User Activity & Server Logon Scan in StealthAUDIT for Oracle

Now that you have been using StealthAUDIT for Oracle for a while, you might be wondering how to squeeze more value out of the product by enhancing the information it is collecting and reporting on. 

StealthAUDIT for Oracle relies on the Oracle Traditional Auditing or Unified Auditing capabilities to collect and report on user activity, as well as successful or unsuccessful server or database logon activity. Neither Traditional Auditing nor Unified Auditing is turned on by default in an Oracle database instance. Therefore, additional database configuration is necessary to collect and report database user activity in StealthAUDIT.

This blog will walk you through the steps required for setting up Oracle Unified Auditing to audit DML and DDL activity on all database objects and specific tables containing sensitive data and instance level logon activity.  Oracle introduced Unified Auditing in Oracle 12c and it has been available in all subsequent releases since.  In releases Oracle 11g and below, Traditional Auditing is used for capturing database user activity.  In Oracle 12c and above, Traditional Auditing is still available along with Unified Auditing and both can be enabled simultaneously in a mode referred to as Mixed Mode Auditing. In case of multitenant environments, different audit settings can be set at the CDB or for each PDB.  Each PDB, including the root, has its own unified audit trail.  One caveat that you need to be aware of is that the fine-grain audit polices (FGA) can only be set at the PDB level and not the root or CDB.

For the purpose of this blog, I am using a single stand-alone Oracle 18c Enterprise Edition instance running on CentOS 8.  However, the steps are similar for other version of Oracle starting with Oracle 12c R1 and other versions and flavors of Linux.

NOTE:  In order to enable Unified Auditing, the steps are different for Oracle running on Linux/Unix compared to Windows.

In Oracle 12c and above, I highly recommend using Unified Auditing mainly due to simplicity, consolidation, security, and performance compared to Traditional Auditing.

SQL> SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER =’Unified Auditing’;

I cannot think of a situation where you will have to turn on both at the same time. Below is a summary of differences between Unified Auditing and Traditional Auditing:

Once either Unified Auditing or Traditional Auditing is enabled based on the instructions below, StealthAUDIT for Oracle will query the database and capture audit events.  In addition, a subset of the Unified Audit Trail records, if written to the Windows Event log, can be captured using the EVENTLOG or SMARTLOG data collectors in StealthAUDIT in addition to the out-of-the-box Oracle Solution Set.

Step 1) To begin, it is necessary to verify if Unified Auditing was enabled at the time of database creation.  If the value is FALSE, then Unified Auditing needs to be enabled.  If the value is TRUE, then it is already enabled and you can proceed to Step 6.

Step 2) Shutdown the database instance.  Please note that if you are enabling Unified Auditing in an active environment, there will be a downtime as the Oracle instance needs to be shutdown to enable Unified Auditing.

Step 3) Shutdown the listener.

Step 4) Relink Oracle with the uniaud_on option as follows:

[oracle@sbora18c ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@sbora18c lib]$ make -f ins_rdbms.mk uniaud_on ioracle

Step 5)  Restart the Oracle instance and the listener.

[oracle@sbora18c ~]$ sqlplus / as sysdba
SQL> startup
[oracle@sbora18c ~]$ lsnrctl start 

Step 6) Verify that Unified Auditing is enabled.

SQL> SELECT DISTINCT POLICY_NAME FROM AUDIT_UNIFIED_POLICIES;
SQL> SELECT DISTINCT POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES;
SQL> SELECT AUDIT_OPTION FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME ='ORA_SECURECONFIG';

SQL> SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER =’Unified Audting’;

Step 7)  By default, Oracle 18c comes with ten audit polices pre-configured.  Out of the ten audit polices, two policies are enabled by default.  The enabled polices include ORA_LOGON_FAILURES and ORA_SECURECONFIG.  The ORA_LOGON_FAILURES will audit any failed logins, ORA_SECURECONFIG is a collection of multiple actions as shown below.

Step 8) The pre-configured and pre-enabled unified audit policies should cover most use cases with some exceptions.  Let us create and enable some audit policies starting with DML activity on a single table that contains sensitive data.

SQL> CREATE AUDIT POLICY SBITS_DML_ACTION
ACTIONS SELECT,UPDATE,DELETE ON SBITS.EMPLOYEES;
SQL> AUDIT POLICY SBITS_DML_ACTION;

Step 9) Based on the two unified audit policies that are enabled and one custom enabled unified audit policy, let us see what the data looks like in Oracle.

SQL> SELECT ACTION_NAME,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP FROM UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME='SUJITH' AND UNIFIED_AUDIT_POLICIES ='ORA_SECURECONFIG';

Oracle provides extensive instance level and object level audit actions including a set of pre-configured audit policies.  A detailed explanation and list of all the audit actions available in the Oracle database instance can be found here.

Now that you have setup the required audit specifications for your servers or databases, run the data collectors in StealthAUDIT and start reporting on it. To learn more about how Stealthbits can help with auditing  your SQL databases operations, visit our website: https://www.stealthbits.com/stealthaudit-for-oracle-product