Auditing in Oracle 10g 

• Server Setup
• Audit Options
• View Audit Trail
• Maintenance and Security
• Fine Grained Auditing

• Fine Grained Auditing (9i)
• Fine Grained Auditing Enhancements (10g)
• Uniform Audit Trail (10g)
• Audit Trail Contents (10g)
• Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2

Server Setup

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

• none or false - Auditing is disabled.
• db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
• db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
• xml- Auditing is enabled, with all audit records stored as XML format OS files.
• xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
• os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248600 bytes
Variable Size              71303848 bytes
Database Buffers          213909504 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
SQL>

Audit Options

CONNECT sys/password AS SYSDBA

CREATE USER audit_test IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;

CONNECT sys/password AS SYSDBA

AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

• DDL (CREATE, ALTER & DROP of objects)
• DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
• SYSTEM EVENTS (LOGON, LOGOFF etc.)

CONN audit_test/password

CREATE TABLE test_tab (
  id  NUMBER
);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;

View Audit Trail

SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

14 rows selected.

SQL>

• DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
• DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
• DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
FROM   dba_audit_trail
WHERE  owner = 'AUDIT_TEST'
ORDER BY timestamp;

USERNAME   EXTENDED_TIMESTAMP                  OWNER      OBJ_NAME   ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00  AUDIT_TEST TEST_TAB   CREATE TABLE
AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00  AUDIT_TEST TEST_TAB   INSERT
AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00  AUDIT_TEST TEST_TAB   UPDATE
AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00  AUDIT_TEST TEST_TAB   SELECT
AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00  AUDIT_TEST TEST_TAB   DELETE
AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00  AUDIT_TEST TEST_TAB   DROP TABLE

6 rows selected.

SQL>

COLUMN db_user       FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name   FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT db_user,
       extended_timestamp,
       object_schema,
       object_name,
       action
FROM   v$xml_audit_trail
WHERE  object_schema = 'AUDIT_TEST'
ORDER BY extended_timestamp;

DB_USER    EXTENDED_TIMESTAMP                  OBJECT_SCH OBJECT_NAM     ACTION
---------- ----------------------------------- ---------- ---------- ----------
AUDIT_TEST 16-FEB-2006 14:14:33.417000 +00:00  AUDIT_TEST TEST_TAB            1
AUDIT_TEST 16-FEB-2006 14:14:33.464000 +00:00  AUDIT_TEST TEST_TAB            2
AUDIT_TEST 16-FEB-2006 14:14:33.511000 +00:00  AUDIT_TEST TEST_TAB            6
AUDIT_TEST 16-FEB-2006 14:14:33.542000 +00:00  AUDIT_TEST TEST_TAB            3
AUDIT_TEST 16-FEB-2006 14:14:33.605000 +00:00  AUDIT_TEST TEST_TAB            7
AUDIT_TEST 16-FEB-2006 14:14:34.917000 +00:00  AUDIT_TEST TEST_TAB           12

6 rows selected.

SQL>

• EXTENDED_TIMESTAMP - A more precise value than the exising TIMESTAMP column.
• PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
• GLOBAL_UID - Global Universal Identifier for an enterprise user.
• INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
• OS_PROCESS - Operating system process id for the oracle process.
• TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
• SCN - System change number of the query. This column can be used in flashback queries.
• SQL_BIND - The values of any bind variables if any.
• SQL_TEXT - The SQL statement that initiated the audit action.