RE: DBA granted to app schema
Date: Thu, 14 Jul 2016 05:54:11 -0700
Message-ID: <002701d1ddce$d225d030$76717090$_at_comcast.net>
As I sated this is where I normally spin up a database on its own VM away from all my other databases so that we can manage security surrounding that app differently.
I normally don’t mind so much the apps that require DBA on install. An example is Oracle Data Integrator. It wants to create 4 different users with their own table spaces and build a lot of objects and perform a variety of grants including system level grants in the metadata database. After the install you revoke the privilege and it doesn’t need it again until potentially an upgrade. There are a variety of products in the IBM and HP space that work this way and in the Devops tools space.
If you have custom apps then you can normally build pl/sql packages that handle the work in a controlled and secure fashion, but when it is a COTS application, you are stuck. DBA privilege can be the least of your worries when it comes to application security and that middleware application that you went to a admin’s desk and entered the password into the screen so the password for the application users were not released into the DEV world and the software allows the password entered to be displayed in another screen in open text (IBM Websphere).
In the larger world today of automation and many companies pushing for all deployments including database deployments to be performed through a tools like chef or puppet and those infrastructures normally having devs have access to the repositories where changes can be implemented to the setups by them. At one company we actually separated infrastructure used puppet and application development used Chef to implement a layer of security separation. The Chef setup had no ability to execute root privs or deploy ssh keys to servers.
The world we live in is not always as controlled as we would like. You have to adapt and achieve a balance, sometimes precarious.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7 Dimensional.dba_at_comcast.net
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn
www.dimensionaldba.com <http://www.dimensionaldba.com/>
From: jack.van.zanen_at_gmail.com [mailto:jack.van.zanen_at_gmail.com] On Behalf Of Jack van Zanen
Sent: Thursday, July 14, 2016 5:34 AM
To: dimensional.dba_at_comcast.net
Cc: mark.powell2_at_hpe.com; oracle-l_at_freelists.org
Subject: Re: DBA granted to app schema
it happens, thank heavens in ORACLE land a lot less than SQL Land but it still rears its ugly head every now and than... I can not see any valid reason why with the enormous amounts of granualr privs we can assign we should be applying blanket god privs
Jack van Zanen
This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
On Thu, Jul 14, 2016 at 12:26 AM, Dimensional DBA <dimensional.dba_at_comcast.net> wrote:
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-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 14 2016 - 14:54:11 CEST