| Database Audit Option [message #58980] |
Mon, 20 October 2003 06:24  |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Folks,
I am trying to enforce some level of auditing on an Oracle 8.0.5 database. We have around 10 developers and 5 testers currently accessing Production database via backend (Toad). The application users access via the web. I need to audit the backend users. As of now they have unique id's and only access a specific list of tables for DML actions. The backend users can only update/delete/insert on a select list of tables owned by a specific schema owner..Lets call it ApOwner
I would like to audit the INSERT/UPDATE/DELETE actions performed by these backend users on the specified list of tables owned by ApOwner. But I do not want to track chaneged made by ApOwner himself. Just the backend users.
Does this warrant Oracle Auditing or should I do this using custom triggers. Does Oracle audit option allow one to exclude specific users and audit the rest? In my case the ApOwner. If not what are my options?
Thank You
Vinny
|
|
|
|
| Re: Database Audit Option [message #58985 is a reply to message #58980] |
Mon, 20 October 2003 09:11  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
You can AUDIT a class of DML statements by user like
Audit Insert Table,Delete Table,Update table by user1,user2;
but this will audit against all tables for those users.
The thing is schema object audit such as
audit select,insert,update,delete on scott.emp;
cant audit by user. You can audit all users using this.
Fine Grained Auditing could do that probably. For more details , see the documentation
-Thiru
|
|
|
|
| Re: Database Audit Option [message #58986 is a reply to message #58985] |
Mon, 20 October 2003 09:25 |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Thiru,
Thanks for the response. Based on your suggestion, it appears that I am caught between either
1.Auditing actions made by the listed users on all tables
2. Auditing all actions on a specific object regardless of which user.
I am on Oracle 8.0.5. So FGA is not an option. Both 1 and 2 will cause serious performance issue. So correct me if this will work.
1. I will grant insert/update/delete on certain tables to those listed users.
2. Then monitor the above actions for those users.
Since I did not give access to all tables to these users, in essence I am only monitoring actions made by the listed users on the objects to which they have access to do INsert/Upd/DEL.
Will this work? Also this still does not give me before and after image does it?
Thanks
V
|
|
|
|
| Re: Database Audit Option [message #58988 is a reply to message #58986] |
Mon, 20 October 2003 09:37 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Right,if they dont have access, the statements will fail and you can exclude them by WHENEVER SUCCESSFUL clause of the audit statement. IF these are the only tables that those users will access anyway, you could just monitor those users for those statement types.
'Both 1 and 2 will cause serious performance issue'
I dont know. It depends on so many factors. You can test it always..
Before and after image ? Consider Log_Miner,Table versioning using workspace manager,triggers for this purpose.
HTH
-Thiru
|
|
|
|
| Re: Database Audit Option [message #58989 is a reply to message #58988] |
Mon, 20 October 2003 09:53 |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Thanks Thiru,
I think that will be a good start. I will wait for my upgrade to 8i so I can implement robust before and after level detail. At this time, I will be happy with just to track what these users are doing to the tables they have access to.
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
