sam15
asked on
Auditiing_System_Reports.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Would FGA work to audit a predefine report query (i.e specific defined SQL).
I doubt it.
I doubt it.
http://www.dba-oracle.com/security/fine_grained_auditing.htm
Hope this will help to you.