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 audits can be added.

Additional audit http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm

• To configure AUDIT_TRAIL if it was changed or disabled.

SYS AS SYSDBA> show parameter audit_trail

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

• The 11gR2 provides better auditing enhancement via DBMS_AUDIT_MGMT.

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

• 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

(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

--(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	

• Other dictionary views

http://www.stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/cfgaudit.htm#1010036

• View Action

SELECT * FROM AUDIT_ACTIONS;