Absolutely, grants are part of the build and nobody should be doing anything
manually, least of all developers.
However I think Dom's point is to do with the arbitrary prohibition of all
direct grants including DBMS packages not granted to public by default, with
hacks that reduce security being proposed as workarounds in the name of
security. You find yourself explaining basic Oracle concepts to the data
security manager. I am seeing this kind of thing more and more, probably
because IT architects are less and less interested in actual data management.
On 7 Mar 2017, at 22:17, Powell, Mark <mark.powell2@xxxxxxx> wrote:
We provide a create table skeleton with a grant section that lists the major
roles and we do not allow the developers to perform DDL operation in System
Test so if the grant is missing the problem should be found in test.
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Reen, Elizabeth <dmarc-noreply@xxxxxxxxxxxxx>
Sent: Tuesday, March 7, 2017 4:43:31 PM
To: 'dombrooks@xxxxxxxxxxx'; Powell, Mark; andrew.kerber@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Privilege
If the code will not work without a direct grant and there is no
other way to do it, we will grant it directly to the FID (Functional ID). Only
the app and support team have logins into the database here. As to having a
schema account and a login account, that means that the developers have to
remember to make the grants. That means they have to understand what a grant
is. Let’s just put it this way, they don’t get it.
Liz
Large Finacial Institution DBA
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Dominic Brooks
Sent: Tuesday, March 07, 2017 2:46 PM
To: Dominic Brooks; Powell, Mark; andrew.kerber@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Privilege
Let me cite the docs:
http://docs.oracle.com/database/121/DBSEG/dr_ir.htm
The privileges of the procedure's defined must be granted directly to the
procedure owner, not granted through roles. These are called definer's rights.
Sent from my Windows Phone
From: Dominic Brooks
Sent: 07/03/2017 19:40
To: Powell, Mark; andrew.kerber@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Privilege
Yep, yep,yep. All missing the point.
The fact is that I'm not allowed an application schema (the one that owns the
application code that I'm writing) with that privilege.
So my application code cannot use Dbms_parallel_execute because the dbas sat
that the code owning schema cannot have Create job.
Not the first time, not the first place.... But I'm starting to see why this
madness is widespread...
Sent from my Windows Phone
From: Powell, Mark
Sent: 07/03/2017 19:31
To: dombrooks@xxxxxxxxxxx; andrew.kerber@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Privilege
I will add my opinion that applications should never run as the application
owner but rather should run using an application user ID that only has DML
privileges to the application tables and execute on stored code granted via a
role. This way if the ID is compromised it cannot be used to change the object
structures or change stored code for the hacker's usage.
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Andrew Kerber <andrew.kerber@xxxxxxxxx>
Sent: Tuesday, March 7, 2017 2:18:34 PM
To: dombrooks@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Privilege
Well, it is what Oracle recommends. It is also much easier to manage
privileges for entire class of users at once, rather than deal with the
privileges individually. For example, when someone changes a job at a company,
its a lot easier to revoke a developer role and grant her a manager role that
has the appropriate privileges, rather than figure out what privileges
specifically need to be revoked and granted.
On Tue, Mar 7, 2017 at 1:01 PM, Dominic Brooks <dombrooks@xxxxxxxxxxx> wrote:
Can someone explain to me why security policies in multiple global banks (no
significance other than they really should know what they are doing) should
advocate not granting privileges to application schemas directly only via roles?
In what way is this less/more secure?
All this seems to mean is that there is a bunch of stuff that either i can't do
in plsql that I could do via dynamic sql or from sql issued by java application
or that I just can't do (e.g. Dbms parallel execute without create job)?
Sent from my Windows Phone
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
