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

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

Scheduled Executions

Alert Management

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 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.