oracledba.help
SpecialTopics

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 PROCEDURE
AUDIT_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)

  1. Create AUDIT tablespace.
  2. Move SYS.AUD$ to the AUDIT tablespace.
  3. Set the database for auditing. ALTER SYSTEM SET audit_trail=<DB|EXTEND|XML|NONE> SCOPE=spfile;
  4. 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

  1. Unset the database for auditing.
  2. Bounce database.
SQLPlus> ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile;

SQLPlus> SHUTDOWN IMMEDIATE
SQLPlus> STARTUP

Audit Trail Views

ViewDescription
AUDIT_ACTIONSContains descriptions for audit trail action type codes.
ALL_DEF_AUDIT_OPTSContains default object-auditing options that will be applied when objects are created.
DBA_STMT_AUDIT_OPTSDescribes current system auditing options across the system and by user.
DBA_PRIV_AUDIT_OPTSDescribes current system privileges being audited across the system and by user.
DBA|USER_OBJ_AUDIT_OPTSDescribes auditing options on all objects. The USER view describes auditing options on
all objects owned by the current user.
DBA|USER_AUDIT_TRAILLists all audit trail entries. The USER view shows audit trail entries relating to current user.
DBA|USER_AUDIT_OBJECTContains 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_SESSIONLists 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_STATEMENTLists audit trail records concerning the database, or for the USER view, issued by the user.
DBA_AUDIT_EXISTSLists audit trail entries produced BY AUDIT NOT EXISTS.
DBA_AUDIT_POLICIESShows all the auditing policies on the system.
DBA_FGA_AUDIT_TRAILLists audit trail records for value-based auditing.
DBA_COMMON_AUDIT_TRAILCombines 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;

-- Auditing any selects, inserts or updates all tables (this could generate a lot of overhead).
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;

<- SpecialTopics