[HOWTO] Oracle AUDIT management

Description

Little document on how to audit oracle things.

Enable audit

To enable audit we must set the init parameter, normally that is:

ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;
ALTER SYSTEM SET audit_file_dest='/u02/admin/<DBSID>/adump' SCOPE=SPFILE ;

You must restart Oracle to make the change effective. Ensure that you have a purged process and enough diskspace on audit_file_dest cause Oracle WILL log SYS actions there (SYS actions are ALWAYS logged).

AUDIT_TRAIL can be:

OS
DB
DB,EXTENDED
XML
XML,EXTENDED

More info here

You can check if audit is enabled with the following query:

COL NAME FORMAT A50
COL VALUE FORMAT A50
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%audit%' ;

The kind of audit methods are described here

Change tablespace

Create the tablespace:

CREATE tablespace SYSAUD DATAFILE
   '/u02/oradata/DBTEST/datafile/SYSAUD_001.dbf' SIZE 1073741824 AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M, 
   '/u02/oradata/DBTEST/datafile/SYSAUD_002.dbf' SIZE 1073741824 AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M ;

Query actual config:

col parameter_name format a50
col parameter_value format a50
 SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
;

Change the tablespace:

BEGIN
    SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
        audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        audit_trail_location_value => 'SYSAUD');
END;
/
 
BEGIN
    SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(
        audit_trail_type           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
        audit_trail_location_value => 'SYSAUD');
END;
/
 
BEGIN
    SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(
        audit_trail_type           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        audit_trail_location_value => 'SYSAUD');
END;
/

Check config again.

Query actual config

General configuration:

 COLUMN PARAMETER_NAME format A40 ; 
 COLUMN PARAMETER_VALUE format A40 ; 
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

AUDIT parameters:

COL OWNER FORMAT A30
COL OBJECT_NAME FORMAT A30
COL OBJECT_TYPE FORMAT A23
COL ALT FORMAT A3
COL AUD FORMAT A3
COL COM FORMAT A3
COL DEL FORMAT A3
COL GRA FORMAT A3
COL IND FORMAT A3
COL INS FORMAT A3
COL LOC FORMAT A3
COL REN FORMAT A3
COL SEL FORMAT A3
COL UPD FORMAT A3
COL EXE FORMAT A3
COL CRE FORMAT A3
COL REA FORMAT A3
COL WRI FORMAT A3
COL FBK FORMAT A3
SELECT * FROM DBA_OBJ_AUDIT_OPTS ;

COL USER_NAME FORMAT COL30;
COL USER_NAME FORMAT A30;
COL PROXY_NAME FORMAT A30 ;
COL AUDIT_OPTION FORMAT A40;
COL SUCCESS FORMAT A10;
COL FAILURE FORMAT A10;
SELECT  * FROM DBA_STMT_AUDIT_OPTS ;

same but:

 SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE owner NOT IN ('LBACSYS', 'DVSYS') ;

Priviledges audited:

SELECT * FROM DBA_PRIV_AUDIT_OPTS ;

Disable default config (if you want)

NOAUDIT ALTER SYSTEM ;
NOAUDIT SYSTEM AUDIT ;
NOAUDIT CREATE SESSION ;
NOAUDIT CREATE USER ;
NOAUDIT ALTER USER ;
NOAUDIT DROP USER ;
NOAUDIT PUBLIC SYNONYM ;
NOAUDIT DATABASE LINK ;
NOAUDIT ROLE ;
NOAUDIT PROFILE ;
NOAUDIT CREATE ANY TABLE ;
NOAUDIT ALTER ANY TABLE ;
NOAUDIT DROP ANY TABLE ;
NOAUDIT CREATE PUBLIC DATABASE LINK ;
NOAUDIT GRANT ANY ROLE ;
NOAUDIT SYSTEM GRANT ;
NOAUDIT ALTER DATABASE ;
NOAUDIT CREATE ANY PROCEDURE ;
NOAUDIT ALTER ANY PROCEDURE ;
NOAUDIT DROP ANY PROCEDURE ;
NOAUDIT ALTER PROFILE ;
NOAUDIT DROP PROFILE ;
NOAUDIT GRANT ANY PRIVILEGE ;
NOAUDIT CREATE ANY LIBRARY ;
NOAUDIT EXEMPT ACCESS POLICY ;
NOAUDIT GRANT ANY OBJECT PRIVILEGE ;
NOAUDIT CREATE ANY JOB ;
NOAUDIT CREATE EXTERNAL JOB ;

AUDIT ANYTHING to the main table

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

Useful Tables

AUDIT CONFIGURATION TABLES

