RE: DBA granted to app schema
Date: Wed, 13 Jul 2016 07:26:45 -0700
Message-ID: <010b01d1dd12$95a4a440$c0edecc0$_at_comcast.net>
There is a variety of reasons why this happens or believed to be required.
1. On initial installation of software the software is not delivered as a
scripts to run against the database but you have to use their installation
to perform the install, (ie ODI for the master repository database IBM MDM).
The rights can be removed after the install, but is sometimes forgotten as
the install takes a couple weeks because the Devs are not trained on the SW.
However there are some applications though that continue this behavior under
normally operating conditions instead of just when you do patches and
upgrades. Normally software had roots in the SQL Server side of the world
and the application user was simply granted control of other databases
(users) within the SQL Server. I had one software package once that we had
to put in its own database instead of using the Schema as a Service database
fleet because of these requirements.
2. Similar designs for COTS applications where an admin user is required to control application structures under another because of the exact setup that Mark speaks of.
3. Requirements of ops for troubleshooting and fixing problems from applications that create and drop objects within a app schema and have to be cleaned up. This is really a problem in organizations that have custom apps and a strong Devops world where management doesn't want the DBA called to fix on-going problems that they believe the Devops should be responsible for. Normally the Devops teams like to write code in Ruby on Rails and want a single login for their fixer app.
4. Poorly designed DW apps that create new temporary objects under one user and build views under another user and want to use their one application login to perform all operations.
And the list goes on.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba_at_comcast.net
View Matthew Parker's profile on LinkedIn
www.dimensionaldba.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Powell, Mark
Sent: Wednesday, July 13, 2016 6:04 AM
To: oracle-l_at_freelists.org
Subject: Re: DBA granted to app schema
I am also strongly of the opinion that applications usernames should not be granted DBA privilege but rather should be granted only those privileges necessary. In fact I prefer that home grown applications run as a username that is not the owner of the target objects but gains access privileges via a role.
Third party vendor products often come with the object owner granted DBA and then run the application as the owning user. Poor security design.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of De DBA <dedba_at_tpg.com.au>
Sent: Wednesday, July 13, 2016 7:55:44 AM To: oracle-l_at_freelists.org
Subject: Re: DBA granted to app schema
I tend to attribute this to MS (SQL Server) style programming. The app owns the database, therefore is dbo, Many duhvullpers that I meet don't even realise that you can have other users in a SQL Server database.
Also, the pressure to deliver yesterday, with no database knowledge to speak of, may lead some to just require "run as administrator" (everyone works in Windhoze these days) in stead of determining exactly which permissions are required.. lazy... perhaps. Eazy certainly.
In my experience there is no valid case. DBA should be limited to as few individuals as possible, which excludes applications outright.
Cheers,
Tony
On 13/07/16 21:42, rob_at_oraclewizard.com<mailto:rob_at_oraclewizard.com> wrote: I still see DBA rights being granted to applications. So before I get up there and make a blanket statement - "if you do this, you are just being lazy, stupid and a few other adjectives." Can anyone think of a use case where granting DBA to an application is valid? I have been racking my brain and can't come up with any use case where that is acceptable.
-Rob
Robert P. Lockard Oracle ACE
Winner of the 2015 Oracle Developers Choice Award for Database Design
President Oraclewizard.com, Inc.
The question is not "who is going to let me," it's "who is going to stop
me." Ayn Rand
(cell) 571.276.4790
(office) 410.766.6960
(fax) 410.766.0332
twitter _at_navonpilot
youtube https://www.youtube.com/user/n4281k
blog: http://www.oraclewizard.com
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 13 2016 - 16:26:45 CEST