Re: ACL with roles.
Date: Mon, 27 Oct 2008 00:33:20 -0700 (PDT)
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:
> > > > > 220.127.116.11.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 out will you...
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.)
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com Received on Mon Oct 27 2008 - 02:33:20 CDT