Name	Description
DBA_PRIV_AUDIT_OPTS	Describes current system privileges being audited across the system and by user
DBA_STMT_AUDIT_OPTS	Describes current statement auditing options across the system and by user
DBA_AUDIT_POLICIES	Lists all the fine-grained auditing policies on the system
DBA_AUDIT_MGMT_CONFIG_PARAMS	Displays the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package
DBA_AUDIT_POLICY_COLUMNS	Describes the fine-grained auditing policy columns on the tables and views throughout the database.
DBA_OBJ_AUDIT_OPTS	Displays the objects on which auditing options have been enabled
ALL_AUDIT_POLICIES	Describes the fine-grained auditing policies on the tables and views accessible to the current user
ALL_AUDIT_POLICY_COLUMNS	Describes the fine-grained auditing policy columns on the tables and views accessible to the current user.
ALL_DEF_AUDIT_OPTS	Lists default object-auditing options that are to be applied when objects are created

TRAIL TABLES

Name	Description
DBA_AUDIT_OBJECT	Lists audit trail records for all objects in the system
DBA_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database
DBA_AUDIT_SESSION	Lists all audit trail records concerning CONNECT and DISCONNECT
DBA_AUDIT_TRAIL	Lists all standard audit trail entries in the AUD$ table
DBA_COMMON_AUDIT_TRAIL	Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format
DBA_FGA_AUDIT_TRAIL	Lists audit trail records for fine-grained auditing.

Less important:

Name	Description
DBA_AUDIT_EXISTS	Lists audit trail entries produced BY AUDIT NOT EXISTS
DBA_AUDIT_MGMT_CLEAN_EVENTS	“Displays the history of purge events. Periodically, as user SYS connected with the SYSDBA privilege, you should delete the contents of this view so that it does not grow too large. For example: DELETE FROM DBA_AUDIT_MGMT_CLEAN_EVENTS
DBA_AUDIT_MGMT_CLEANUP_JOBS	Displays the currently configured audit trail purge jobs
DBA_AUDIT_MGMT_LAST_ARCH_TS	Displays the last archive timestamps that have set for audit trail purges.
V$LOGMNR_CONTENTS	Contains log history information. To query this view, you must have the SELECT ANY TRANSACTION privilege.
V$XML_AUDIT_TRAIL	Shows standard, fine-grained, SYS, and mandatory audit records written in XML format files.

Querys

The audit must be enabled.

Format

Its a good practice to format the columns before quering them:

COLUMN USERNAME FORMAT A40 ;
COLUMN USERHOST FORMAT A40 ;
COLUMN OWNER FORMAT A30 ;
COLUMN OBJ_NAME FORMAT A30 ;
COLUMN ACTION_NAME FORMAT A18 ;
COLUMN NEW_OWNER FORMAT A30 ;
COLUMN NEW_NAME FORMAT A30 ;
COLUMN SES_ACTIONS FORMAT A10 ;
COLUMN SESSIONID FORMAT 99999999 ;
COLUMN ENTRYID FORMAT 9999 ;
COLUMN STATEMENTID FORMAT 9999 ;
COLUMN RETURNCODE FORMAT 9999 ;
COLUMN INSTANCE_NUMBER FORMAT 9999 ;
COLUMN SCN FORMAT 99999999999 ;
COLUMN SQL_BIND FORMAT A50 ;
COLUMN SQL_TEXT FORMAT A100 ;
COLUMN OBJ_EDITION_NAME FORMAT A10 ;

Query FAILED User logins

COL ETIMESTAMP FORMAT A22
COL DB_USER FORMAT         A40
COL OS_USER FORMAT         A40
COL USERHOST FORMAT        A25
COL OBJECT_SCHEMA FORMAT   A15
COL STATEMENT_TYPE FORMAT  A20
 
SELECT TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP,
	 DB_USER , 
	 OS_USER , 
	 USERHOST , 
	 OBJECT_SCHEMA , 
     RETURNCODE,
	 STATEMENT_TYPE
FROM DBA_COMMON_AUDIT_TRAIL
WHERE RETURNCODE <>0
ORDER BY EXTENDED_TIMESTAMP
;

Query User logins

COL ETIMESTAMP FORMAT A22
COL DB_USER FORMAT         A10
COL OS_USER FORMAT         A10
COL USERHOST FORMAT        A25
COL OBJECT_SCHEMA FORMAT   A15
COL STATEMENT_TYPE FORMAT  A20
 
SELECT TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP,
	 DB_USER , 
	 OS_USER , 
	 USERHOST , 
	 OBJECT_SCHEMA , 
	 STATEMENT_TYPE
