Oracle : Auditing object access in 9i

Created new audit tablespace AUDIT CREATE TABLESPACE "AUDIT" DATAFILE '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_01.dbf' SIZE 4096M REUSE , '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_02.dbf' SIZE 4096M REUSE NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; Moved audit table - nb. original audit table is left in place create table audx tablespace "AUDIT" storage (initial 50k next 50k<br />pctincrease 0) as select * from aud$ where 1 = 2<br />rename AUD$ to AUD$$;<br />rename audx to aud$;<br />create index i_aud2;<br />on aud$(sessionid, ses$tid)<br />tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0)<br /> Turned on audit of all procedural code AUDIT EXECUTE PROCEDURE BY SESSION;<br />-- To remove<br />--NOAUDIT EXECUTE PROCEDURE;<br /> Turned on audit of all table access AUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION;<br />-- To remove<br />--NOAUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION;<br /> Review space usage carefully [ Show » ] Peter Lawes added a comment - 19/Aug/10 10:47 AM Created new audit tablespace AUDIT CREATE TABLESPACE "AUDIT" DATAFILE '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_01.dbf' SIZE 4096M REUSE , '/u03/volnewlivedata02/oradata/LIVE1/AUDIT_02.dbf' SIZE 4096M REUSE NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; Moved audit table - nb. original audit table is left in place create table audx tablespace "AUDIT" storage (initial 50k next 50k pctincrease 0) as select * from aud$ where 1 = 2 rename AUD$ to AUD$$; rename audx to aud$; create index i_aud2; on aud$(sessionid, ses$tid) tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0) Turned on audit of all procedural code AUDIT EXECUTE PROCEDURE BY SESSION; -- To remove --NOAUDIT EXECUTE PROCEDURE; Turned on audit of all table access AUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION; -- To remove --NOAUDIT DELETE TABLE, INSERT TABLE, SELECT TABLE, UPDATE TABLE BY SESSION; Review space usage carefully [ Permlink | Edit | Delete | « Hide ] Peter Lawes added a comment - 19/Aug/10 11:40 AM Audit query with base_data as <br />(<br />select <br />nvl(rtrim(decode(substr(ses_actions, 1,1),'S','ALTER/')<br />||decode(substr(ses_actions, 2,1),'S','AUDIT/')<br />||decode(substr(ses_actions, 3,1),'S','COMMENT/')<br />||decode(substr(ses_actions, 4,1),'S','D/')<br />||decode(substr(ses_actions, 5,1),'S','GRANT/')<br />||decode(substr(ses_actions, 6,1),'S','INDEX/')<br />||decode(substr(ses_actions, 7,1),'S','I/')<br />||decode(substr(ses_actions, 8,1),'S','LOCK/')<br />||decode(substr(ses_actions, 9,1),'S','RENAME/')<br />||decode(substr(ses_actions,10,1),'S','S/')<br />||decode(substr(ses_actions,11,1),'S','U/')<br />||decode(substr(ses_actions,12,1),'S','REFERENCES/')<br />||decode(substr(ses_actions,13,1),'S','X/') <br />,'/'),'?') action<br />,a.*<br />from DBA_AUDIT_OBJECT a<br />)<br />select <br />username,os_username,timestamp,owner,obj_name,<br />b.* <br />from base_data b<br />where 1=1<br />and (owner not in ('SYS','ODL_DBADMIN_DBO','XDB')<br />and username not in ('SYS','DBSNMP')<br />and owner||'.'||obj_name not in ('SYSTEM.SQLPLUS_PRODUCT_PROFILE','SYSTEM.PRODUCT_PRIVS'))<br />or action like '%?%' <br />order by b.timestamp desc<br /> [ Show » ] Peter Lawes added a comment - 19/Aug/10 11:40 AM Audit query with base_data as ( select nvl(rtrim(decode(substr(ses_actions, 1,1),'S','ALTER/') ||decode(substr(ses_actions, 2,1),'S','AUDIT/') ||decode(substr(ses_actions, 3,1),'S','COMMENT/') ||decode(substr(ses_actions, 4,1),'S','D/') ||decode(substr(ses_actions, 5,1),'S','GRANT/') ||decode(substr(ses_actions, 6,1),'S','INDEX/') ||decode(substr(ses_actions, 7,1),'S','I/') ||decode(substr(ses_actions, 8,1),'S','LOCK/') ||decode(substr(ses_actions, 9,1),'S','RENAME/') ||decode(substr(ses_actions,10,1),'S','S/') ||decode(substr(ses_actions,11,1),'S','U/') ||decode(substr(ses_actions,12,1),'S','REFERENCES/') ||decode(substr(ses_actions,13,1),'S','X/') ,'/'),'?') action ,a.* from DBA_AUDIT_OBJECT a ) select username,os_username,timestamp,owner,obj_name, b.* from base_data b where 1=1 and (owner not in ('SYS','ODL_DBADMIN_DBO','XDB') and username not in ('SYS','DBSNMP') and owner||'.'||obj_name not in ('SYSTEM.SQLPLUS_PRODUCT_PROFILE','SYSTEM.PRODUCT_PRIVS')) or action like '%?%' order by b.timestamp desc