It is a fairly easy matter to configure Fine-Grained Auditing (FGA) in Oracle, you have to create a policy with the DBMS_FGA package. You can choose which columns to audit and a logical audit condition to determine whether the rows should be audited. This example will log users who query salary data from the JOB record in PeopleSoft.
If the audit condition is matched for any rows in a query, and the query selects one of the audit columns, a single audit row is produced, irrespective of the number of rows that match.
Be aware that if the audit condition raises an error for any inspected row (such as an error caused by coercion), the audited query will also fail. There will be nothing in the message to suggest that it is caused by FGA, and so it can be difficult to diagnose and debug.
I chose to save the audit data to a table in the database, but alternatively, you can output the audit data as an XML data structure in a flat file written by setting:
Oracle writes a file to the directory indicated by the AUDIT_FILE_DEST parameter.
And this is the data the Oracle records in the table.
It tells us is that a process, running as OS user David on node GO-FASTER-4, connected to the database as SYSADM and queried some audited data on table PS_JOB. But this node is the application server, and it will look the same for all access to PeopleSoft via the Application Server or any other program.
We need to collect some more information from the session.
If you use database triggers to audit updates in PeopleSoft, they obtain the PeopleSoft operator ID from the client information string that is set by a call to the DBMS_APPLICATION_INFO package at the start of every service in the application server. They extract the operator ID with a function called GET_PS_OPRID (delivered in getpsoprid.sql).
You can’t put a trigger on the audit table (SYS.FGA_LOG$) because it is owned by SYS, but we could use the error handler to call a custom PL/SQL procedure. The handler module is fired every time an audit record is written. It is intended to permit a notification to be sent on audit to the DBA when a policy is breached. But we could use it for a different purpose. The handler runs in the same session as the process that performs the audited action. So I can collect information about the session and write it to another audit table that I have created.
And this is the information that is inserted into my new logging table
The SCNs do not match between these two audit records. There is a difference of at least 3, but the tables can be joined on SESSIONID and ENTRYID (so you will probably need an index on these columns).
So, if I now when I open a component in PeopleSoft which queries somebody’s compensation rate, the FGA policy generates a record.
I can use the above query to combine the audit data. It this example it shows that operator PS queried salary data for employee
Note that the data was queried from the database when the component was opened, and that is when the audit occurred. The operator would have to navigate to the ‘Compensation’ tab to see the data, so the audit does not absolutely prove the data was displayed to the operator, but I suspect that is only a semantic difference.
The ability to audit is all very well, but it comes at a price. It is not difficult to generate a large number of audit rows. In the following PL/SQL block, an audit row is generated for each time the query inside the loop is executed (2050 times on my HR demo database).
This can have a dramatic effect on performance. I ran the above PL/SQL on my laptop with SQL*Trace.
Other time in Exception Handler
FGA causes a huge increase in the response time of this test. Most of the additional time is spent inserting the audit rows, although the performance of the single disk in my laptop is probably magnifying the effect.
ConclusionI have demonstrated that it is possible to use FGA to monitor who is looking at what in PeopleSoft as well as detecting other forms of break-in. However, it can introduce a significant run-time overhead. The ability to incorporate a custom PL/SQL notification procedure suggests that it is designed with the intention of logging exceptional activities rather than routine one that users are permitted to perform.
While FGA could be used to audit access to sensitive data, I think that row-level security should be setup correctly in PeopleSoft in the first place, so that only duly authorised users can access sensitive data. I suggest that FGA should only be used conservatively to monitor exceptional events.