We have a buisness requirement to track all activity on a particular table. All selects , inserts,updates and deletes should be captured with userid , timestamp when they have executed and sql user used . After collection we should generate monthly reports on all activites on that table for auditiing purposes.
I have done researsh on db2aduit facility & query patroller both are not suitable for this situation as it does on whole database which will add a overhead, Also tried to use a triggers it only captures DML activities. Is there are any other ways , any suggestions will be appreciated.
I do not know if this will work, but you could try this:
1) use the triggers to log the DML
2) create a stored procedure to log read events
3) create a table UDF that calls the SP and returns the table rows
4) create a view on the UDF
5) revoke select access on the table