DBAs, roles and privs

Dear all,

I work in a company that employs about 1000 people, as one of four Oracle DBAs. We manage about 50 production databases and about 50 various test/dev databases. Size of the largest one is between 2 and 3 TB.

Recently our management decided to upgrade the company's crucial software system that manages *all* the data needed for a company operation and existance. It uses two databases and a set of applications. Both of the databases generally consist of a single schema with all the application objects and huge number of users with various roles and privileges granted. A group of people called "application administrators" (as well as several processes) connect directly as schema owner; other users (hundreds of them) connect as ordinary users with restricted privileges.

The upgrade was performed by first upgrading the DBs from 81740 to 92040; after that the vendor team 'upgraded' the structure of the schemas, the data and all the client software - everything was ofcourse first done on test copy of production system.

During the test upgrade, vendor asked for SYS (?) access to the database, or DBA role and *all* the system privileges granted to schema that owns the application data.

When we asked for an explanation, there was a conflict between us (DBA team) and our management. Our management considered us as a sort of obstacle in this upgrade process :-) because of declining to perform several such requests. The vendor was not willing/able to give a list of specific grants/privileges needed. As we had no support even from our management, we were forced to grant everything to this schema owner in order to "keep the project going on", with a statement from vendor that this is required only during test upgrade process and will not be needed on a real production system once it starts operating.

Of course, we were forced to grant everything to schema owner during real (production) upgrade, through a lot of hard conflict between us and our management... we were not able to convince them why this is dangerous and not appropriate...

This is now a production system; revoking all those would prevent the system from operating (tried on a test system). Now there is about 20-30 people (most of them almost computer illiterate) and several processes connecting to a schema that has DBA role, all the sys privileges and all the grants to SYS objects :-) This is not the only system 'organized' in such way, some others are too, but they are not so important for the company.

Excuse me for imperfect English and for not signing the post;

Any thoughts appreciated.

Regards... Received on Sun May 16 2004 - 14:13:46 CDT