oracle audit trial - ghdrako/doc_snipets GitHub Wiki

Oracle Database Auditing Types

  • Standard auditing (traditional)
  • Unified Auditing
  • Fine-grained auditing FGA
  • Triggers

Standard Auditing

  • Enabled by setting the parameter AUDIT_TRAIL to DB, OS, or XML
  • When DB audit records are saved in SYS.AUD$
  • To configure the standard auditing option, use the AUDIT SQL statement
  • Standard Auditing Levels:
    • Statement
    • Privilege
    • Object
    • Network obraz

Unified Audit

  • Introduced in 12c to provide a full set of enhanced auditing features
  • Audit trail is captured from multiple sources
  • By default, databases operate in mixed mode
  • You can create named audit policies
  • If possible, use it and do not use the standard audit
  • You still must learn standard audit obraz

Fine-grained Auditing FGA

  • Fine-grained auditing enables you to create policies that define specific conditions (could be content based) that must take place for the audit to occur • Can be linked to one or more columns in a table or view • May execute a procedure when an audit entry is produced • Audits SELECT, INSERT, UPDATE, DELETE, and MERGE • Is administered with the DBMS_FGA package
DBMS_FGA.ADD_POLICY (
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
POLICY_NAME => 'AUDIT_SALES_SALARY',
AUDIT_CONDITION => 'DEPARTMENT_ID=30',
AUDIT_COLUMN => 'SALARY,COMMISSION_PCT',
HANDLER_SCHEMA => 'SECURE',
...

Auditing Using Triggers (Value-Based)

  • Table triggers create an audit row which could contain the new and old values
  • Must be written manually by a DBA or developer
  • Could introduce performance impact
CREATE OR REPLACE TRIGGER sysaudit.hrsalary_audit
AFTER UPDATE OF salary ON hr.employees
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
IF :OLD.SALARY != :NEW.SALARY THEN
INSERT INTO sysaudit.AUDIT_EMPLOYEES
VALUES (SYS_CONTEXT('USERENV','OS_USER'), SYSDATE,
SYS_CONTEXT('USERENV','IP_ADDRESS'),
:NEW.EMPLOYEE_ID || ' SALARY CHANGED FROM '||:OLD.SALARY||
' TO '||:NEW.SALARY);
END IF;
END;

Auditing Modes (Standard and Unified Auditing):

  • Mixed Mode: traditional and unified auditing are supported.
  • Enabled Unified Auditing: pure unified audit mode
  • Standard Auditing only: the unified auditing is disabled

Typy audit trail w Oracle

parametr AUDIT_TRAIL

  • NONE - audyt wylaczony
  • DB - Audyt do tabeli SYS.AUD$ (w bazie danych)
  • DB, EXTENDED - Jak wyżej + pełny tekst zapytania i zmienne bindowane
  • OS - Audyt zapisywany w plikach systemowych
  • XML / XML,EXT - Audyt do pliku XML (w katalogu wskazanym przez AUDIT_FILE_DEST)
  1. Ustawienie - Wymaga restartu bazy danych
ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;
  1. Przeniesienie tabeli AUD$ do dedykowanej przestrzeni
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'AUDIT_TBS'
  );
END;
/
  1. Włączenie audytu
AUDIT SESSION; --  Audyt logowania
AUDIT SELECT, INSERT, UPDATE ON hr.employees BY hr; -- Audyt operacji na konkretnej tabeli
AUDIT ALL ON hr.employees; -- Audyt wszystkich operacji na tabelach

Utrzymanie i czyszczenie audit trail

pakiet DBMS_AUDIT_MGMT

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    default_cleanup_interval => 24);
END;
/

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

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    use_last_arch_timestamp => TRUE);
END;
/

Dobre praktyki:

  • Twórz dedykowany tablespace tylko dla audytu.
  • Regularnie archiwizuj i czyść stare wpisy z AUD$.
  • Audytuj tylko to, co naprawdę istotne – audytowanie wszystkiego spowolni bazę.
  • Monitoruj rozmiar tabeli AUD$ oraz wpływ na wydajność.