Re: ACL with roles.
Date: Fri, 24 Oct 2008 07:19:39 -0700 (PDT)
On Oct 24, 5:53 am, "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:
> > > > > 188.8.131.52.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...
I *think* that what is happening is that, within the context of your
stored procedure, you do not have the role assigned that you think you
That is, you can think of what's going on this way.... 1. user connects to db.
2. User has access to mydefined_acl role (the acl role you've defined to grant access)
3. user calls my_get_htttp_request() stored procedure 4. oracle, behind the scenes, sets your role to NONE within the context of the procedure
5. the sp calls utl_http.request(URL)
6. your ACL is checked --> within the context of the call, you do not have the mydefined_acl role.
7. ORA-24247 is returned.
So, there are several ways to test/fix this, based on your overall security requirements. The easiest way is to define your my_get_http_request() procedure with invoker's rights (that is create procedure my_get_http_request AUTHID CURRENT_USER as....). Received on Fri Oct 24 2008 - 09:19:39 CDT