Automatic audit archive in 11gR1
Jump to navigation
Jump to search
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 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 | } |