Re: DBAs, roles and privs

From: Igor Laletin <ilaletin_at_usa.net>
Date: 17 May 2004 00:12:27 -0700
Message-ID: <f9226414.0405162312.6cf7cdb9@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1084737583.359769_at_yasure>...
> dba wrote:

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

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

[...]
> > 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 :-)

In short: continue to work with your software vendor. All changes must be authorised by both vendor and your management.

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

This is actually a good advice and it's not too late to follow it. No need to insist on ink and paper but at least get a e-mail with an explicit order.  

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

Up to here I agree. First, you should try to separate on-line 'computer illiterate' users from apllication processes connected to the same schema:

- create personal accounts for every apps admin
- give them APP_OWNER
- find out how they manage the application; do they use some sort of

front-end or just sqlplus?
- try to reduce their priviledges (more difficult if they use front-end).

If you revoke priviledges from people and overdo it, you can always fix it. If you revoke from the application processes and batch job fails in the middle of the night, that's another story. So _don't_ touch appl processes.

Your software vendor must provide you with the list of priviledges for appl admins and processes. I would continue to insist on this. At the very least make them stand by their promise not to use SYS access in production.

> Then, each and every week revoke the privileges you think most unlikely
> to be required and/or most dangerous. [...]

I agree with Marc. It's a very dangerous advice. You don't want application processes to fail. If they, say, leave your schemas logically corrupted, you'll be responsible, not software vendor.

The only way is to continue to work with the vendor.

> Other things I would do:
> 1. Write a DDL trigger that makes it impossible to DROP, ALTER, or
> TRUNCATE any object.

[...]
> There is no excuse for DROP, ALTER, or TRUNCATE on a production system
> unless it is performed by the DBA.

Of course application can create/modify/drop its objects. Nothing's wrong with this.

> 3. Revise your resume and bail out of that mad-house at the first
> reasonable opportunity

:) Usually there are more parameters involved into decision to change jobs than just unsupportive manager. I wouldn't rush out, especially considering the current market state.

> unless you are paid by the hour and/or for overtime.

Cheers,
Igor Received on Mon May 17 2004 - 02:12:27 CDT