FROM DBA_COMMON_AUDIT_TRAIL
WHERE STATEMENT_TYPE IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
ORDER BY EXTENDED_TIMESTAMP
;

From where a user connects

COL ETIMESTAMP FORMAT A22
COL DB_USER FORMAT         A10
COL OS_USER FORMAT         A10
COL USERHOST FORMAT        A25
COL OBJECT_SCHEMA FORMAT   A15
 
SELECT 	 DB_USER , 
	 OS_USER , 
	 USERHOST ,
         TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP,
	 OBJECT_SCHEMA
FROM DBA_COMMON_AUDIT_TRAIL
WHERE STATEMENT_TYPE = 'LOGON'
ORDER BY EXTENDED_TIMESTAMP
;

Query everything EXCEPT LOGIN events

With SQL:

SET PAGES 0
COL ETIMESTAMP FORMAT A22
COL DB_USER FORMAT         A10
COL OS_USER FORMAT         A10
COL USERHOST FORMAT        A25
COL OBJECT_SCHEMA FORMAT   A15
COL OBJECT_NAME FORMAT     A30
COL STATEMENT_TYPE FORMAT  A20
COL SQL_TEXT FORMAT        A90
 
SELECT TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP,
	 DB_USER , 
	 OS_USER , 
	 USERHOST , 
	 OBJECT_SCHEMA , 
	 OBJECT_NAME , 
	 STATEMENT_TYPE , 
	 SQL_TEXT
FROM DBA_COMMON_AUDIT_TRAIL
WHERE STATEMENT_TYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
ORDER BY EXTENDED_TIMESTAMP
;

Without SQL:

SET PAGES 0
COL ETIMESTAMP FORMAT A22
COL DB_USER FORMAT         A10
COL OS_USER FORMAT         A10
COL USERHOST FORMAT        A25
COL OBJECT_SCHEMA FORMAT   A15
COL OBJECT_NAME FORMAT     A30
COL STATEMENT_TYPE FORMAT  A20
 
SELECT TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP,
	 DB_USER , 
	 OS_USER , 
	 USERHOST , 
	 OBJECT_SCHEMA , 
	 OBJECT_NAME , 
	 STATEMENT_TYPE
FROM DBA_COMMON_AUDIT_TRAIL
WHERE STATEMENT_TYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
ORDER BY EXTENDED_TIMESTAMP
;

Searching the SQL of an audit record

This query will display ressults on 2 rows each one. First, the SELECT query to obtain the desired SQL and just bellow the information of that query:

SET PAGES 0
SET LINESIZE 150
SET COLSEP |
COL SELECT_TEXT FORMAT     A150
COL ETIMESTAMP FORMAT      A22
COL DB_USER FORMAT         A10
COL OS_USER FORMAT         A10
COL OBJECT_SCHEMA FORMAT   A15
COL OBJECT_NAME FORMAT     A35
COL STATEMENT_TYPE FORMAT  A20
 
SELECT 'SELECT SQL_TEXT FROM DBA_COMMON_AUDIT_TRAIL WHERE SESSION_ID = ' || SESSION_ID ||
' AND STATEMENTID = ' || STATEMENTID || ' AND ENTRYID = ' || ENTRYID || ' ; ' SELECT_TEXT, TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP,
	DB_USER , 
	OS_USER , 
	OBJECT_SCHEMA , 
	OBJECT_NAME , 
	STATEMENT_TYPE
FROM DBA_COMMON_AUDIT_TRAIL
WHERE STATEMENT_TYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
ORDER BY EXTENDED_TIMESTAMP
;

Policies

COL OWNER FORMAT A30
COL OBJECT_NAME FORMAT A30
COL OBJECT_TYPE FORMAT A23
COL ALT FORMAT A3
COL AUD FORMAT A3
COL COM FORMAT A3
COL DEL FORMAT A3
COL GRA FORMAT A3
COL IND FORMAT A3
COL INS FORMAT A3
COL LOC FORMAT A3
COL REN FORMAT A3
COL SEL FORMAT A3
COL UPD FORMAT A3
COL EXE FORMAT A3
COL CRE FORMAT A3
COL REA FORMAT A3
COL WRI FORMAT A3
COL FBK FORMAT A3
COL REF FORMAT A3
SELECT * FROM DBA_OBJ_AUDIT_OPTS ;

&

COLUMN USER_NAME FORMAT A33 ;
COLUMN PROXY_NAME FORMAT A33 ;
COLUMN AUDIT_OPTION FORMAT A40 ;
COLUMN SUCCESS FORMAT A20 ;
COLUMN FAILURE FORMAT A20 ;
SELECT  USER_NAME,
   AUDIT_OPTION,
   SUCCESS,
   FAILURE
