Re: ACL with roles.

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Mon, 27 Oct 2008 00:33:20 -0700 (PDT)
Message-ID: <092b8012-9285-4b50-9013-cafdca38025d@m3g2000hsc.googlegroups.com>


On Oct 24, 12:53 pm, "Preston" <dontwant..._at_nowhere.invalid> wrote:
> Vladimir M. Zakharychev wrote:
> > On Oct 23, 7:10 pm, "Preston" <dontwant..._at_nowhere.invalid> wrote:
> > > gazzag wrote:
> > > > On 23 Oct, 15:31, "Preston" <dontwant..._at_nowhere.invalid> wrote:
> > > > > 11.1.0.6.0 on Vista 64 Ultimate.
>
> > > > > I've created a new ACL, assigned a host & added a privilege for
> > > > > a role, and can now do a utl_http.request when logged in as a
> > > > > user with that role. However if I put the utl_http.request in a
> > > > > procedure & try to run that, it fails with ORA-24247: network
> > > > > access denied by access control list (ACL).
>
> > > > > If I add a privilige for the user to the ACL, it works. Anyone
> > > > > know where I'm going wrong?
>
> > > > > --
> > > > > Preston
>
> > > > Privileges to run stored procedures have to be explicitly granted
> > > > to the individual users, not roles.
>
> > > The user owns the procedure so that's not the issue. Or are you
> > > saying that specifically the ACL privilige has to be granted to
> > > individual users to run stored procedures?
>
> > > --
> > > Preston
>
> > AUTHID DEFINER (default) stored procedures are executed in environment
> > equivalent to the one you get after SET ROLE NONE. In other words,
> > roles are disabled for PL/SQL and any privileges granted via roles do
> > not apply unless you created the procedure with AUTHID CURRENT_USER,
> > in which case role privileges do apply (but executing such procedures
> > is a bit more expensive because Oracle has to evaluate the privileges
> > on every call.)
>
> So the ability to add a role with dbms_network_acl_admin.add_privilege
> is completely pointless, & likely to cause much confusion as it will
> never work?
>
> Paging Oracle - sort the docs[1] out will you...
>
> [1]http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_network
> acl_adm.htm#BABHJEHG
>
> --
> Preston

The ability to add a role to the ACL is as pointless as granting any privileges to roles and expecting them to be active in default DEFINER PL/SQL context. This behavior is thoroughly documented and is actually a FAQ. It will work if you implement it properly (create your stored procedures with AUTHID CURRENT_USER, so that security context is evaluated for the caller, not the owner, and roles are active; or grant required privileges directly to the owner of the stored procedure.)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon Oct 27 2008 - 02:33:20 CDT

Original text of this message