Auditing The Database for User Actions

Auditing is the monitoring and recording of selected user database actions.
When you use standard auditing, Oracle Database writes the audit records to either to DBA_AUDIT_TRAIL (the SYS.AUD$ Table), the operating system audit trail, or to the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.In addition, the actions performed by administrators are recorded in the syslog audit trail.

After enabling auditing, and enabling the type of actions you need to audit, issue the following queries to find out actions on the database.

Sample Audit enabling query:

audit update table, delete table, insert table by user123 by access;

Checking details from dba_audit_trail

COLUMN os_username FORMAT A10
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A15
COLUMN timestamp FORMAT A10
COLUMN action_name FORMAT A20

SELECT os_username,
username,
timestamp,
obj_name,
owner,
action_name
FROM dba_audit_trail
ORDER BY timestamp;

Checking details from sys.aud$

COLUMN userid FORMAT A10
COLUMN userhost FORMAT A10
COLUMN terminal FORMAT A10
COLUMN timestamp# FORMAT A10
COLUMN statement FORMAT A20
COLUMN obj$name FORMAT A15
COLUMN action# FORMAT A20
SELECT userid,
userhost,
terminal,
timestamp#,
statement,
obj$name,
action#
FROM sys.aud$
ORDER BY timestamp#;