Automatic audit archive in 11gR1

Set up automatic audit archive

 * Oracle audits the AUDIT ROLE SQL statement by default (when AUDIT_TRAIL=DB).

The privileges that are audited by default are as follows: 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 Reference: (http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/auditing.htm#CEGGCIAE)

Additional audit http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm
 * Additional audits can be added.

SYS AS SYSDBA> show parameter audit_trail
 * To configure AUDIT_TRAIL if it was changed or disabled.

NAME                                TYPE        VALUE --- -- audit_trail                         string      NONE

SYS AS SYSDBA> alter system set audit_trail=db scope=spfile; System altered.

srvctl stop database -d ORA11G srvctl start database -d ORA11G

SYS AS SYSDBA> show parameter audit_trail

NAME                                TYPE        VALUE --- -- audit_trail                         string      DB

This includes - Moving the audit trail to a different tablespace - Automated purging See more details at http://www.oracle-base.com/articles/11g/AuditingEnchancements_11gR2.php (1) conn audit_user create table SESSION_AUDIT ( SESSIONID      NUMBER,                                   ENTRYID         NUMBER,                                  STATEMENT       NUMBER,                                   TIMESTAMP#      DATE,        USERID          VARCHAR2(30), USERHOST        VARCHAR2(128), TERMINAL        VARCHAR2(255),                        ACTION#         NUMBER,                                   RETURNCODE      NUMBER,                                   OBJ$CREATOR     VARCHAR2(30), OBJ$NAME        VARCHAR2(128), AUTH$PRIVILEGES VARCHAR2(16), AUTH$GRANTEE    VARCHAR2(30), NEW$OWNER       VARCHAR2(30), NEW$NAME        VARCHAR2(128), SES$ACTIONS     VARCHAR2(19), SES$TID         NUMBER, LOGOFF$LREAD    NUMBER, LOGOFF$PREAD    NUMBER, LOGOFF$LWRITE   NUMBER, LOGOFF$DEAD     NUMBER, LOGOFF$TIME     DATE, COMMENT$TEXT    VARCHAR2(4000), CLIENTID        VARCHAR2(64), SPARE1          VARCHAR2(255), SPARE2         NUMBER, OBJ$LABEL      RAW(255), SES$LABEL      RAW(255), PRIV$USED      NUMBER, SESSIONCPU     NUMBER, NTIMESTAMP#    TIMESTAMP(6), PROXY$SID      NUMBER, USER$GUID      VARCHAR2(32), INSTANCE#      NUMBER, PROCESS#       VARCHAR2(16), XID            RAW(8), AUDITID        VARCHAR2(64), SCN            NUMBER, DBID           NUMBER, SQLBIND        CLOB, SQLTEXT        CLOB, OBJ$EDITION    VARCHAR2(30) ) PARTITION BY RANGE (NTIMESTAMP#) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION p_2010_08 VALUES LESS THAN (TO_DATE('01-09-2010', 'DD-MM-YYYY')) ) grant INSERT on SESSION_AUDIT to SYS; -- Note that we create the interval partition on the audit table for ease of maintenance
 * The 11gR2 provides better auditing enhancement via DBMS_AUDIT_MGMT.
 * Audit data goes into SYS.AUD$ table. Space will keep growing if not regularly removed or archived.
 * To setup Audit Archiving on 11gR1 PRDW environment

--(2) -- conn SYS as sysdba create or replace procedure pr_purge_audit_trail (days in number) as

-- Script to Purge the Audit Trail data stored in SYS.AUD$ table (enabled by the init parameter AUDIT_TRAIL. -- Data will be archived to AUDIT_USER.SESSION_AUDIT (identical structure with SYS.AUD$, --      but created with the INTERVAL partition for ease of maintenance) --  -- Adapted from : Oracle 11g — Audit Enabled by Default, But What About Purging? by Alex Gorbachev --   http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/ --

purge_date date; begin purge_date := trunc(sysdate-days); dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||                       purge_date || ' started'); insert into audit_user.SESSION_AUDIT select * from sys.aud$ where ntimestamp# < purge_date; delete from sys.aud$ where ntimestamp# < purge_date; commit; dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||                       purge_date || ' has completed'); end; /

--Then let's schedule a new maintenance job using Job Scheduler. --Oracle 11g includes the default maintenance windows group, MAINTENANCE_WINDOW_GROUP, -- and we will use that to run the purge.

BEGIN sys.dbms_scheduler.create_job(   job_name => 'AUDIT_PURGE',    job_type => 'PLSQL_BLOCK',    job_action => 'begin pr_purge_audit_trail(30); end;',    schedule_name => 'MAINTENANCE_WINDOW_GROUP',    job_class => '"DEFAULT_JOB_CLASS"',    comments => 'Audit Trail Purge',    auto_drop => FALSE,    enabled => TRUE); END; /

-- During the job run you will see the following in the alert.log: (A) AUDIT: Purging Audit Trail until 27-AUG-10 started AUDIT: Purging Audit Trail until 27-AUG-10 has completed

(B) New partition is created automatically with the Interval Partition AUDIT: Purging Audit Trail until 24-SEP-10 started TABLE AUDIT_USER.SESSION_AUDIT: ADDED INTERVAL PARTITION SYS_P61 (1) VALUES LESS THAN (TIMESTAMP' 2010-10-01 00:00:00') AUDIT: Purging Audit Trail until 24-SEP-10 has completed

--(3) -- To quickly view Audit Data -- Less than 30-day data in SYS.AUD$ -- Greater than 30-day data in AUDIT_USER.SESSION_AUDIT SELECT AUD.USERID, AUD.USERHOST, AUD.TERMINAL, AA.NAME Action_Name, AUD.SPARE1 OS_user, TO_CHAR(AUD.NTIMESTAMP#, 'MM/DD/YYYY HH24:MI') Audit_Start, TO_CHAR(AUD.LOGOFF$TIME, 'MM/DD/YYYY HH24:MI') Logoff_Time FROM SYS.AUD$ AUD, SYS.AUDIT_ACTIONS AA WHERE AUD.ACTION# = AA.ACTION ORDER BY NTIMESTAMP# DESC; USERID	USERHOST	TERMINAL	ACTION_NAME	OS_USER	  AUDIT_START	        LOGOFF_TIME 1	SYSTEM	DS\FSKFJSDF-00	FSKFJSDF-00	LOGOFF	       CHRISCCC   09/26/2010 19:40	09/26/2010 19:40 2	SYSTEM	DS\FSKFJSDF-00	FSKFJSDF-00	LOGOFF	       CHRISCCC   09/26/2010 19:40	09/26/2010 19:40 3	SYSTEM	DS\FSKFJSDF-00	FSKFJSDF-00	LOGON	       CHRISCCC   09/26/2010 19:40 4	SYSTEM	DS\FSKFJSDF-00	FSKFJSDF-00	LOGON	       CHRISCCC   09/26/2010 19:40 5	SYSTEM	DS\FSKFJSDF-00	FSKFJSDF-00	LOGON	       CHRISCCC   09/26/2010 19:26 http://www.stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/cfgaudit.htm#1010036
 * Other dictionary views

SELECT * FROM SYS.AUDIT_ACTIONS;
 * View Action