• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:


I have an oracle table (9i) that stores an application adhoc queries and reports.

The queries are simply the filenames and directory path for script files stored on a shared drive. Reports are crystal report filenames stored at a crystal reports server.

Users acccess those using a web page that displays a link for each file.
When they clicks on a report name, it calls the report server which sends SQL query to database and runs the report.If the URL is for a SQL script, it opens up an ad hoc windows desktop software that allows the user to hit "Run" and it displays the results in that ad hoc user interface screen.

My requirement is to audit usage of these queries and reports in that table.

I wrote a function "f_audit_report" which runs an autonomous transaction that saves an audit record into a table.

The problem is in order for it to run I would need to update every single report and script so it calls  the function as show here:

select a.*, f_audit_report from emp a;

Is there a way to run this from backend using a trigger on SELECT somehow or other mechanism instead of editing and testing hundreds of reports and scripts to call that function?

I am thinking that the server solution (if any) may require to store the SQL used for each report or query in the oracle table so that database can compare it to the incoming SQL and audit that statement only if it matches unless I pass a unique flag with the SQL and database is programmed so it only audits that SQL with that flag set.
  • 2
  • 2
2 Solutions
There is no easy way to do what you want from Crystal.

Reports could be modified to use a command that updates a database.

Can you change the front-end application that the users use to select the report and query so when they hit execute the information is stored then used to run the report?

Amitkumar PSr. ConsultantCommented:
Check with Fine Grained Auditing feature of oracle.


Hope this will help to you.
sam15Author Commented:
I think changing the application would be very similar to adding the audit function call to every SQL in the query or report that needs to be audited. Actually the URL would need to be changed to call some client program that logs the audit record before calling sending the actual URL to report server or SQL query to database.

For fine grain auditing, which I think is the only wayt to audit SELECT in oracle 9i, There might be a couple of issues. First the user are authenticated by application which connects to database using one application account. Capturing the OSUSER and MACHINE NAME might help here identifying the user if can be done. However, I think the database will see the application server for the OSUSER and MACHINE NAME (not the user desktop).

The othe concern is if FGA will support auditing speicifc defined SQL query or report since i am only intereted in specific ad hoc audits.

It sounds there is no easy solution to this other that adding a function call to evey SQL statement used in ad hoc query or report.
Amitkumar PSr. ConsultantCommented:
In this case, you need to enhance the application by adding the auditing feature. I would suggest that auditing feature should not be limited to the reporting queries, but it should also audit the important business events, so that it can be tracked in future.

I've given the idea of FGA considering that solution requires no change in the application.
sam15Author Commented:
Would FGA work to audit a predefine report query (i.e specific defined SQL).

I doubt it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now