Ittichai at 14:35, 23 March 2012

2012-03-23T14:35:51Z

Ittichai at 13:51, 23 March 2012

2012-03-23T13:51:14Z

Ittichai at 13:50, 23 March 2012

2012-03-23T13:50:23Z

Show changes

Ittichai at 13:33, 23 March 2012

2012-03-23T13:33:13Z

Ittichai at 16:48, 3 February 2012

2012-02-03T16:48:26Z

Ittichai: Created page with "== 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 follo..."

2012-02-03T16:47:01Z

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..."

New page

== 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 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
</pre>
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.
<pre>
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
</pre>

* 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
<pre>
(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
</pre>

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

* View Action
SELECT * FROM AUDIT_ACTIONS;

@@ Line 537: / Line 537: @@
 |	219	||	ALTER FLASHBACK ARCHIVE
 |-
-|	220	||	DROP FLASHBACK ARCHIVE	|}
+|	220	||	DROP FLASHBACK ARCHIVE
 [[Category:Oracle_11g]]

@@ Line 184: / Line 184: @@
 * View Action
-  SELECT * FROM AUDIT_ACTIONS;
+  SELECT * FROM SYS.AUDIT_ACTIONS;
 [[Category:Oracle_11g]]

← Older revision		Revision as of 16:48, 3 February 2012
Line 185:		Line 185:
	* View Action		* View Action
	SELECT * FROM AUDIT_ACTIONS;		SELECT * FROM AUDIT_ACTIONS;
		+
		+	[[Category:Oracle_11g]]

Automatic audit archive in 11gR1 - Revision history

Ittichai at 14:35, 23 March 2012

Ittichai at 13:51, 23 March 2012

Ittichai at 13:50, 23 March 2012

Ittichai at 13:33, 23 March 2012

Ittichai at 16:48, 3 February 2012

Ittichai: Created page with "== 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 follo..."

@@ Line 17: / Line 17: @@
 * Additional audits can be added.
-Additional audit http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm
+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.