Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted January 29, 2018

WEBINAR:
On-Demand

Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js


Oracle's Unified Auditing, Part II

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM