Auditing, Standard Database
Overview
Standard Database Auditing records user database actions. Records can be stored in the database audit trail or in files on the operating system. Standard auditing includes operations on privileges, schemas, objects, and statements.
To enable Standard Database Auditing requires a parameter change and issuing the appropriate commands to specify what to be audited. In Oracle 11g auditing is on by default. In Oracle 10g you must enable it but nothing is audited by default once it is active. The table SYS.AUD$ is used to store audit records and is stored in the SYSTEM tablespace. Depending on what is being audited it can get quite large so it is recommended to move this to another tablespace.
Oracle 11g\12c default auditing settings:
ALTER ANY PROCEDURE CREATE ANY JOB DROP ANY TABLE ALTER ANY TABLE CREATE ANY LIBRARY DROP PROFILE ALTER DATABASE CREATE ANY PROCEDURE DROP USER ALTER PROFILE CREATE ANY TABLE EXEMPT ACCESS POLICY AUDIT ROLE BY ACCESS CREATE EXTERNAL JOB GRANT ANY OBJECT PRIVILEGE ALTER SYSTEM CREATE PUBLIC DATABASE LINK GRANT ANY PRIVILEGE ALTER USER CREATE SESSION GRANT ANY ROLE AUDIT SYSTEM CREATE USER AUDIT SYSTEM BY ACCESS DROP ANY PROCEDUREAUDIT_TRAIL Values (DB|EXTEND|XML|OS|NONE)
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. NONE or FALSE Auditing is disabled.
If OS used you will no longer be able to query into the corresponding Oracle views. You will have to rely on OS and third-party tools to extract information from audit logging.
AUDIT_TRAIL=OS on Windows
- Auditing actions will be written to the Windows Application Event log.
- Make sure to also set AUDIT_SYS_OPERATIONS=TRUE as it is not the default.
- AUDIT_FILE_DEST is not used.
- Make sure to set Maximum log size as it can get really big fast.
- From the Event Viewer right-click on the Application log.
- On the General tab set Maximum log size. Example: 32,768.
- The default path to the Windows Application log is:
%SystemRoot%\System32\Winevt\Logs\Application.evtx
Commands to Enable for Windows:
alter system set audit_trail=OS scope=SPFILE; alter system set audit_sys_operations=true scope=SPFILE;
Display
show parameter audit_trail SELECT count(*) FROM SYS.AUD$; COL username FORMAT a25 COL terminal FORMAT a15 COL action_name FORMAT a20 SELECT username, terminal, action_name, to_char(timestamp,'DDMMYYYY:HHMISS') timestamp, returncode FROM dba_audit_session WHERE rownum <= 25; SELECT extended_timestamp,username,owner,obj_name,action_name FROM dba_audit_trail WHERE cast(extended_timestamp AS DATE) >= sysdate-.15 ORDER BY timestamp ASC;
Enable (storing in database)
- Create AUDIT tablespace.
- Move SYS.AUD$ to the AUDIT tablespace.
- Set the database for auditing. ALTER SYSTEM SET audit_trail=<DB|EXTEND|XML|NONE> SCOPE=spfile;
- Bounce database.
CREATE BIGFILE TABLESPACE "AUDIT" DATAFILE 'c:\oradata\dbf\audit.dbf' SIZE 100m AUTOEXTEND ON NEXT 5m MAXSIZE 10g EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO LOGGING; ALTER TABLE AUD$ MOVE TABLESPACE "AUDIT" LOB (sqltext) STORE AS aud_sqltext (TABLESPACE "AUDIT") LOB (sqlbind) STORE AS aud_sqlbind (TABLESPACE "AUDIT"); ALTER SYSTEM SET audit_trail=DB SCOPE=spfile; SHUTDOWN IMMEDIATE STARTUP
Disable
- Unset the database for auditing.
- Bounce database.
SQLPlus> ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile; SQLPlus> SHUTDOWN IMMEDIATE SQLPlus> STARTUP
Audit Trail Views
View | Description |
---|---|
AUDIT_ACTIONS | Contains descriptions for audit trail action type codes. |
ALL_DEF_AUDIT_OPTS | Contains default object-auditing options that will be applied when objects are created. |
DBA_STMT_AUDIT_OPTS | Describes current system auditing options across the system and by user. |
DBA_PRIV_AUDIT_OPTS | Describes current system privileges being audited across the system and by user. |
DBA|USER_OBJ_AUDIT_OPTS | Describes auditing options on all objects. The USER view describes auditing options on all objects owned by the current user. |
DBA|USER_AUDIT_TRAIL | Lists all audit trail entries. The USER view shows audit trail entries relating to current user. |
DBA|USER_AUDIT_OBJECT | Contains audit trail records for all objects in the system. The USER view lists audit trail records for statements concerning objects that are accessible to the current user. |
DBA|USER_AUDIT_SESSION | Lists all audit trail records concerning CONNECT and DISCONNECT. The USER view lists all audit trail records concerning connections and disconnections for the current user. |
DBA|USER_AUDIT_STATEMENT | Lists audit trail records concerning the database, or for the USER view, issued by the user. |
DBA_AUDIT_EXISTS | Lists audit trail entries produced BY AUDIT NOT EXISTS. |
DBA_AUDIT_POLICIES | Shows all the auditing policies on the system. |
DBA_FGA_AUDIT_TRAIL | Lists audit trail records for value-based auditing. |
DBA_COMMON_AUDIT_TRAIL | Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format. |
Auditing Command Examples
Command Format
AUDIT <statement_option|privilege_option> [by user] [by {session|access}] [ whenever <successful|unsuccessful>];
Commands
-- Capturing logon and logoff attempts for all users except privileged attempts. AUDIT CREATE SESSION;
AUDIT select table, insert table, update table; AUDIT select table, insert table, update table WHENEVER unsuccessful;-- Auditing any selects, inserts or updates on specific users.
AUDIT select table, insert table, update table BY hr,oe;-- Auditing any selects, inserts or updates on a specific table.
AUDIT select ON hr.employees; AUDIT insert ON hr.employees; AUDIT update ON hr.employees; -- Equivalent to above three commands. AUDIT select, insert, update ON hr.employees;-- Setting Default Auditing Options
AUDIT alter, grant, insert, update, delete ON DEFAULT;