Use of DBA_ROLE by an Application Schema Owner

[dbbyleo]

I'm trying to bounce this off of other DBAs out there to see what your experience has been around this subject.

It's simple. We have a project to deploy a new COTS application. The vendor provides a set of scripts that need to be ran against the database to "install" the application schema. These scripts include being ran by someone with DBA access (me).

Of course I review the script. I come to find that the first script I ran creates the schema owner and grants the schema a lot of privileges. Among those are:

ALTER DATABASE
ALTER SYSTEM
ALTER TABLESPACE
ALTER USER
BECOME USER
GRANT ANY PRIVILEGE
... and so on (these are just the high level ones).

They are just shy of granting DBA role.

After the review I questioned the vendor reps and asked are these privileges necessary. They said yes. They said the app was designed so that it can create additional tablespaces/schema. The application manages certain kinds of "projects" and supposedly when end-users create new projects, the application will create new schema and tablespaces. And apparently, there are various other "functions" within the application that require things like ALTER DATABASE and ALTER SYSTEM.

It just blows my mind how a software development team would have approach their design in this fashion. For instance, instead of creating whole new schema for every "project" in the app, why not just create a new set of tables/objects within the existing schema??

In my modest experience as a DBA, this is the first time I've seen an application behave in such a way.

At the same, the vendor has submitted that most of their clients do not have a formal IT group to support back-end systems, so they've provisioned these functions within the application to allow end-users to do "database administration work" without needed to hire administrators.

Just like in other posts I've seen, the concern is around who is then responsible for a database where administrative access is allowed outside the primary DBA group?

Where you guys can help me is provided any whitepaper out there that goes against the way this application has been designed. The vendor reps admits their approach is outside the norm, but tries to sell it as a "paradigm shift" in application development, which I think is a bunch of bull.

Any feedback would be greatly appreciated. Thanks.

[davey23uk]

it comes own to this, if you want the app - deal with it, you have no choice

[gandolf989]

I boils down to having a division of responsibilities. The DBA's can't change the application code and the application people don't have full control over the database. Start by creating a schema with connect and resource and then do direct grants for things that the application truly needs, and ask if you can turn off the part where the application owner creates schema's and tablespaces.

As DBA you are primarily concerned with keeping the database up, running, accessible, and secure. If an application has the ability to fill the root partition causing the machine to hang, or drop a tablespace with active data, or drop a schema with active data you will be forced to go to your backups, which will create downtime.

IMHO, the application in question sounds like a piece of crap, or at least like the developers were guilty of feature creep.

Best of luck!

[dbbyleo]

Here's the rub... in a big corporation like us, the decision to buy this app came from somewhere long before the app was on my desk. Whatever product evaluation they did obviously didn't cover these aspects of the app. While there may be still time to change course on the decision to go with this app, it is now up to me to present to leadership why I think this app is a piece of crap (from an enterprise administration standpoint). I'd like help to find some documentation (whitepaper, articles, etc) that shares that point of view that developers whole develop application in this fashion should really be shot! Seriously, anything that would provide some collaborative information to what I have to say about it.

At the very least, what I will have to tell them that should they decide to move forward with this app (as is), then the database administration group will limit their responsibility to the database down to simply backup and recovery; they the stake holders of the app will need to maintain SLA/OLA commitments with the vendor, etc.

[PAVB]

... in a big corporation like us, the decision to buy this app came from somewhere long before the app was on my desk.

Resistance is futile.

Install the application then write a nice email to your boss letting him/her know you have successfuly installed the App and list your concerns.