Oracle’s Unified Auditing, Part II

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

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles