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:
|Traditional Auditing||Unified Auditing|
|Available in all releases of Oracle||Only available in Oracle 12c and above|
|Audit trail file at the OS level is available||Audit trail file at the OS level is not available|
|XML formatted audit trail file is available||XML formatted audit trail file is not available|
|No mandatory auditing of administrative actions||Administrative actions are audited|
|Role based segregation of duties is not available||Admin and Viewer roles allow for segregation of duties|
|Audit data can be tampered with||Audit data is tamper proof, even by the SYS user|
|Audit records can be queued in memory (queued-write mode) and can be writing to disk periodically||Audit actions are queued in SGA and flushed to a table in the AUDSYS schema thereby prevents the risk of losing audit records during an instance crash|
|RMAN, SQL Loader and Data Pump auditing is not available||RMAN, SQL Loader and Data Pump activities can be audited|
|Requires changes to init.ora parameters to enable auditing||Requires relinking of an Oracle library file, with no changes to the init.ora parameters required|
|DVSYS.AUDIT_TRAIL$ system table is used to store Oracle Database Vault audit actions||The table is renamed to DVSYS.OLD_AUDIT_TRAIL$ and retains old records. New records are made available|
|Oracle Label Security is administered using the SA_AUDIT_ADMIN PL/SQL package||The SA_AUDIT_ADMIN package is not available and the related functionality is rolled into unified auditing|
|Auditing data is logged to AUD$ and FGA$ tables||AUD$ and FGA$ tables are replaced by UNIFIED_AUDIT_TRAIL which resides in the SYSAUX tablespace|
|No support for writing audit data to SYSLOG in Unix/Linux and Windows Event log in Windows OS||Audit records can be written to Unix/Linux SYSLOG or to the Windows Event log in Windows OS|
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
Sujith Kumar has over 25 years of professional experience in the IT industry. Sujith has been extensively involved in designing and delivering innovative solutions for the Fortune 500 companies in the United States and across the globe for disaster recovery and high availability preparedness initiatives. Recently after leaving Quest Software/Dell after 19 years of service he was working at Cirro, Inc. focusing on database management and security. His main focus and area of interest is anything data related.
Sujith has a Master of Science in engineering degree from Texas A&M University and a Bachelor of Science in engineering degree from Bangalore University and has published several articles in referred journals and delivered presentations at several events.
Proper data security begins with a strong foundation. Find out what you're standing on with a free deep-dive into the security of your Structured and Unstructured Data, Active Directory, and Windows infrastructure.Read more
Start a Free Stealthbits Trial!
No risk. No obligation.