Posted on 2012-09-10
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
    LVL 100

    Accepted Solution

    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 20

    Expert Comment

    by:Amitkumar Panchal
    Check with Fine Grained Auditing feature of oracle.

    Hope this will help to you.

    Author Comment

    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 20

    Assisted Solution

    by:Amitkumar Panchal
    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

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

    I doubt it.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
    The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now