dcsimg
 

Oracle's Unified Auditing, Part II

Monday Jan 29th 2018 by David Fitzjarrell

It may not be obvious, but some aspects of Unified Auditing only register audit trails for privileged users.  Read on to see which areas won't generate audit trails for conventional users.

Going back to Oracle's UNIFIED AUDIT TRAIL some notes need be made as to which auditing actions are reported to regular users and which are reserved to privileged (SYSDBA) accounts. There are basically two types of policies that can be created: action policies and privilege policies. Action policies are triggered by actions executed by any user against the audited objects. An example of an action policy is shown below:


create audit policy aud_inserts
actions insert on bob.emp,
        update on bob.emp,
        delete on bob.emp;

Such policies can list each action to be monitored on the given table or can be a 'blanket' declaration to audit every action against the given table, as shown below:


create audit policy aud_inserts
actions all on bob.emp,
        all on bob.dept,
        all on bob.salgrade;

Multiple tables can be included in a single policy, which can make monitoring a set of related tables easier as they can all reside in a single auditing policy. Once such a policy is created and audited any listed action performed by any user authorized to access the table or tables is recorded in the audit trail:


SQL> insert into emp select * From emp;

14 rows created.

SQL>
SQL> select event_timestamp, action_name, object_name
  2  from unified_audit_trail
  3  where object_name = 'EMP'
  4  order by event_timestamp;

EVENT_TIMESTAMP               ACTION_NAME               OBJECT_NAME
----------------------------- ------------------------- -----------------------------------
14-JAN-18 10.10.30.199000 AM  INSERT                    EMP
14-JAN-18 10.10.30.213000 AM  SELECT                    EMP


SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> insert into dept select * From dept;

4 rows created.

SQL>
SQL> select event_timestamp, action_name, object_name
  2  from unified_audit_trail
  3  where object_name = 'DEPT'
  4  order by event_timestamp;

EVENT_TIMESTAMP               ACTION_NAME               OBJECT_NAME
----------------------------- ------------------------- -----------------------------------
14-JAN-18 10.10.08.920000 AM  INSERT                    DEPT
14-JAN-18 10.10.08.920000 AM  SELECT                    DEPT


SQL>
SQL> rollback;

Rollback complete.

SQL>

Privilege audits will only register actions by privileged accounts. A privilege audit policy example is shown below:


create audit policy aud_tbl_mods
privileges      create any table, alter any table, drop any table;

The above policy has been created and audited; let's see which accounts get audit records when create table statements are executed:


SQL> --
SQL> -- Create table for audit policy testing
SQL> --
SQL> create table audit_policy_test(
  2  id              number,
  3  stuff           varchar2(20),
  4  morestuff       varchar2(25),
  5  stuff_dt        date);

Table created.

SQL>
SQL> --
SQL> -- Populate the table
SQL> --
SQL> begin
  2          for q in 1..1000 loop
  3                  insert into audit_policy_test(id, stuff, morestuff, stuff_dt)
  4                  values(q, 'Audit '||q, 'More audit '||q, sysdate+q);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select event_timestamp, action_name, object_name, sql_text
  2  from unified_audit_trail
  3  where object_name = 'AUDIT_POLICY_TEST'
  4  and object_schema = 'BING'
  5  and dbusername = 'BING'
  6  and event_timestamp >= trunc(sysdate)
  7  order by event_timestamp;

no rows selected

SQL>

So Unified Auditing falls short when it comes to auditing table creates, alters, and drops when those are executed by the table owner. Let's drop that table and re-create it from a SYSDBA session:


SQL> connect / as sysdba
Connected.
SQL>
SQL> --
SQL> -- Create table for audit policy testing
SQL> --
SQL> create table bing.audit_policy_test(
  2  id              number,
  3  stuff           varchar2(20),
  4  morestuff       varchar2(25),
  5  stuff_dt        date);

Table created.

SQL>
SQL> connect bing/############
Connected.
SQL>
SQL> --
SQL> -- Populate the table
SQL> --
SQL> begin
  2          for q in 1..1000 loop
  3                  insert into audit_policy_test(id, stuff, morestuff, stuff_dt)
  4                  values(q, 'Audit '||q, 'More audit '||q, sysdate+q);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> select event_timestamp, action_name, object_name, sql_text
  2  from unified_audit_trail
  3  where object_name = 'AUDIT_POLICY_TEST'
  4  and object_schema = 'BING'
  5  and event_timestamp >= trunc(sysdate)
  6  order by event_timestamp;

EVENT_TIMESTAMP               ACTION_NAME               OBJECT_NAME
----------------------------- ------------------------- -----------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
14-JAN-18 10.31.33.804000 AM  CREATE TABLE              AUDIT_POLICY_TEST
create table bing.audit_policy_test(
id              number,
stuff           var


SQL>

Even a DBA account won't create audit records for such actions (the previous example, that produced no audit records for the create table and drop table statements, was executed by a DBA-privileged account). This is good information to have as checking for new tables may not produce the expected audit trail if the session creating/dropping those tables isn't using the elevated SYSDBA privileges.

Knowing what will and won't produce audit records for a given account can help tremendously when monitoring that account for activity; auditing new table creations may require a query of DBA_OBJECTS to report any new tables in the database as the Unified Audit Trail won't record such actions for 'normal' user connections. The Unified Audit Trail is a vast improvement over previous audit trail configurations however, as it provides a 'one-stop shop' to provide information on audited objects. Getting around its idiosyncrasies (what few there are) is trivial when one realizes the wealth of auditing data that is available.

See all articles by David Fitzjarrell

Home
Mobile Site | Full Site