Re: Privilege

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_at_hpe.com> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Reen, Elizabeth <dmarc-noreply_at_freelists.org> Sent: Tuesday, March 7, 2017 4:43:31 PM
To: 'dombrooks_at_hotmail.com'; Powell, Mark; andrew.kerber_at_gmail.com Cc: oracle-l_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dominic Brooks Sent: Tuesday, March 07, 2017 2:46 PM
To: Dominic Brooks; Powell, Mark; andrew.kerber_at_gmail.com Cc: oracle-l_at_freelists.org
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_at_gmail.com Cc: oracle-l_at_freelists.org
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_at_hotmail.com; andrew.kerber_at_gmail.com Cc: oracle-l_at_freelists.org
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Andrew Kerber <andrew.kerber_at_gmail.com> Sent: Tuesday, March 7, 2017 2:18:34 PM
To: dombrooks_at_hotmail.com
Cc: oracle-l_at_freelists.org
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_at_hotmail.com> 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.'
--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 08 2017 - 02:10:33 CET