Oracle 11g R2 APEX 4.2 with the latest listener.
Trying to click on the test button on the Oracle.example.hr sample under the SQL WORKSHOP Restful Services and receive the following error:
Internal Service Error 500
Error during evaluation of resource template: GET hr/employees/, SQL Error Code: 28,150, SQL Error Message: ORA-28150: proxy not authorized to connect as client
I have the APEX_REST_PUBLIC_USER and APEX_LISTENER created from the supplied scripts and have executed the DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE.
We can't get the web services to work.
2 days wasted on this issue; Any help would be greatly appreciated!
The error is saying that for whatever reason APEX_REST_PUBLIC_USER cannot proxy to the SCHEMA (APEX WORKSPACE) where the RESTful Service is defined.
Allow me explain how RESTful Services are invoked in Listener 2.0
- It is important that the RESTful Service is invoked as the SCHEMA user that the RESTful Service is defined in, to make sure the correct database security rules/constraints are applied when executing the query/pl sql block associated with the service.
- The mechanism Listener uses to do this is called connection proxying, which is a facility provided by the Oracle database to switch a connection from one user to another.
- Of course if you could switch from user A to user B without restriction that would not be good. Instead user B must grant user A the privilege to proxy to user B's identity.
- Thus when an APEX workspace first has a RESTful Service created in it, it performs this grant, giving APEX_REST_PUBLIC_USER the privielge to proxy to the workspace/schema where the RESTful Service is defined.
- Then when the RESTful Service definition is being evaluated for execution by the listener it proxies from APEX_REST_PUBLIC_USER to the workspace schema user immediately before executing the query/plsql of the service.
- It looks like the granting of the proxy privilege has not happened/failed for some reason.
Can you tell me:
- If the workspace where the service was defined is a newly created workspace, i.e it was created after installing APEX 4.2?
- If the workspace was created before or after running apex_rest_config.sql?
A manual workaround for your problem would as the SYS user, do:
ALTER USER <apex_workspace_name> GRANT CONNECT THROUGH apex_rest_public_user;
My DBA migrated us from APEX 4.1... to 4.2
Later when I told him I needed to use the Web Services he ran a .sql script to create the apex_rest_public_listener and APEX_LISTENER users.
Once I notified him about the proxy error he tried to make the apex_rest_public_listener proxy through the apex_public_listener which did not work.
I am excited to learn that the apex_rest_public_listener needs to proxy through the schema user. I am a little unclear on which schema, should it be the schema that is my parsing schema for apex or the apex_040200. We tried the proxy as you suggested through the parsing schema and now I get an error page that says:
500 - Internal Server Error
The test url: http://servername.com/apex/ws_name/hr/employees/
I am testing the oracle.example.hr for the GET. I click the test button on the web service page in apex and receive the error.
I will try to find the log files to see if there is something that will indicate the issue.
Once I get it working I will post the issue and solution here for others to read.
I do see 12 connections to my DB as the user apex_rest_public_listener.
Removed everything that DBA and I did and started over:
Steps to use web services:
Go into the administration area of apex and choose enable for the web services select list and save.
Run script to create the apex_rest_public_listener and APEX_LISTENER users.
Create a web service to cause APEX to create the proxy between your apex_rest_public_listener and your schema.