OraFAQ Forum: Server Administration » ACL denied using Pkb but works using anonymous block with same user

Home » RDBMS Server » Server Administration » ACL denied using Pkb but works using anonymous block with same user

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ACL denied using Pkb but works using anonymous block with same user [message #305560]

Tue, 11 March 2008 05:01

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?

Report message to a moderator