====== [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:
<code sql>
ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;
ALTER SYSTEM SET audit_file_dest='/u02/admin/<DBSID>/adump' SCOPE=SPFILE ;
</code>
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
[[http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#CEGHHFBF|More info here]]

You can check if audit is enabled with the following query:
<code sql>COL NAME FORMAT A50
COL VALUE FORMAT A50
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%audit%' ;
</code>

The kind of audit methods are described [[http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams017.htm#REFRN10006|here]]

===== Change tablespace =====

Create the tablespace:
<code sql>
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 ;
</code>


Query actual config:
<code sql>
col parameter_name format a50
col parameter_value format a50
 SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
;
</code>

Change the tablespace:
<code sql>
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;
/
</code>

Check config again.

====== Query actual config ======
General configuration:
<code sql>
 column PARAMETER_NAME format A40 ; 
 column PARAMETER_VALUE format A40 ; 
select * from DBA_AUDIT_MGMT_CONFIG_PARAMS;
</code>

AUDIT parameters:
<code sql>
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 ;
</code>
<code sql>
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 ;
</code>

same but:
<code sql>
 SELECT * FROM DBA_OBJ_AUDIT_OPTS where owner not in ('LBACSYS', 'DVSYS') ;
</code>

Priviledges audited:
<code sql>
select * from DBA_PRIV_AUDIT_OPTS ;
</code>

====== Disable default config (if you want) ======
<code sql>
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 ;
</code>

====== AUDIT ANYTHING to the main table ======
<code sql>
AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
</code>
======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: <code sql>DELETE FROM DBA_AUDIT_MGMT_CLEAN_EVENTS</code> | 
| 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 [[http://www.dba-oracle.com/t_tracking_counting_failed_logon_signon_attempts.htm|enabled]].

===== Format =====
Its a good practice to format the columns before quering them:
<code sql>
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 ;
</code>
=====Query FAILED User logins=====
<code sql>
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
;
</code>




=====Query User logins=====
<code sql>
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
;
</code>


=====From where a user connects=====
<code sql>
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
;
</code>

===== Query everything EXCEPT LOGIN events =====
With SQL:
<code 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
;
</code>

Without SQL:
<code 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
;
</code>

===== 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:
<code sql>
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
;
</code>
===== Policies =====
<code sql>
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 ;
</code>
&
<code sql>
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;
</code>


===== Failed querys by day =====
<code sql>
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
;
</code>

===== 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 :
<code sql>
DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# < (SYSDATE-10) ;
</code>

====== Examples ======
===== Auditing login failures =====
Creating the rule:
<code sql>
audit create session whenever not successful;
</code>
And as usual, the query:
<code sql>
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;
</code>

===== 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:
<code sql>
AUDIT EXECUTE PROCEDURE BY ORAUSER BY ACCESS ;
</code>

Query that rule:
<code sql>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;
</code>


Now you can query the DBA_AUDIT_OBJECT table for results, for example:
<code sql>
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 ;
</code>


===== Auditing ALL USER actions =====

''audit_trail'' must be st to ''db,extended'':
<code sql>
alter system set audit_trail=db,extended scope=spfile;
</code>

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

Then:
<code sql>
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;
</code>

Take care, this will insert a huge number of rows in ''sys.aud$''
====== Disable audit ======
To simply disable AUDITING on statements:
<code sql>
NOAUDIT ALL STATEMENTS;
</code>

You must use the same syntax as the AUDIT, for example:
<code sql>
NOAUDIT EXECUTE PROCEDURE BY ORAUSER;
</code>

To completely disable audit:

<code sql>ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE ;
</code>

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 ======
[[http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG525|Fine-Grained Auditing]]