Protip: How to Setup User Activity & Database Logon Scans in StealthAUDIT for Oracle

StealthAUDIT for Oracle can monitor database user activity in all your Oracle databases. In addition, it can also enumerate and report on user permissions, database configuration, conduct a vulnerability assessment and can help you discover and report on sensitive data stored in your Oracle databases. StealthAUDIT Oracle activity monitoring can audit all types of database activity. Such as the type of SQL statement executed, changes to data, username, application, execution time, etc.,

StealthAUDIT for Oracle leverages native auditing available in Oracle databases to collect user activity, as well as successful and/or unsuccessful database logon activity. 

Prior to Oracle 12c, if one wanted to audit any aspect of the Oracle database, one had to set up an audit trail for each individual component.  Besides that, it was management intensive and had an impact on the database performance, at least in my experience as a DBA.  All that changed with the introduction of the Unified Auditing feature in Oracle 12c and above. Unified auditing comes standard as part of both Standard 2 and Enterprise editions with no need to buy additional licenses.  While it is installed by default, it is not enabled by default.

Unified Auditing consolidates all types of auditing into a single repository, which means that all the audit data is accessible in a single location. Setting up and maintaining Unified Auditing is a lot simpler than traditional auditing.  Oracle provides the DBMS_AUDIT_MGMT PL/SQL package for setting up and managing unified auditing.  The package supports automatic or manual purging of audit data based on the user requirements.  Unified auditing data is written to the AUDSYS schema in the SYSAUX tablespace keeping things clean and not mixing it up with the rest of the metadata in the SYSTEM tablespace. 

In addition to pure Unified Auditing mode, mixed mode auditing is also supported.  When a new instance of Oracle 12c database is created, by default the database is set up to use mixed mode auditing.  Mixed mode auditing is a combination of the older auditing facility that was available in pre-Oracle 12c versions and newer unified auditing.  The database can be configured to use either mixed mode auditing or unified auditing only.  StealthAUDIT will work with either mode of auditing.   It is important to note that in mixed mode both traditional and unified auditing features are available.  In pure unified auditing mode only the newer unified auditing features are available.

This blog will walk you through the steps required for setting up an Oracle database to audit DML activity on all or specific tables, database logon activity and DDL activity. Once the unified or mixed mode auditing is configured and enabled, Oracle will write the audit events to the SYSAUX schema.  StealthAUDIT for Oracle will scan the unified audit trail views to capture the audit events.

Fine Grained Auditing (FGA) provides the ability to audit policies associated with columns in application tables along with conditions necessary for an audit record to be generated.  It is worthwhile to note that FGA is only available in Oracle Enterprise Edition.  While StealthAUDIT for Oracle can take full advantage of FGA if it is available, it will only work with Oracle Enterprise Editions.  FGA is useful when there is a need to audit table access during specific periods or when specific columns are accessed, and certain conditions are met.  For example, if there is a need to audit read access on SALARY column in the HR.SALARIES table where SALARY > 50000 then enabling FGA on such a condition will do the trick.

Step 1

First, verify if the unified auditing is enabled or not by executing the following SQL statement using your favorite database tool.  In my case, unified auditing is already enabled so VALUE came back as TRUE.  If it comes back as FALSE, then follow the steps below to enable it based on your operating system.

SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

Based on whether the Oracle server operating system is Linux/UNIX or Windows following the steps as applicable.

For Oracle Standard 2 and Enterprise editions running on Linux and UNIX:

1.	Shutdown Oracle instance
SQL>shutdown immediate;

2.	Shutdown Oracle listener, if more than listener is configured, shutdown all of them
[oracle@oel73 ~]$ lsnrctl stop

3.	Make sure the Oracle instance is down
[oracle@oel73 ~]$ ps -ef| grep pmon

4.	Relink Oracle binaries using the uniaud_on option
[oracle@oel73 ~]$ cd $ORACLE_HOME
[oracle@oel73 lib]$ make -f ins_rdbms.mk uniaud_on ioracle

5.	Restart all instance, listener and any other Oracle process hosted on the same server
SQL>startup
[oracle@oel73 ~]$ lsnrctl start

6.	Check to ensure that the unified auditing shows as being enabled.  The value for the parameter ‘Unified Auditing’ should return the value of TRUE

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;

7.	To disable or turn off unified auditing follow Steps 1 through 6, the only exception being Step 4 which will be as follows
[oracle@oel73 lib]$ make -f ins_rdbms.mk uniaud_off ioracle

