SEM fine tuning of event for Oracle Database auditing

VEC3842

Need quick help to capture auditable database actions of delete and update from Oracle Database into SEM events.
We are able to capture create, alter, drop. Have configured "Oracle Auditor - Syslog" SEM connector
and OS level auditing in Oracle Database.

muhammad.osama

To capture UPDATE and DELETE actions, you need to extend your Oracle Audit policy beyond the basic schema changes (DDL) to include Data Manipulation Language (DML) actions.

Since you already have the Oracle Auditor - Syslog connector working for CREATE, ALTER, and DROP, your communication path (Oracle $\to$ OS Syslog $\to$ SEM) is healthy. The gap is simply in what the Oracle engine is choosing to write to that log.

1. Enable DML Auditing in Oracle

By default, standard auditing often focuses on DDL. You must explicitly tell Oracle to audit successful and unsuccessful DML attempts. Run the following commands in your Oracle SQL tool as a user with AUDIT SYSTEM privileges:

SQL

-- Audit all deletions across the databaseAUDIT DELETE ANY TABLE BY ACCESS;-- Audit all updates across the databaseAUDIT UPDATE ANY TABLE BY ACCESS;

Note: If you only want to monitor specific sensitive tables rather than the entire database (to save on log volume), use:

AUDIT UPDATE, DELETE ON schema_name.table_name BY ACCESS;

2. Verify Audit Trail Settings

For the SEM connector to see these events, Oracle must be writing them to the Operating System's syslog. Ensure your initialization parameters are set correctly:

1. SHOW PARAMETER audit_trail;It should be set to OS or XML, EXTEND.
2. SHOW PARAMETER audit_syslog_level;Ensure this matches the facility your OS syslog (and SEM) is listening to (e.g., local0.info).

3. Why "BY ACCESS" Matters

In your AUDIT command, always use BY ACCESS.

• BY SESSION: Writes one event for the entire connection (harder to audit specific timing).
• BY ACCESS: Writes a record for every single statement executed. This is the requirement for a clean audit trail in SEM.

4. SEM Connector Verification

Once the SQL commands above are applied, perform a test DELETE on a non-production table.

• Check the local OS log file (usually /var/log/messages or /var/log/secure) to see if the Oracle record appears.
• If it’s in the OS log but not in SEM, go to the SEM Console → Nodes → Manage Node Connectors and ensure the Oracle Auditor connector is looking at the correct log path.

5. Common "Gotcha": Fine-Grained Auditing (FGA)

If you need to know exactly what data was changed (e.g., "User changed Salary from 5000 to 6000"), standard auditing won't capture the values—it only captures that an UPDATE occurred. For the specific values, you would need to implement Oracle Fine-Grained Auditing (FGA) using DBMS_FGA, though this significantly increases log volume for SEM to process.