step by step enable audit in xml,extended files in Oracle 11gR2

step by step enable audit in xml,extended files in Oracle 11gR2

Step1: Check the audit parameter setting

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /oracledb/admin/stlbas/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE
SQL>

Step2: Enable audit as xml,extended 

ALTER SYSTEM SET audit_trail=xml SCOPE=SPFILE;

Note: xml- Auditing is enabled, with all audit records stored as XML format OS files.

ALTER SYSTEM SET audit_trail=xml,extended SCOPE=SPFILE;

Note: xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.

Step3: First check the current location of the audit trail tables

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Step4: create a new tablespace to hold the audit trail.

CREATE TABLESPACE TBL_SBL_AUDIT_DAY30 DATAFILE 
  '/oracledb/oradata/stlbas/TBl_SBL_AUDIT_DAY30_01.dbf' SIZE 1500M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


Step5:  move the standard audit trail to the new tablespace.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'TBL_SBL_AUDIT_DAY30');
END;
/

Step6: Check locations.
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Step7: move the fine-grained audit trail.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'TBL_SBL_AUDIT_DAY30');
END;
/

Step8: Check locations of audit tables.

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Step9: Finally, we move them both back to their original location in a single step.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'SYSTEM');
END;
/

---Controlling the Size and Age of the OS Audit Trail

SQL> SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';  2    3

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL

SQL>


These defaults mean that OS and XML audit trail files will grow to a maximum of 10,000Kb, at which point a new file will be created. In addition, files older than 5 days will not be written to any more, even if they are below the maximum file size. Instead, a new file will be created and written to. Here are some examples of changing the settings.

-- Set the Maximum size of OS audit files to 15,000Kb.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
    audit_trail_property_value => 15000);
END;
/

-- Set the Maximum age of XML audit files to 10 days.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
    audit_trail_property_value => 10);
END;
/

The CLEAR_AUDIT_TRAIL_PROPERTY procedure can be used to remove the size and age restrictions, or reset them to the default values. Setting the USE_DEFAULT_VALUES parameter value to FALSE removes the restrictions, while setting it to TRUE returns the restriction to the default value.

-- Reset the max size default values for both OS and XML audit file.
BEGIN
  DBMS_AUDIT_MGMT.clear_audit_trail_property(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
   use_default_values   => TRUE );
END;
/

-- Remove the max age restriction for both OS and XML audit file.
BEGIN
  DBMS_AUDIT_MGMT.clear_audit_trail_property(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   use_default_values   => FALSE );
END;
/

Step10: Purging Audit Trail Records

In this specification:

AUDIT_TRAIL_TYPE: Enter one of the following values:

DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)
DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types.

--Manul Purging--

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   use_last_arch_timestamp => FALSE);
END;
/


--Automated Purging
The following code schedules a purge of all audit trails every 24 hours. The resulting job is visible in the DBA_SCHEDULER_JOBS view.

BEGIN
  DBMS_AUDIT_MGMT.create_purge_job(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24 /* hours */,  
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE);
END;
/

--The job can be disabled and enabled using the SET_PURGE_JOB_STATUS procedure.

BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);

  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

--The interval of the purge job can be altered using the SET_PURGE_JOB_INTERVAL procedure.

BEGIN
  DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_interval_value => 48);
END;
/

If you want the purge job to maintain an audit trail of a specific number of days, the easiest way to accomplish this is to define a job to set the last archive time automatically. The following job resets the last archive time on a daily basis, keeping the last archive time 90 days in the past.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'audit_last_archive_time',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN 
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
END;
/

SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';


Step11: AUDIT Enable for specific user

AUDIT DELETE TABLE, UPDATE TABLE,INSERT TABLE      BY TEST1;

NOAUDIT DELETE TABLE, UPDATE TABLE,INSERT TABLE      BY TEST1;

--******** Note : AUDIT will be effected after logout the seesion by audited user---**********

Step12: AUDIT Enable on specific table

AUDIT INSERT, DELETE, UPDATE ON TEST1.test BY ACCESS  WHENEVER SUCCESSFUL;

NOAUDIT INSERT, DELETE, UPDATE ON TEST1.test   WHENEVER SUCCESSFUL;



--View ALL Audit which are enabled
column USER_NAME format a10
column PROXY_NAME format a10
column AUDIT_OPTION format a20



select * from dba_stmt_audit_opts;

NOAUDIT ALL; - remove all auditing.
noaudit delete table;
noaudit insert table;
noaudit update table;

NOAUDIT INSERT TABLE BY TEST2;
NOAUDIT UPDATE TABLE BY TEST2;
NOAUDIT DELETE TABLE BY TEST2;
NOAUDIT SELECT TABLE BY TEST2;
NOAUDIT EXECUTE PROCEDURE BY TEST2;


NOAUDIT INSERT TABLE BY TEST1;
NOAUDIT UPDATE TABLE BY TEST1;
NOAUDIT DELETE TABLE BY TEST1;
NOAUDIT SESSION by TEST1;

TRUNCATE TABLE SYS.AUD$;  --truncate all audit record

---Miscellaneous command for audit views---

--shows all audited privileges
select * from DBA_PRIV_AUDIT_OPTS

--shows all audited statements
select * from DBA_STMT_AUDIT_OPTS

--shows all audited objects
select * from DBA_OBJ_AUDIT_OPTS 

SELECT * FROM  V$XML_AUDIT_TRAIL order by extended_timestamp desc;

--Ref--

http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php