FROM DBA_STMT_AUDIT_OPTS ORDER BY USER_NAME;

Failed querys by day

SELECT THEDAY, COUNT(*)
FROM (
    SELECT TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY')) THEDAY
    FROM DBA_COMMON_AUDIT_TRAIL 
    WHERE STATEMENT_TYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
    AND NOT RETURNCODE=0
    AND NOT (SUBSTR(SQL_TEXT,1,30) LIKE '%INSERT%' OR SUBSTR(SQL_TEXT,1,30) LIKE '%MERGE%')
    ORDER BY EXTENDED_TIMESTAMP
    )
GROUP BY THEDAY
ORDER BY THEDAY
;

DBA_AUDIT_TRAIL Maintenance

You must be careful about DBA_AUDIT_TRAIL records so you don't exhaust the SYSAUX tablespace space. Erasing records by time is as simple as :

DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# < (SYSDATE-10) ;

Examples

Auditing login failures

Creating the rule:

audit CREATE SESSION whenever NOT successful;

And as usual, the query:

COL OS_USERNAME FORMAT A20
COL USERNAME FORMAT A20
COL TERMINAL FORMAT A30
COL LOGONTIME FORMAT A25
SELECT
   os_username,
   username,
   terminal,
   to_char(TIMESTAMP,'MM-DD-YYYY HH24:MI:SS') LOGONTIME
FROM
   dba_audit_trail
WHERE ROWNUM <30;

Auditing Objects

Copy/paste of oracle documentation:

The object must be a table, view, sequence, stored procedure, function, package, materialized view, mining model, or library.

For example, I want to audit all the executions of the procedures by a user:

AUDIT EXECUTE PROCEDURE BY ORAUSER BY ACCESS ;

Query that rule:

COLUMN USER_NAME FORMAT A33 ;
COLUMN PROXY_NAME FORMAT A33 ;
COLUMN AUDIT_OPTION FORMAT A40 ;
COLUMN SUCCESS FORMAT A20 ;
COLUMN FAILURE FORMAT A20 ;
SELECT  USER_NAME,
   AUDIT_OPTION,
   SUCCESS,
   FAILURE
FROM DBA_STMT_AUDIT_OPTS ORDER BY USER_NAME;

Now you can query the DBA_AUDIT_OBJECT table for results, for example:

COLUMN USERNAME FORMAT A10 ;
COLUMN USERHOST FORMAT A20 ;
COLUMN OWNER FORMAT A10 ;
COLUMN OBJ_NAME FORMAT A20 ;
COLUMN ACTION_NAME FORMAT A18 ;
COLUMN NEW_OWNER FORMAT A10 ;
COLUMN NEW_NAME FORMAT A10 ;
COLUMN SES_ACTIONS FORMAT A10 ;
COLUMN SESSIONID FORMAT 99999999 ;
COLUMN ENTRYID FORMAT 9999 ;
COLUMN STATEMENTID FORMAT 9999 ;
COLUMN RETURNCODE FORMAT 9999 ;
COLUMN INSTANCE_NUMBER FORMAT 9999 ;
COLUMN SCN FORMAT 99999999999 ;
COLUMN SQL_BIND FORMAT A10 ;
COLUMN SQL_TEXT FORMAT A10 ;
COLUMN OBJ_EDITION_NAME FORMAT A10 ;
 
 
SELECT 	USERNAME,
	USERHOST,
	OWNER,
	OBJ_NAME,
	ACTION_NAME,
	NEW_OWNER,
	NEW_NAME,
	SES_ACTIONS,
	SESSIONID,
	ENTRYID,
	STATEMENTID,
	RETURNCODE,
	INSTANCE_NUMBER,
	SCN,
	SQL_BIND,
	SQL_TEXT,
	OBJ_EDITION_NAME,
	TIMESTAMP
FROM DBA_AUDIT_OBJECT ;

Auditing ALL USER actions

audit_trail must be st to db,extended:

ALTER system SET audit_trail=db,extended scope=spfile;

Restart is needed for this change to be applied (oracle rules…).

Then:

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

Take care, this will insert a huge number of rows in sys.aud$

Disable audit

To simply disable AUDITING on statements:

NOAUDIT ALL STATEMENTS;

You must use the same syntax as the AUDIT, for example:

NOAUDIT EXECUTE PROCEDURE BY ORAUSER;

To completely disable audit:

ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE ;

And restart the instance of DDBB

Links

http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/
http://www.oracle-base.com/articles/10g/auditing-10gr2.php
Master Note For Oracle Database Auditing (Doc ID 1299033.1)

FGA

Fine-Grained Auditing

Ciberterminal Wiki

Table of Contents