Difference between revisions of "Automatic audit archive in 11gR1"
Jump to navigation
Jump to search
(Created page with "== Set up automatic audit archive == * Oracle audits the AUDIT ROLE SQL statement by default (when AUDIT_TRAIL=DB). <pre> The privileges that are audited by default are as follo...") |
|||
Line 185: | Line 185: | ||
* View Action | * View Action | ||
SELECT * FROM AUDIT_ACTIONS; | SELECT * FROM AUDIT_ACTIONS; | ||
+ | |||
+ | [[Category:Oracle_11g]] |
Revision as of 11:48, 3 February 2012
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;