Sapna Malik
How many types of auditing in Oracle?
By Sapna Malik in Databases & DBA on Apr 19 2011
  • Dinkar Chavhan
    Apr, 2011 21

    Database Auditing

    There are two distinct types of auditing

    • standard - auditing based on statement, privilege and object-level
    • fine-grained - auditing on a finer granule which is based on content i.e. value > 10,000

    Standard

    There are 3 levels of standard auditing

    • statement - audit all actions on any type of object
    • privilege - audit actions that stem from system privileges
    • object-level - specific audit actions like select, update, insert or delete

    For all 3 levels of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session), you can also auditing on if the access was successful (whenever successful) or not (whenever not successful).

    When auditing you need somewhere to log the audit information, this is controlled by the audit_trail parameter which can take the following values

    • none - disables auditing
    • os - records the information to a o/s file (uses parameter audit_file_dest for the file location)
    • db - records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ ) to display audit information.
    • db, extended - as per the db value but also populates the sqlbind and sqltext clob columns
    • xml - audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
    • xml,extended - as per xml option but also populates the sqlbind and sqltext clob columns

    Remember to restart the instance when changing the audit_trail parameter.

    turn off auditing

    alter system set audit_trail=none scope=spfile;

    auditing written to db

    alter system set audit_trail=db scope=spfile;

    auditing written to o/s

    alter system set audit_file_dest='c:\oracle\auditing';
    alter system set audit_trail=os scope=spfile;

    Note: if the audit_file_dest is not set then the default location is $oracle_home/rdbms/audit/

    audit all sys operations (default false)

    alter system set audit_sys_operations=true scope=spfile;

    Note: this will audit all sys operations regardless if audit_trail is set.

    To start auditing you can use the below (there are many more options than stated below)

    session

    audit session by vallep;

    table

    audit table;

    table and specific user

    audit table by vallep;

    table, specific user and access

    audit table by vallep by access;

    privilege auditing

    audit create any table;
    audit create any table by vallep;

    object auditing

    audit select on vallep.employees by access whenever successful;
    audit select on vallep.employees by access whenever not successful;

    disabling audit

    noaudit table;
    noaudit all privileges;
    noaudit create any table by vallep;

    turn off all auditing

    noaudit all;         (turn off all statement auditing)
    noaudit all privileges;     (turn off all privilege auditing)
    noaudit all on default;    (turn off all object auditing)

    purge audit table

    delete from sys.aud$;
    truncate from sys.aud$;

    Useful Views

    DBA_STMT_AUDIT_OPTS

    display any statement auditing

    DBA_PRIV_AUDIT_OPTS

    display any privilege auditing

    DBA_OBJ_AUDIT_OPTS

    display any object auditing

    DBA_AUDIT_TRAIL

    display the captured audit information

    DBA_COMMON_AUDIT_TRAIL

    displays the captured audit information for both standard and FGA

    Auditing via Triggers

    It is possible to audit the system by using triggers, there are a number of system-level triggers that can be fired

    • database startup
    • logon
    • logoff
    • ddl
    • server error

    example trigger auditing

    create or replace trigger audit_insert
    after insert on vallep.employees for each row
    insert into employees_table_audit
    values (user, sysdate);

    create or replace trigger logon_audit_trig
    after logon on database
    begin
       insert into logon_audit values (user, sys_context('userenv', 'sessionid'), sysdate, null, sys_context('userenv', 'host'));
    end;

    ## You would need to create table as below, you would also create a logoff trigger that populated the below table

    create table logon_audit (
    user_id varchar2(30),
    sess_id number(10),
    logon_time date,
    logoff_time date,
    host varchar2(20));

    Fine-Grain Auditing

    Fine-grain auditing (FGA) allows you to audit users accessing data of a certain criteria. As per standard auditing you can audit select, insert, update and delete operations. You use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that you can attach handlers (like a trigger) to the policies which can execute procedures which could email or page you.

    There are many options that can be applied to the dbms_fga package, so best to look up the oracle man pages but here are some simple examples

    Privilege

    grant execute on dbms_fga to vallep;

    Creating

    begin
    dbms_fga.add_policy (
    object_schema=>'vallep',
    object_name=>'employees',
    policy_name=>'compensation_aud',
    audit_column=>'salary,commission_pct',
    enable=>false,
    statement_types=>'select');
    end;
    /

    Creating (handler)

    dbms_fga.add_policy (
    object_schema=>'vallep',
    object_name=>'employees',
    policy_name=>'compensation_aud',
    audit_column=>'salary,commission_pct',
    enable=>false,
    statement_types=>'select'
    handler_schema=>'vallep'
    handler_module=>'log_id');

    create procedure vallep.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2)
    as
    begin
       util_alert_pager(schema1, table1, policy1);  /* send an alert via a pager */
    end;

    Removing

    begin
    dbms_fga.drop_policy (
    object_schema=>'vallep',
    object_name=>'employees',
    policy_name=>'compensation_aud');
    end;
    /

    Enabling

    begin
    dbms_fga.enable_policy (
    object_schema=>'vallep',
    object_name=>'employees',
    policy_name=>'compensation_aud');
    end;
    /

    Disabling

    begin
    dbms_fga.edisable_policy (
    object_schema=>'vallep',
    object_name=>'employees',
    policy_name=>'compensation_aud');
    end;
    /

    Useful Views

    DBA_AUDIT_POLICIES

    identify FGA audit policies

    DBA_FGA_AUDIT_TRAIL

    display the captured audit information

    DBA_COMMON_AUDIT_TRAIL

    displays the captured audit information for both standard and FGA

    SYS.AUD$ table

    Make sure that the sys.aud$ table gets purged from time to time as connections and DML activity in the database might come to a stand still if it becomes full.

    purge audit table

    delete from sys.aud$;
    truncate from sys.aud$;

     

    • 0
  • Sapna Malik
    Apr, 2011 19

    Five types of auditing in Oracle:

    1. Mandatory auditing
    2. Standard database auditing
    3. Value-based auditing
    4. Fine-grained auditing (FGA)
    5. DBA auditing

    • 0


Most Popular Job Functions


MOST LIKED QUESTIONS