For Oracle Standard 2 and Enterprise editions running on Microsoft Windows:

1.	Logon to Windows server as a user with administrative access and go to Windows Services and stop Oracle related services.  Specifically, the Oracle instance and listener services.

2.	In Windows Command Prompt or Windows Explorer, navigate to the Oracle Home directory (%ORACLE_HOME%bin) and rename the %ORACLE_HOME%binorauniaud18.dll.dbl to orauniaud18.dll and leave it in the same directory.  Please note the number 18 might be different based on the version of Oracle you have.  For example, if you are on Oracle 12c, then the name of this file will be orauniaud12.dll.dbl.

3.	Restart the Oracle Services which were stopped in Step #1

4.	In SQLPlus run the following query to verify that it was enabled.
SQL>SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;

5.	To disable or turn off unified auditing follow Step 1 and in Step  2, rename the orauniaud18.dll back to orauniaud18.dll.dbl and restart the Oracle services and verify that the auditing is turned off by running the query from Step 4.

Step 2

Once the desired auditing mode is enabled and verified the next step is to create one or more audit policies based on the requirements.  If you would like to audit database changes, you need to create an audit policy to include the desired actions and enable the audit policy.  The policy below will apply to all the users in the database.

Create the audit policy using the SQL statement below.  I have used sample set of actions in this example.  Please refer to the Oracle documentation for a detailed list of available database actions that can be audited.

CREATE AUDIT POLICY sbits_dbchanges_audit_policy ACTIONS CREATE TABLE, DROP TABLE, ALTER TABLE, GRANT, REVOKE, CREATE USER, DROP USER, ALTER USER, CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, ALTER TRIGGER, CREATE AUDIT POLICY, ALTER AUDIT POLICY, DROP AUDIT POLICY, CREATE ROLE, DROP ROLE, CREATE PROCEDURE, DROP PROCEDURE, CREATE FLASHBACK ARCHIVE, ALTER FLASHBACK ARCHIVE, DROP FLASHBACK ARCHIVE,CREATE PACKAGE, DROP PACKAGE, ALTER PACKAGE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, CREATE PACKAGE BODY, ALTER PACKAGE BODY, DROP PACKAGE BODY, LOGON, LOGOFF, CREATE DIRECTORY, DROP DIRECTORY, CREATE JAVA, ALTER JAVA, DROP JAVA, CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE, DROP PLUGGABLE DATABASE;

Once the Audit Policy called sbits_dbchanges_audit_policy is created, it needs to be enabled using the following SQL statement.

AUDIT POLICY sbits_dbchanges_audit_policy;

If there is ever a need to disable an active audit policy use the following SQL statement to disable it.

NOAUDIT POLICY sbits_dbchanges_audit_policy;

To drop an existing policy, disable it first and issue drop statement as show below.

DROP AUDIT POLICY sbits_dbchanges_audit_policy;

Step 3

The next step is to create an audit policy to audit user activity (INSERT, UPDATE, DELETE) on tables of interest (for example, tables containing sensitive data).  The script for creating this audit policy is shown below and can be easily customized to add more objects to the existing audit or create new audits as required.

CREATE AUDIT POLICY sbits_tabchanges_audit_policy ACTIONS
SELECT, UPDATE, DELETE ON SUJITH.CUSTOMERS;

AUDIT POLICY sbits_tabchanges_audit_policy;

Step 4

The audit policy shown in Step 3 applies to all the users in the database.

CREATE AUDIT POLICY sbits_tabchanges2_audit_policy ACTIONS
SELECT, UPDATE, DELETE ON SUJITH.SALARY;

AUDIT POLICY sbits_tabchanges2_audit_policy EXCEPT sujith;

AUDIT POLICY sbits_tabchanges2_audit_policy EXCEPT bob,jane;

If there is a need to explicitly exclude a specific user or list of users, the script below will be helpful.

SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES;

Step 5

To review the list of unified audit polices and their status use the SQL statement below.

Oracle provides numerous types and levels of audit policies which allows customers to audit virtually every single aspect of the Oracle database. A detailed explanation and a list of auditable actions provided by Oracle can be found here.

Now that you have setup the required audited specifications for your Oracle databases, run the data collectors in StealthAUDIT and start reporting on them from a single pane of glass.

To learn more about how Stealthbits can help with auditing  your Oracle databases operations, visit our website: https://www.stealthbits.com/stealthaudit-for-oracle-product