Pros vs Cons of 'With Admin Option'

Okay gang, I need some advice.

I am DBA'ing a system that currently has about 5 developers. As part of the initial setup, I created a pseudo-admin user called ADM, and through the use of roles, gave the ADM account the ability to create other users and grant permissions to those users, such as create table, create sequence, etc.

Up to this point it has worked out well. With the ADM account having the 'ADMIN OPTION' for creating tables, etc, the developers have been able to create users, as well as 'schema-owner IDs'. However, they are now requesting that the ADM user be given 'CREATE ANY xxx' privileges 'WITH ADMIN OPTION', and I'm torn about this. In my opinion, giving ANY non-DBA user ID the ability to CREATE ANY xxx in ANY other users's schema creates a convoluted environment.

Their arguments consist of 2 points:

(1) having a "super" user allows them to develop in any schema at any
time without having to switch user IDs. It makes their script creation and running simpler. My reply to that is that it only takes 1/2 second to issue a connect user/pass statement to develop in a different schema.

(2) they want to be able to audit what developer is doing what, and
when. Good point here. I know we can enable auditing at the database level, but if the developers are connecting as the schema-owner to do their work, I don't know of any way to correlate this back to an external user. SYS.AUD$ contains a username and userhost field, and even if that can somehow be tied to V$SESSION to pick up on the username and hostname of the PC connecting to the database, that doesn't do me any good if DeveloperB sits down at DeveloperA's PC and logs on.

Need your advice folks....how have you handled similar requests / situations ? On the one hand I understand their desire to be able to audit the development work, but on the other hand, shouldn't they trust each other? And I still see the granting of CREATE ANY to a non-DBA user ID as opening the door to polluting the environment.

Thoughts / comments readily welcomed ....

Thanks.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 06 1999 - 14:54:40 CDT