Re: ACL with roles.

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 23 Oct 2008 09:19:27 -0700 (PDT)
Message-ID: <ead8ff7c-dc6b-4130-8e07-88a54483f0eb@a1g2000hsb.googlegroups.com>


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.)

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Oct 23 2008 - 11:19:27 CDT

Original text of this message