AUDIT TABLE

Job details

Name:	AUDIT TABLE
Platform:	Oracle
Category:	Audit
Description:	Checks the AUD$ trail table for CREATE TABLE, DROP TABLE and TRUNCATE TABLE audit records for audited users (DCL used: ‘AUDIT TABLE BY username’)
Long description:	Checks the AUD$ trail table for CREATE TABLE, DROP TABLE and TRUNCATE TABLE audit records for audited users (DCL used: ‘AUDIT TABLE BY username’)
Version:	1.5
Default schedule:	15 4 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_dba_segments = ‘1′ & _priv_read_v_parameter = ‘1′]

Parameters

Name	Default value	Description
return status when CREATE TABLE	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when CREATE TABLE audit record is detected in SYS.AUD$ table.
return status when DROP TABLE	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when DROP TABLE audit record is detected in SYS.AUD$ table.
history threshold	90	The number of days the statistics are hold in the dbWatch history table.
return status when TRUNCATE TABLE	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when TRUNCATE TABLE audit record is detected in SYS.AUD$ table.
last run	2022.01.01 00:00:00	The last time the sys.aud$ table has been checked (format YYYY.MM.DD HH24:MI:SS).
keep WARNING/ALARM status	24	For how long time (in hours) the job will returng WARNING/ALARM status after last WARNING/ALARM event.
ignore if repetitive	10	Ignore if the recorded number of identical error messages exceeds the value for this parameter.

Job Summary

Purpose: The monitoring job is designed to inspect the AUD$ table for specific audit records concerning `CREATE TABLE`, `DROP TABLE`, and `TRUNCATE TABLE` actions performed by users who are subject to auditing. This helps in ensuring compliance and monitoring activities within critical database environments.
Why: Maintaining surveillance over database modifications is crucial as unauthorized or unintended DDL changes could lead to data loss or breach of security. The ability to detect these changes rapidly facilitates immediate response and also aids in audit trails, which are essential for compliance and security investigations.
Manual checking: To manually check for these records in the database, you can use the following SQL command:

SELECT * FROM SYS.AUD$ WHERE ACTION# IN (SELECT ACTION FROM AUDIT_ACTIONS WHERE NAME IN ('CREATE TABLE', 'DROP TABLE', 'TRUNCATE TABLE'));

Dependencies and Data Management

This job relies on stored procedures and additional tables that it interacts with to process and store the required data. The dependencies include:
- Main table: dbw_aud_table_ddl_proc – This is a procedure that actually processes the audit logs.
- Details history table: dbw_aud_table_details_histr – Stores historical details about the execution of this job.
- Audit history table: dbw_aud_table_histr_tab – Maintains a history of audit record processing.

On failure, clean-up procedures are triggered to maintain integrity and consistency of data.
The job configuration ensures that it can handle failures by potentially rolling back changes or cleaning up partial data.

Scheduled Executions

This job is scheduled to run periodically, ensuring that the audit logs are consistently monitored. The `default-schedule` tag within the XML suggests it is configured to execute at 4:15 every day.

Alert Management

The job assesses the severity of audit actions detected in the audit trail, and based on predefined conditions, it determines the level of alert (ALARM, WARNING, or OK). Each type of DDL action (CREATE, DROP, TRUNCATE) in the audit log has a specific return status value associated with it:
- Create Table Event: Returns a status of 1 (WARNING) when detected.
- Drop Table Event: Returns a status of 2 (ALARM) when detected.
- Truncate Table Event: Returns a status of 2 (ALARM) when detected.

Persistence of WARNING/ALARM status occurs for a duration after the last such event, which is a configurable parameter.

Reporting

Action Name	Record Count	Last Date	Action#
CREATE TABLE	Count of Create Table logs	Last recorded date of CREATE	Action code for CREATE
DROP TABLE	Count of Drop Table logs	Last recorded date of DROP	Action code for DROP
TRUNCATE TABLE	Count of Truncate Table logs	Last recorded date of TRUNCATE	Action code for TRUNCATE

The table above, automatically generated by the job, gives a summary of different actions captured in the AUD$ table and helps in quick overview and accessing trends or immediate past activities.

The architecture of this job ensures that audit tables are monitored effectively, alerts are managed appropriately, and compliance and security postures are maintained in an Oracle database environment.