Home » RDBMS Server » Server Administration » ACL denied using Pkb but works using anonymous block with same user
ACL denied using Pkb but works using anonymous block with same user [message #305560] Tue, 11 March 2008 05:01 Go to next message
humbletech99
Messages: 4
Registered: February 2008
Junior Member
I have a recent 11g installation with a developer that is getting a access denied message when using a package but is able to run the same code manually in sqlplus and it works.

eg. I have granted access to connect and resolve to the user BLAH.

from sqlplus, BLAH can do

select trim(substr(utl_inaddr.get_host_name,1,30)) from dual;
TRIM(SUBSTR(UTL_INADDR.GET_HOST_NAME,1,30))
--------------------------------------------------------------------------------
serverhostname


and get the hostname successfully, but this same line in a pkb fails when called like so

exec func_name.something()
BEGIN func_name.something(); END;

*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1
ORA-06512: at "BLAH.FUNC_NAME", line 83
ORA-06512: at line 1


but if you were to do this same bit of code in sqlplus as follows:

set serveroutput on
declare
  mhost varchar(30);
begin
select trim(substr(utl_inaddr.get_host_name,1,30))  into mhost
   from dual;
dbms_output.put_line(mhost);
end;
/


it works.

I do not understand this at all. This is the same user account that owns the pkb and is running it in sqlplus. The user has been granted the connect and resolve priv through being granted a role that has this permission.

Can anyone help me out here?
Re: ACL denied using Pkb but works using anonymous block with same user [message #305666 is a reply to message #305560] Tue, 11 March 2008 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do not apply with PL/SQL procedures.

You can make it fail in SQL*Plus by 1st doing

SQL> SET ROLE NONE
Re: ACL denied using Pkb but works using anonymous block with same user [message #305912 is a reply to message #305666] Wed, 12 March 2008 05:58 Go to previous message
humbletech99
Messages: 4
Registered: February 2008
Junior Member
thanks, I got around that fact by granting the privs explicitly to the user account and now the pl/sql thingy works as well.
Previous Topic: rollback segments
Next Topic: free space
Goto Forum:
  


Current Time: Sat Dec 03 05:45:21 CST 2016

Total time taken to generate the page: 0.12578 seconds