It really depends on the application.
I have had some that are that way and some SW deployment applications that
actually required DBA or they wouldn’t work including ones that actually query
the data dictionary to look for them having DBA privilege and if not failing.
Just trying to say that you can fight the battle to a point but there are some
apps that absolutely require it. In those cases I normally separate the app off
into its own world so that it can be controlled by other means.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<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: Sheehan, Jeremy [mailto:JEREMY.SHEEHAN@xxxxxxx] ;
Sent: Monday, July 18, 2016 8:23 AM
To: dimensional.dba@xxxxxxxxxxx; 'j_akins'; debra.scarpelli@xxxxxxx; 'Oracle L'
Subject: RE: DBA granted to app schema
Late to the party with this, but I recently came across this issue with
Hortonworks for a Hadoop repository database. They wanted DBA granted to a
user and I refused. When support came back, they wanted resource and create
view. Hmmmmm…… Another user didn’t have DBA, but had every single sys priv
granted to it that was granted to DBA. I granted resource and create view and
what do you know? No problems!
Jeremy
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Dimensional DBA
Sent: Thursday, July 14, 2016 10:29 AM
To: 'j_akins'; debra.scarpelli@xxxxxxx; 'Oracle L'
Subject: RE: DBA granted to app schema
CAUTION - EXTERNAL EMAIL
Rich asked for scripts to capture the SQL that the app was running as I
understood in order to then use that to install into his database environments
instead of granting them DBA in every environment that they would install into.
This is one of many alternative approaches to granting the DBA privilege on
install, if the SW vendor approves its use. An example where it would not be
supported is say the install of SAP.
In regular environments some form of auditing even on what we as DBAs do is
considered sufficient by Compliance once you can explain how you ensure that
the DBA cannot compromise the audit trail.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<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: j_akins [mailto:j_akins@xxxxxxxxx] ;
Sent: Thursday, July 14, 2016 7:23 AM
To: dimensional.dba@xxxxxxxxxxx; debra.scarpelli@xxxxxxx; 'Oracle L'
Subject: RE: DBA granted to app schema
Just turning on auditing is a poor security stance. I'm pretty sure that will
not pass most industry audits.
Sent from my Sprint Samsung Galaxy S6 edge+.
-------- Original message --------
From: Dimensional DBA <dimensional.dba@xxxxxxxxxxx>
Date: 7/14/16 10:16 AM (GMT-05:00)
To: debra.scarpelli@xxxxxxx, 'Oracle L' <oracle-l@xxxxxxxxxxxxx>
Subject: RE: DBA granted to app schema
There a variety of apps that they simply provide the scripts and you bypass
this but more and more of the java apps it is all encapsulated in the Java JAR
file and if you didn’t install it with the tool as a lot of times there is
metadata that goes along with it.
This is where you have to adapt and navigate the waters however you can. The
real problem is with the apps that have an admin user that expects to be able
to perform work under multiple users without having to login as those users.
Some vendors are willing to work with you and some are not.
It is easiest to just turn on full SQL auditing for a user to capture
everything they do.
Matthew Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Scarpelli, Debra
Sent: Thursday, July 14, 2016 6:40 AM
To: Oracle L
Subject: RE: DBA granted to app schema
I’m working thru this issue with vendor software now. The application
installation requires a schema, and there are scripts to run to pre-create the
schema.. so it seems it should not be necessary to grant DBA privileges. I’ve
decided to put tracing/auditing on to see what the application user is trying
to do when it connects, and maybe this way, can grant just the privileges
needed instead of DBA.
If anyone has done this before and is willing to share their scripts, etc.
please contact me? Or post URL
Thank you.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Rich J
Sent: Thursday, July 14, 2016 9:31 AM
To: Oracle L
Subject: RE: DBA granted to app schema
On 2016/07/14 07:54, Dimensional DBA wrote:
The reasons are many as I explained yesterday. There are a variety of COTS
vendor software that was written to think they own the database world and need
the access through one administrative user to control other users that are a
part of their application in the database. Normally these applications are
purchased by a specific team in the company in a lot of cases other
infrastructure teams before the DBA team evens knows the app exists and there
is nothing that can be done at that point as the vendor is not changing their
app and it has to be implemented.
I'm going through something similar right now, although I was able to talk the
vendor out of the DML "ANY" privs. Instead of installing their schema into our
ERP DB, I have an auxiliary DB that connects to the ERP DB via links. I
created schemas to mirror the ERP DB, then views over the DB links. The vendor
keeps warning of performance problems of the DB links, as though their views
than generate 70-line explain plans aren't the real issue...
Not that this method will work for every vendor's software, but it might be one
alternative.
Rich
