Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Setting up ACL to call webservice from PL/SQL

User_AUH4YFeb 17 2021

I am attempting to set up an ACL in order to make restful POST. I have run the below code but continue to keep getting a 01031. 00000 - "insufficient privileges" message when I run the utl_http.request.

grant execute on utl_http to DBO;
grant execute on dbms_lock to DBO;

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'test_6.xml',
description => 'A test of the ACL functionality',
principal => 'DBO',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
end;

begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test_6.xml',
host => 'us-central1-recruiting-297022.cloudfunctions.net',
lower_port => 1522,
upper_port => NULL);
end;

Comments

Jason_(A_Non)

Well for starters, the ORA-01031: insufficient privileges has nothing to do with what utl_http is trying to do and more so with your attempt to call it. We would need to see your code that calls utl_http and how you call that code. Also your version of Oracle is always helpful.

User_AUH4Y

I am working in 19c.

The code I running is
select utl_http.request ('http://us-central-recruiting-297022.cloudfunctions.net') from dual;

Jason_(A_Non)

First, the ACL approach was deprecated with version 12c.
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_NETWORK_ACL_ADMIN.html#GUID-FC9FCE6D-A11C-412A-BE76-1C943EF53B66
Oracle wants you to use the access control entry (ACE) approach.
Tear down the ACL setup you started. You can create the ACE later after you get an error message "ORA-24247: network access denied by access control list (ACL)" I'm assuming you are using sql*plus so show us the output of

show user;
select utl_http.request ('http://us-central-recruiting-297022.cloudfunctions.net') from dual;
User_AUH4Y

I removed the ACL I setup and ran the code you posted and received this message.
USER is "DBO"
ORA-01031: insufficient privileges
ORA-06512: at "SYS.UTL_HTTP", line 1810
ORA-06512: at "SYS.UTL_HTTP", line 136
ORA-06512: at "SYS.UTL_HTTP", line 1745
ORA-06512: at line 1
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges

Jason_(A_Non)

I should have also asked for the output of this
select grantee, privilege from dba_tab_privs where table_name = 'UTL_HTTP'
In my 19.10 DB, public still has EXECUTE on utl_http by default, hence the reason I haven't been able to get your 01031 error.

User_AUH4Y

PUBLIC EXECUTE
ADMIN EXECUTE
DBO EXECUTE
APEX_200200 EXECUTE

Jason_(A_Non)

Well, because PUBLIC has EXECUTE on utl_http, you don't need to grant DBO EXECUTE privs so you can remove that grant. Beyond that, I'm not really sure what is happening internally to throw an "insufficient privileges" error. It's not the one you should be getting because of missing ACL/ACE access. I did some poking around on Duck Duck Go, Google, and Metalink and didn't see anything that stood out as being the same. Hopefully someone else here will have some suggestions for you.

1 - 7

Post Details

Added on Feb 17 2021
7 comments
3,882 views