Re: Deadly sins againts database performance/scalability

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u3cbz2rb2.fsf_at_standardandpoors.com>...
> On Thu, 04 Dec 2003, damorgan_at_x.washington.edu wrote:
>
> > The division of DBAs and developers into different departments
> > with different budgets and different managers. Can't think of a
> > better way to NOT create a team atmosphere.
>
> We have this at my place of work. The wall between my database
> team and the DBA team of our parent company was such that I
> wasn't given a lick of access to any of our development database
> machines. I asked for dba access to the development machines
> they wanted us to use and they got all up in arms about security
> and other production concerns. These machines were to be used by
> my team and my team only. They have never once asked my about my
> designs or layouts or anything. The only thing they wanted to
> make sure of was that we were going through their layers to get
> anything done. Here is my answer in a thread that I was involved
> in. The thread I was involved in is probably reconstructed by
> many here from similar pleas for sane viewpoints from the DBA
> crews.
>
>
> To your question about dba access, I will answer from my
> personal viewpoint. (ie, I don't want to talk for our overall
> architect) . I don't understand how the database architect
> for the whole project isn't given full access to all
> databases designated for development purposes for the
> project. These are development instances. They shouldn't be
> locked down as production instances. The only thing that
> should be locked down is the way the app is interacting with
> the database, because we are building a production app. We
> need to be certain that we hamper the application in exactly
> the same way it will be hampered in production. But, we
> don't need to hamper the development process with the same
> restrictions. When a construction crew is building a new
> home, they don't put the locks on the doors and windows and
> make the construction crew knock on the door to get in to do
> their work, but they certainly make sure the home is put
> together, just like it will be when it is sold. Before it is
> put on the market and sold, the security against even the
> guys that built it is then, and only then, put in place.

what privileges do you actually require?

What if the DBA (person) removed all of the privileges from the DBA role except for create session, table, type, view, trigger, sequence, synonym and alter session?

the problem with granting an app_owner schema the role DBA is that then the application is coded depending upon the DBA role (and usually, all of the sys_privs that are in that role, that the account invariably grants itself directly). it is such a PITA to get changes made to remove queries that hit the dba_ views (such as dba_cons_columns for RI errors). If the developers can't code against the dba_% views, but are limited to the all_ views, you don't have as many issues when the code runs on a qa db where the app owner account does not have the DBA role granted to it.

If you are using a single app_owner schema, you don't need any of the ANY privs.
Yes, you need dictionary access in development, so the developer role gets dictionary access.

You only think that you need the DBA role granted to you. You most likely do not.
The more you read about security, the more that you'll agree with this viewpoint (IMHO).

This is a gross oversimplification, I'm sure that you can find exceptions, but I believe it to be generally true.

Give the developers enough privileges to create objects in the own schemas in dev to get their work done, but not enough sys_privs to alter the app_owner schema(s) themselves.

Pd Received on Fri Dec 05 2003 - 22:42:26 CST