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.
- 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:
-
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.
-
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
-
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.
-
I ran the page a few times in my APEX application using the Interactive Report.
-
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.
