you have Oracle XE installed along with Apex version 2.1 or above and you are trying to execute a stored procedure directly from the URL (exploiting the HTTP binding performed automatically by the embedded PL/SQL gateway of XE, similar to mod_plsql) like:
http://127.0.0.1/apex/schema.testor
http://127.0.0.1/apex/testif using a public synonym.
When you submit your request, the browser comes up with a similar screen:
Forbidden
The requested operation is not allowed
When i first hit this error message i contacted John Scott who quickly mentioned the name of the function i had to look at:
wwv_flow_epg_include_mod_local
With this function name i searched the web and i stumbled immediately on Dietmar Aust's blog entry, where he clearly explains how to fix the problem and puts a link to the Oracle documentation dealing with the subject.
Thanks Dietmar!
PS: please note that if you misspell the procedure name or if you forget to grant execute privilege to ANONYMOUS (see in the comments section) or to PUBLIC or you are trying to call the procedure name without the schema prefix and without a public synonym, you won't get HTTP 403 error (forbidden) but HTTP 404 (page not found), so if you get HTTP 403 it means that the procedure was actually found but did not pass the wwv_flow_epg_include_mod_local filter.
12 comments:
Hi,
Do you know how to do this if you don't use XE but a normal APEX with Apache and mod_plsql eg. not using the XML DB HTTP server?
Regards Pete
Do you mean that you would like to implement a restriction on the "authorized" procedures or do you mean that you cannot invoke a stored procedure on a normal Apex installation?
In the latter case i'm invoking stored procedures all the time without any authorization problem whatsoever, the URL syntax is exactly the same in both cases (XE and not XE).
Did i understand your question correctly?
Yes I could not invoke a stored procedure via the URL in APEX. But after prefixing the procedure with the schema owner and granting execute on my procedure to apex_public_user it now works.
Thanks :-)
Excellent, Peter.
The schema prefix can be avoided if you create a public synonym for your procedure.
In the case of XE one must be careful as to the way the procedure is invoked (with or without prefix, packaged procedure or stand-alone) because the wwv_flow_epg_include_mod_local function uses an IN condition and you may have to handle all the possible cases to avoid troubles or perhaps "prune" the unwanted prefixes. Also, I've just found out that in XE is not sufficient to use the schema prefix if the procedure has been granted to APEX_PUBLIC_USER, i had to grant execute to PUBLIC instead to make it work. I'm investigating the problem right now...
Bye,
Flavio
I forgot one final word...
på gensyn Peter!
So, it turns out that on Apex the anonymous web user is called "ANONYMOUS" not "APEX_PUBLIC_USER", so if one doesn't want to grant execute to PUBLIC, one can grant execute to ANONYMOUS and then it will be possible to invoke the procedure using the schema prefix.
Bye,
Flavio
APEX_PUBLIC_USER is used by APEX. ANONYMOUS is only used when you use the HTTP server from XML DB with APEX. (http://download.oracle.com/docs/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm#BABGIBBA)
På gensyn :-)
Pete
Great! It solved my problem.
Thanks a lot.
G.
Hi,
I just tried your sample but no luck.
already modified the wwv_flow_epg_include_mod_local.
already granted an execute to PUBLIC and ANONYMOUS and APEX_PUBLIC_USER (just to make sure)
And still getting error 403-Forbidden.
Im using apex 4.01 and Oracle 10g.
Any help is highly appreciated.
Hi,
could you please double check that the statement "return false" that appears at the beginning of the wwv_flow_epg_include_mod_local function has been commented out?
It happened a couple of times to overlook that detail and spending 10 minutes scratching my head about why it was not working.
If that statement is already commented out, could you please post the content of the wwv_flow_epg_include_mod_local function?
Flavio
Hi,
Thanks for the reply.
I just found out that wwv_flow_epg_include_mod_local function is both exist in FLOWS_020100 and apex_040000 users.
And what Im trying to edit is the one in FLOWS_020100 (as mentioned on this blog http://daust.blogspot.com/2006/04/xe-calling-stored-procedures.html),
which does not work.
After editing the one inside the apex_040000 schema, it went perfectly :)
Thanks a lot.
Well, that's exactly the problem I described in another post, when Apex is upgraded, the content of wwv_flow_epg_include_mod_local is not carried over from the old apex schema.
I suggest you to drop the old schema which is also taking useless disk space and may generate confusion, as you have now personally experienced :-)
Cheers
Flavio
Post a Comment