Difference between revisions of "Auditing"

Latest revision as of 13:18, 12 April 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

See the latest activity on the database[edit]

select username
,      userhost
,      count(*) num_connections
,      max(timestamp)
from   dba_audit_trail
where  username not in ('OPS$ORACLE','OPS$ORAIBM')
/

Has your database migrated to Unified Auditing?[edit]

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

Show the audit trail (12c)[edit]

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[edit]

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[edit]

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)[edit]

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[edit]

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[edit]

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[edit]

SELECT * FROM dba_audit_mgmt_config_params;

When was audit trail last purged?[edit]

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[edit]

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[edit]

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[edit]

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;

Audit Inserts on a particular table[edit]

select owner
,      segment_name
,      segment_type
,      bytes/1024/1024/1024
from   dba_segments
where  1=1
and    segment_name = 'QUERY_RESULT'
/

OWNER                          SEGMENT_NAME                                                                      SEGMENT_TYPE       BYTES/1024/1024/1024
------------------------------ --------------------------------------------------------------------------------- ------------------ --------------------
ENDUR_PRD1                     QUERY_RESULT                                                                      TABLE                        .702148438


select count(1)
from   QUERY_RESULT
/

  COUNT(1)
----------
  26413733

1 row selected.


OWNER                          TABLE_NAME                     Fragmented size                Actual size                    Space reclaimable                          reclaimable space %
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------------------ --------------------
ENDUR_PRD1                     QUERY_RESULT                   .7GB                           .29GB                          .41GB                                                48.5714286



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

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           AUDIT_DT
FGA_LOG$                       SYSTEM


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

dbms_fga.add_policy ( object_schema     => 'ENDUR_PRD1'
                    , object_name       => 'QUERY_RESULT'
                    , policy_name       => 'QUERY_RESULT_POLICY'
                    , audit_condition   => NULL
                    , audit_column_opts => DBMS_FGA.ALL_COLUMNS
                    , audit_trail       => DBMS_FGA.DB_EXTENDED
                    , statement_types   => 'INSERT, UPDATE'
                    );

select DB_USER
,      OBJECT_SCHEMA
,      OBJECT_NAME
,      POLICY_NAME
,      SQL_TEXT
from   dba_fga_audit_trail
/

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

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[edit]

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.

create tablespace audit_dt datafile '/oracle/SID/oradata2/audit_dt01.dbf' size 100M autoextend on maxsize 30000M
/

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[edit]

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[edit]

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$ move 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