Can you use Oracle Database Unified Auditing with Oracle APEX?

A customer asked me this morning:

"Can we monitor the table access from an APEX application?"

This is a security-minded customer, who was also interested in the use of Oracle Database Vault to help protect unauthorized access to personally identifiable information which would be maintained in their database tables.  The only access to these tables would be through their APEX applications (for now), and they wanted to be able to monitor access to these tables.  While Oracle offers a very robust, enterprise solution in this problem domain named Oracle Audit Vault and Database Firewall, auditing via the native database functionality would be sufficient for now.

This blog post is not intended to be a complete treatise on the subject of database auditing in the Oracle Database, there is already a lot of documentation on this topic.  This blog post is a simple proof that auditing can be enabled to track accesses from an APEX application and how to see the audit log entries.

1.   I created a simple APEX application based upon an updated version of the TASKS CSV file, used in this 2015 Oracle Magazine article.  This was done in the JOEL_DB schema.  The Interactive Report on page 1 is based upon the query:
   
   

   select id, project, task_name, start_date, end_date, status, assigned_to, cost, budget
    from tasks
   

   
   The application looked like:
   
   
   
   
   
   
   
2. I created an audit policy named JOEL_TASKS_POLICY and enabled it using the following SQL:
   
   

   create audit policy joel_tasks_policy actions 
       select on joel_db.tasks, 
       update on joel_db.tasks, 
       delete on joel_db.tasks, 
       insert on joel_db.tasks;
   

   
   Note:  This is Oracle Database 12cR2.  Before beginning this step, I ensured that the database was configured for Unified Auditing.
   
   
   
3. I ran the following SQL statement to validate existence of the audit policy:
   

   SQL> 
   
   select audit_option, object_schema, object_name 
     from audit_unified_policies 
    where policy_name = 'JOEL_TASKS_POLICY';
   
   AUDIT_OPTION  OBJECT_SCHEMA  OBJECT_NAME
   DELETE        JOEL_DB        TASKS
   INSERT        JOEL_DB        TASKS
   SELECT        JOEL_DB        TASKS
   UPDATE        JOEL_DB        TASKS
   

   
   
4. I stopped and restarted Oracle REST Data Services (ORDS).  This is important, because the policy will only take effect for database sessions established after the audit policy was enabled.  Since ORDS maintains a pool of database sessions, I needed to nuke the existing sessions and establish new ones.
   
   
5. I ran the page a few times in my APEX application using the Interactive Report.
   
   
6. As a DBA, I queried the database view UNIFIED_AUDIT_TRAIL using the following query:
   

   select dbusername, action_name, event_timestamp, scn, 
          object_schema, object_name, sql_text, sql_binds, client_identifier 
     from unified_audit_trail 
    where unified_audit_policies = 'JOEL_TASKS_POLICY' 
      and object_schema = 'JOEL_DB' 
    order by event_timestamp desc
   

   
   
   
   
   
   and voila!  You can easily see that the SELECT statements against the TASKS table are properly being audited, complete with any bind values used in the query.  The username of the database session is APEX_PUBLIC_USER as expected, because this is what the database sessions in the ORDS pool are connected as.  In the CLIENT_IDENTIFIER column, you can see that the name of the authenticated user to the APEX application along with the APEX session identifier are also recorded.
   
   

Does this give you everything you wish?  Probably not.  I'd like to know the APEX application ID, the page number, the actual parsing schema, and more.  But using the APEX session ID and correlating it with entries in the APEX_ACTIVITY_LOG database view, I can find so much more about this activity.  There are also many other columns in the UNIFIED_AUDIT_TRAIL database view which are used in conjunction with Oracle Database Vault and Real Application Security, and which would capture still more information about access of this underlying database object.

A great benefit of using database auditing instead of simply implementing "poor-man's" logging in the APEX application is that any access will be recorded from any application, not just the APEX app.