| Audit Question [message #171997] |
Fri, 12 May 2006 19:47  |
mdoakes42
Messages: 5
Registered: May 2006
|
Junior Member |
|
|
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.
|
|
|
|
| Re: Audit Question [message #172274 is a reply to message #171997] |
Mon, 15 May 2006 15:16  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
1.) I need to see the actual SQL text for the select statement and I only want to audit selected users.
I would use triggers to call a pl/sql function.
2.) I also need to be able to automatically add selected new users to the "audit list".
Your 'create user' privilege will have to call a function that rewrites the function used above.
3.) I only need to audit one table and need to record any select against it for certain users.
Again with the trigger. I found Oracle FGA did not work for what I wanted, so I wrote my own. The trick is that you can not allow the functions to become invalid. And it does take a very slight performance hit when you execute the functions. You will not notice it if you keep the function memory resident.
I use a 'before commit' trigger to call audit functions that populate my own audit tables. Then look at those tables once in a while and remove ones after a certain date.
I audit a few sets of tables and record what it looked like before and after the statement executes.
|
|
|
|
| Re: Audit Question [message #172308 is a reply to message #172274] |
Mon, 15 May 2006 23:40 |
amankhan
Messages: 69
Registered: December 2005
Location: Texas
|
Member |
|
|
Hi Mac,
Your procedure seems interesting in auditin tables.
I also want to audit few tables and some columns in those tables.
Help Appreciate . Can you guide in a proper way by exaplinaing the procedure.
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
