Gunther Oracle APEX und Datenbank Wiki

Aufgabe:

• In einer Oracle 12c R2 Datenbank sollen mit dem Oracle Board Mitteln die Audit Logs der DB nach einer gewissen Zeit auch gelöscht werden.

Lösung:

• Interne Löschroutinen über DBMS_AUDIT_MGMT aufsetzen.

Ablauf (alles als SYS User):

1. Mit DBMS_AUDIT_MGMT.INIT_CLEANUP initialisieren
2. Zeitpunkt vor dem alles gelöscht werden soll setzen mit DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
   1. Für eine Window Funktion diesen Zeitraum täglich neu setzen, als Job mit DBMS_SCHEDULER.CREATE_JOB
3. Job zum löschen anlegen mit „DBMS_AUDIT_MGMT.CREATE_PURGE_JOB“

Bzgl. RAC siehe auch „ How to create a purge job using DBMS_AUDIT_MGMT for RAC instances. (Doc ID 2261246.1)“.

Wie vielen Einträge haben wir schon in der AUD$?

SELECT username
     , action_name
     , COUNT(*) AS entries
     , to_char(MIN(TIMESTAMP),'dd.mm.yyyy hh24:mi:ss') AS first_log
     , to_char(MAX(TIMESTAMP),'dd.mm.yyyy hh24:mi:ss') AS last_log
 FROM dba_audit_object
GROUP BY username,action_name
ORDER BY 1
/

Alle Details siehe ⇒ https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/audit.sql

Einschalten mit:

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

Falls ein „ORA-46267“ auftritt siehe Support Node ⇒ DBMS_AUDIT_MGMT.INIT_CLEANUP Fails With ORA-46267 (Doc ID 1508787.1)

BEGIN
-- Standard database audit records in the SYS.AUD$ table
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
       audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
     , last_archive_time => SYSTIMESTAMP-180);
 
--  Unified audit trail. In unified auditing, all audit records are written to the unified audit trail and are made --  available through the unified audit trail views, such as UNIFIED_AUDIT_TRAIL.
 DBMS_AUDIT_MGMT.set_last_archive_timestamp(
       audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
     , last_archive_time => SYSTIMESTAMP-180);
 
-- Operating system audit trail. This refers to the audit records stored in operating system files.
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
       audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS
     , last_archive_time => SYSTIMESTAMP-180);
 
END;
/

Mit den Parameter AUDIT_TRAIL_ALL erhalten ich den Fehler „ORA-46250: Ungültiger Wert für Argument „AUDIT_TRAIL_TYPE“ , daher für alle drei Optionen seperat gesetzt.

Zeitpunkt abfragen über:

COLUMN RAC_INSTANCE    format 999 heading "RAC|Inst"  
COLUMN audit_trail     FORMAT A20 heading "Audit|Trail"
COLUMN last_archive_ts FORMAT A40 heading "Last Archive|TS"
 
SELECT RAC_INSTANCE
     , AUDIT_TRAIL
     , LAST_ARCHIVE_TS
 FROM dba_audit_mgmt_last_arch_ts
ORDER BY 1,2
/

Job anlegen:

BEGIN
 
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'AUDIT_ARCHIVE_BEFORE_TIMESTAMP',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'begin 
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
       audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
     , last_archive_time => SYSTIMESTAMP-180);
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
       audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
     , last_archive_time => SYSTIMESTAMP-180);
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
       audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS
     , last_archive_time => SYSTIMESTAMP-180);
   end;',
     start_date      => sysdate,
     repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
     enabled         =>  TRUE,
     comments        => 'Set the point in time before delete all audit log entries'
  );
END;
/
 
-- testen mit:
EXEC dbms_scheduler.run_job('AUDIT_ARCHIVE_BEFORE_TIMESTAMP');

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     => 'CLEANUP_AUDIT_TRAIL_ALL'
              , use_last_arch_timestamp    => TRUE);
END;
/

Job Info abfragen über DBA_AUDIT_MGMT_CLEANUP_JOBS:

COLUMN JOB_NAME                      format a24 heading "JOB|NAME"
COLUMN JOB_STATUS                    format a10 heading "JOB|STATUS"
COLUMN AUDIT_TRAIL                   format a20 heading "AUDIT|TRAIL"
COLUMN JOB_FREQUENCY                 format a30 heading "JOB|FREQUENCY"
COLUMN USE_LAST_ARCHIVE_TIMESTAMP    format a10 heading "LAST | TIMESTAMP" 
COLUMN JOB_CONTAINER                 format a20 heading "JOB|CONTAINER"
 
SELECT JOB_NAME
	, JOB_STATUS
	, AUDIT_TRAIL
	, JOB_FREQUENCY
	, USE_LAST_ARCHIVE_TIMESTAMP
	, JOB_CONTAINER
 FROM DBA_AUDIT_MGMT_CLEANUP_JOBS
/

Wann werden nun aber die Jobs gestartet:

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

Ist der Default Tabelspace für die AUD$ Tabelle NICHT SYSAUX, tritt der folgende Fehler auf beim Aufruf von DBMS_AUDIT_MGMT.init_cleanup auf:

ORA-46267: Nicht ausreichender Platz IN 'SYSAUX' Tablespace, Vorgang kann nicht abgeschlossen werden
ORA-06512: IN "SYS.DBMS_AUDIT_MGMT", Zeile 2544
ORA-06512: IN "SYS.DBMS_AUDIT_MGMT", Zeile 181
ORA-06512: IN "SYS.DBMS_AUDIT_MGMT", Zeile 2330
ORA-06512: IN "SYS.DBMS_AUDIT_MGMT", Zeile 655
ORA-06512: IN Zeile 2

Lösung: Tabelle SYS.dam_config_param$ wie unter Support Node „DBMS_AUDIT_MGMT.INIT_CLEANUP Fails With ORA-46267 (Doc ID 1508787.1)“ beschreiben, manipulieren.

-- Job löschen
 
BEGIN
  DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
      AUDIT_TRAIL_PURGE_NAME  => 'CLEANUP_AUDIT_TRAIL_ALL');
END;
/
 
 
BEGIN
   DBMS_SCHEDULER.DROP_JOB(
         job_name       =>     'AUDIT_ARCHIVE_BEFORE_TIMESTAMP'
     ,   force               => TRUE
   );
END;
/
 
 
BEGIN
  DBMS_AUDIT_MGMT.deinit_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/

Oracle:

• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_AUDIT_MGMT.html#GUID-C704D6B0-A6ED-4CFC-B364-CC008CFF76F1

Web:

• https://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/