Re: Deadly sins againts database performance/scalability

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<uu14eyocr.fsf_at_standardandpoors.com>...
> On 5 Dec 2003, drak0nian_at_yahoo.com wrote:
>
> > 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.
>
> You are missing my point. I never want the user that the
> application will log in as to have anything but the priviledges
> that will be granted to it in public. What I want is a user that
> has dba priviledges (or a form thereof) that can be used by me
> and the development crew for the sole purpose of modifying the
> database for developing the app. I, most definitely, want to
> hamper the application schema exactly as I plan to in production.

oh.
please allow me to take a step back for a second.

we now have 3 user types, so let us assume that roles are in use for simplicity.

you have an app_owner schema role.
you have an application user role (unprivileged). you have a dba role (although not necessarily the role named "DBA" that is pre-built by dbca).

you want an account that has some sort of dba role that can alter database properties, and have access to the init.ora or spfile.

your existing dba is unavailable, overworked, not competent, uninterested or just plain has no clue and nothing to offer in terms of background, experience, etc.

I can see you wanting to be able to make changes to the database config so as to help optimize how the app code runs. you want the project to succeed.

I could also see where an existing dba (group) might not want you tweaking parameters without justification, this sounds not so much like a communal development environment, but a play area for you to hone your dba skills. personal workstation and laptops are a great place to experiment. If you have to support a horde of developers with this dev db, it might not be the best place for you to develop/hone your dba skills.

one possible approach is to get say 10% of your time allocated to the dba group, and gain their respect, work with them to optimize your development environment.

another possible approach is to completely break from having any involvement from your dbas employed by your company. you will likely lose out on assistance that they could provide.

most likely, you do not want a development crew making database changes en masse.
if anything, give them alter session so that they can muck with things as far as their session is concerned, but not muck with other user's sessions, or instance-wide settings.

I'd prefer if you not use the term "public" but substitute the term "production".
to me, "PUBLIC" is something that is granted (improperly?) where security is of absolutely no concern - dual, perhaps - where anyone that has a session, has read/execute on the object - or worse (think dba_tab_privs).

I am honestly trying to be constructive here, and might have as much to gain from these thought experiments as you.

Pd Received on Sat Dec 06 2003 - 23:42:33 CST