Re: DBAs, roles and privs

dba wrote:

> 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...

I'm with Frank on this one. Your management team deserves what they get.

The one thing I'd have advised you to do at the time was to write a letter from the DBA team to management explaining the risks, advising that they not do it, and finally asking them to sign it as an acknowledgment that they had been so informed. It is amazing how often asking for ink on paper changes management minds.

That said here's what I'd do now. Create a new role with a name like APP_OWNER that contains all of the DBA privileges and swap the DBA role, Oracle's own advise is that it should NEVER be granted, and give that new role to the application.

Then, each and every week revoke the privileges you think most unlikely to be required and/or most dangerous. When someone complains about something you'll know the privilege was required and since you will know which one's you revoked you can provide a two-second fix. Eventually you will have a role that truly reflects the privs required.

Other things I would do:
1. Write a DDL trigger that makes it impossible to DROP, ALTER, or TRUNCATE any object. Code for this can be found at: http://www.psoug.org/reference/ddl_trigger.html

2. Verify that your backups reflect the system as it is now rather than how it used to be by testing your ability to recover on the test box.

3. Revise your resume and bail out of that mad-house at the first reasonable opportunity unless you are paid by the hour and/or for overtime.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)