Difference between revisions of "Auditing"

Revision as of 12:30, 7 January 2016

• Master note on Oracle Auditing
• Oracle Database Security Guide 12c
• Oracle-Base 11gR2 Auditing enhancements
• Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt and dbms_scheduled_jobs

Has your database migrated to Unified Auditing?

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

Show the audit trail (12c)

set lines 2000
col dbusername      for a32
col event_timestamp for a23
col sql_text        for a200

select dbusername
,      event_timestamp
,      sql_text
from   unified_audit_trail

Disable unified auditing

select policy_name, enabled_opt from audit_unified_enabled_policies
/

POLICY_NAME                    ENABLED_
------------------------------ --------
ORA_SECURECONFIG               BY

noaudit policy ORA_SECURECONFIG;

Noaudit succeeded.

12c auditing is protected

The Unified Audit Trail is well protected. Not even SYS can mess with it.

select table_name from dba_tables where owner='AUDSYS';

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
CLI_SWP$4f81f886$1$1


SYS@RMANV12> truncate table audsys."CLI_SWP$4f81f886$1$1";
truncate table audsys."CLI_SWP$4f81f886$1$1"
                      *
ERROR at line 1:
ORA-55941: DML and DDL operations are not allowed on table "AUDSYS"."CLI_SWP$4f81f886$1$1"

SYS@RMANV12> drop user audsys cascade;
drop user audsys cascade
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped

Manage the audit trail (12c)

The only way to work with the audit trail is with the DBMS_AUDIT_MGMT package.
Before the audit trail can be purged, you must initialise it. This is a one-off execution of the init_cleanup procedure.

SYS@RMANV12> BEGIN
    DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24 /* hours */);
END;
/

PL/SQL procedure successfully completed.

Set auditing properties

exec dbms_audit_mgmt.set_audit_trail_property ( audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std
                                              , audit_trail_property       => dbms_audit_mgmt.db_delete_batch_size
                                              , audit_trail_property_value => 1000
                                              );

Check whether audit trail cleanup has been initialised

SET SERVEROUTPUT ON
BEGIN
    IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
        DBMS_OUTPUT.put_line('YES');
    ELSE
        DBMS_OUTPUT.put_line('NOT YET...');
        DBMS_AUDIT_MGMT.init_cleanup(
        audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        default_cleanup_interval => 24 /* hours */);
        DBMS_OUTPUT.put_line('YES!');
    END IF;
END;
/

Check audit settings

SELECT * FROM dba_audit_mgmt_config_params;

When was audit trail last purged?

COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;

no rows selected

Set a date to purge to

Setup to purge anything older than 90 days before today

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-90);
END;
/

Recheck

COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 08-NOV-14 06.24.44.000000 PM +00:00

Run the audit cleanup

begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type        => dbms_audit_mgmt.audit_trail_aud_std
                                  , use_last_arch_timestamp => true
                                  );
end;

or

begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type        => dbms_audit_mgmt.audit_trail_unified
                                  , use_last_arch_timestamp => true
                                  );
end;

Which tablespace is the standard auditing table (AUD$) going to?

select string_value from dam_config_param$ where audit_trail_type# = 1 and param_id = 22;

or

select table_name, tablespace_name from dba_tables where table_name IN ('AUD$', 'FGA_LOG$') order by table_name;

Move the standard audit trail (AUD$) to a different tablespace

Can move both the standard and the fine-grained access (fga) audit trail in one go by specifying "audit_trail_db_std" as audit_trail_type.

begin
    dbms_audit_mgmt.set_audit_trail_location ( audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std
                                             , audit_trail_location_value => 'AUDIT_DT'
                                             );
end;
/

Move the unified audit trail to a different tablespace

begin
    dbms_audit_mgmt.set_audit_trail_location ( audit_trail_type           => dbms_audit_mgmt.audit_trail_unified
                                             , audit_trail_location_value => 'AUDIT_DT'
                                             );
end;
/

AUD$ has not been cleaned up and is now too big to work with

Trim down the audit table the old-fashioned way...

#!/usr/bin/ksh



sqlplus / as sysdba <<'EOSQL'

whenever sqlerror exit failure

create tablespace audit_dt_temp
    datafile '/oracle/${ORACLE_SID}/oradata5/audit_dt_temp.dbf'
    size 500M
    autoextend on
    maxsize 30G
/

create table aud2$
    tablespace audit_dt_temp
    parallel (degree 4)
    nologging
as
select *
from   aud$
where  ntimestamp# > sysdate - 90
/


rename aud$ to aud_old
/

rename aud2$ to aud$
/


drop tablespace audit_dt including contents and datafiles
/

create tablespace audit_dt
    datafile '/oracle/${ORACLE_SID}/oradata1/audit_dt.dbf'
    size 500M
    autoextend on
    maxsize 30G
/


alter table aud$ enable row movement
/

alter table aud$ tablespace audit_dt
/

create index i_aud
on     aud$ (sessionid, ses$tid)
       tablespace audit_dt
/

alter table aud$ move tablespace audit_dt
    lob (sqltext) store as lobsegment1 (tablespace audit_dt)
    lob (sqlbind) store as lobsegment2 (tablespace audit_dt)
/


drop tablespace audit_dt_temp including contents and datafiles
/

EOSQL