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://docs.oracle.com/cd/E11882_01/server.112/e26088/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 SYS.AUDIT_ACTIONS;

ACTION	NAME
0	UNKNOWN
1	CREATE TABLE
2	INSERT
3	SELECT
4	CREATE CLUSTER
5	ALTER CLUSTER
6	UPDATE
7	DELETE
8	DROP CLUSTER
9	CREATE INDEX
10	DROP INDEX
11	ALTER INDEX
12	DROP TABLE
13	CREATE SEQUENCE
14	ALTER SEQUENCE
15	ALTER TABLE
16	DROP SEQUENCE
17	GRANT OBJECT
18	REVOKE OBJECT
19	CREATE SYNONYM
20	DROP SYNONYM
21	CREATE VIEW
22	DROP VIEW
23	VALIDATE INDEX
24	CREATE PROCEDURE
25	ALTER PROCEDURE
26	LOCK
27	NO-OP
28	RENAME
29	COMMENT
30	AUDIT OBJECT
31	NOAUDIT OBJECT
32	CREATE DATABASE LINK
33	DROP DATABASE LINK
34	CREATE DATABASE
35	ALTER DATABASE
36	CREATE ROLLBACK SEG
37	ALTER ROLLBACK SEG
38	DROP ROLLBACK SEG
39	CREATE TABLESPACE
40	ALTER TABLESPACE
41	DROP TABLESPACE
42	ALTER SESSION
43	ALTER USER
44	COMMIT
45	ROLLBACK
46	SAVEPOINT
47	PL/SQL EXECUTE
48	SET TRANSACTION
49	ALTER SYSTEM
50	EXPLAIN
51	CREATE USER
52	CREATE ROLE
53	DROP USER
54	DROP ROLE
55	SET ROLE
56	CREATE SCHEMA
57	CREATE CONTROL FILE
59	CREATE TRIGGER
60	ALTER TRIGGER
61	DROP TRIGGER
62	ANALYZE TABLE
63	ANALYZE INDEX
64	ANALYZE CLUSTER
65	CREATE PROFILE
66	DROP PROFILE
67	ALTER PROFILE
68	DROP PROCEDURE
70	ALTER RESOURCE COST
71	CREATE MATERIALIZED VIEW LOG
72	ALTER MATERIALIZED VIEW LOG
73	DROP MATERIALIZED VIEW LOG
74	CREATE MATERIALIZED VIEW
75	ALTER MATERIALIZED VIEW
76	DROP MATERIALIZED VIEW
77	CREATE TYPE
78	DROP TYPE
79	ALTER ROLE
80	ALTER TYPE
81	CREATE TYPE BODY
82	ALTER TYPE BODY
83	DROP TYPE BODY
84	DROP LIBRARY
85	TRUNCATE TABLE
86	TRUNCATE CLUSTER
91	CREATE FUNCTION
92	ALTER FUNCTION
93	DROP FUNCTION
94	CREATE PACKAGE
95	ALTER PACKAGE
96	DROP PACKAGE
97	CREATE PACKAGE BODY
98	ALTER PACKAGE BODY
99	DROP PACKAGE BODY
100	LOGON
101	LOGOFF
102	LOGOFF BY CLEANUP
103	SESSION REC
104	SYSTEM AUDIT
105	SYSTEM NOAUDIT
106	AUDIT DEFAULT
107	NOAUDIT DEFAULT
108	SYSTEM GRANT
109	SYSTEM REVOKE
110	CREATE PUBLIC SYNONYM
111	DROP PUBLIC SYNONYM
112	CREATE PUBLIC DATABASE LINK
113	DROP PUBLIC DATABASE LINK
114	GRANT ROLE
115	REVOKE ROLE
116	EXECUTE PROCEDURE
117	USER COMMENT
118	ENABLE TRIGGER
119	DISABLE TRIGGER
120	ENABLE ALL TRIGGERS
121	DISABLE ALL TRIGGERS
122	NETWORK ERROR
123	EXECUTE TYPE
128	FLASHBACK
129	CREATE SESSION
130	ALTER MINING MODEL
131	SELECT MINING MODEL
133	CREATE MINING MODEL
134	ALTER PUBLIC SYNONYM
157	CREATE DIRECTORY
158	DROP DIRECTORY
159	CREATE LIBRARY
160	CREATE JAVA
161	ALTER JAVA
162	DROP JAVA
163	CREATE OPERATOR
164	CREATE INDEXTYPE
165	DROP INDEXTYPE
166	ALTER INDEXTYPE
167	DROP OPERATOR
168	ASSOCIATE STATISTICS
169	DISASSOCIATE STATISTICS
170	CALL METHOD
171	CREATE SUMMARY
172	ALTER SUMMARY
173	DROP SUMMARY
174	CREATE DIMENSION
175	ALTER DIMENSION
176	DROP DIMENSION
177	CREATE CONTEXT
178	DROP CONTEXT
179	ALTER OUTLINE
180	CREATE OUTLINE
181	DROP OUTLINE
182	UPDATE INDEXES
183	ALTER OPERATOR
192	ALTER SYNONYM
197	PURGE USER_RECYCLEBIN
198	PURGE DBA_RECYCLEBIN
199	PURGE TABLESPACE
200	PURGE TABLE
201	PURGE INDEX
202	UNDROP OBJECT
204	FLASHBACK DATABASE
205	FLASHBACK TABLE
206	CREATE RESTORE POINT
207	DROP RESTORE POINT
208	PROXY AUTHENTICATION ONLY
209	DECLARE REWRITE EQUIVALENCE
210	ALTER REWRITE EQUIVALENCE
211	DROP REWRITE EQUIVALENCE
212	CREATE EDITION
213	ALTER EDITION
214	DROP EDITION
215	DROP ASSEMBLY
216	CREATE ASSEMBLY
217	ALTER ASSEMBLY
218	CREATE FLASHBACK ARCHIVE
219	ALTER FLASHBACK ARCHIVE
220	DROP FLASHBACK ARCHIVE