Posted on 2012-09-10
Medium Priority
Last Modified: 2012-11-11
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.
Question by:sam15
  • 2
  • 2
LVL 101

Accepted Solution

mlmcc earned 1000 total points
ID: 38385391
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?

LVL 21

Expert Comment

by:Amitkumar P
ID: 38385488
Check with Fine Grained Auditing feature of oracle.


Hope this will help to you.

Author Comment

ID: 38387200
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.
LVL 21

Assisted Solution

by:Amitkumar P
Amitkumar P earned 1000 total points
ID: 38387459
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.

Author Comment

ID: 38388740
Would FGA work to audit a predefine report query (i.e specific defined SQL).

I doubt it.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Via a live example, show how to take different types of Oracle backups using RMAN.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses
Course of the Month16 days, 23 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question