I have a need to audit all selects on one table in our database by certain users. We are currently running Oracle 9i. I have tried using the audit feature as well as the dbms_fga policy but it doesn't seem like either approach can meet my requirements. The requirements I have are
1.) I need to see the actual SQL text for the select statement and I only want to audit selected users.
2.) I also need to be able to automatically add selected new users to the "audit list".
3.) I only need to audit one table and need to record any select against it for certain users.
The problem I am having is the dbms_fga policy allows me to see the sql text but it records all users in the database. This doesn't work for us because we have thousands of records created for users that we do not need to audit and the audit table grows very rapidly.
Using the normal audit feature doesn't allow me to dynamically add new users to the audit feature. I can upgrade to Oracle 10 to use db_extended so that I can see the sql but if a new user is added they don't get added to the Audit.
Does anyone have any recommendations for how to accomplish my goals.