Home » RDBMS Server » Security » Database Audit Option
Database Audit Option [message #58980] Mon, 20 October 2003 06:24 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Database Audit Option [message #59003 is a reply to message #58985] Mon, 20 October 2003 23:39 Go to previous message
Velu Natarajan
Messages: 16
Registered: July 2002
Junior Member
Dear ALL,

How to AUDIT database object DROP on specific user.

Thanks!

Velu N
Previous Topic: Password for internal account
Next Topic: checking user name and password
Goto Forum:
  


Current Time: Sat Aug 15 15:35:23 CDT 2020