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.