Exposing procedures for URL access with ORDS

I had just been looking at exposing some procedure for URL access, using ORDS.

ORDS offers the following configuration properties:

• security.inclusionList
• security.exclusionList
• security.disableDefaultExclusionList
• security.validationFunctionType
• security.requestValidationFunction

Which you can see on the docs: https://docs.oracle.com/cd/E37099_01/doc.20/e25066/config_file.htm#AELIG7204 

If you look at the administrator guide: https://docs.oracle.com/cd/E37097_01/doc.42/e35129/adm_mg_service_set.htm#AEADM209 it suggests pointing the validation function to: wwv_flow_epg_include_modules.authorize, and modifying the function: wwv_flow_epg_include_mod_local, within the APEX schema to return true or false depending if you want your procedure to be accessible or not. This unwrapped function is effectively called at the end of wwv_flow_epg_include_modules.authorize if none of the apex procedures matched.

If you leave the value of security.requestValidationFunction empty, all procedures will be accessible.

In addition to requestValidationFunction, or in place of, you can make use of the properties security.inclusionList and security.exclusionList. These allow you to specify a comma separated list of procedures that should be included or excluded respectively. Keep in mind, for the inclusionList, if it contains a value, all supported procedures must be specified. So it might make the validation function a little redundant if you need to go specifying the same procedures twice.

The benefit of the inclusion list is it supports wild cards, so removing the validation function, you could have a value such as: 'f, p, z, ws, apex*, htmldb*, apex*, wwv_flow*' which should support most apex related URL procedures. Then you just need to append any schema qualified procedures you want to expose.

..

The other option is making use of RESTful services in SQL Workshop. This has a couple of benefits:

1. You won't have to modify the configuration everytime you want to expose a new procedure
2. You don't need to apply grants to public on your procedures/packages

As an example, I create the following procedure:

create or replace procedure output_name(p_name in varchar2)
as
begin

  htp.p('Hello ' || p_name || '. You have successfully exposed your procedure using RESTful services.' );

end output_named;

Then, through SQL Workshop, I go into RESTful services and create a new service named 'example.com'. I specify a URI prefix of messages/ - this is a completely optional part of the service, and just adds another portion to the request URL after the workspace name.

In the 'Add a Resource Template' section, I specify welcome/{name}. The bit in curly braces just creates a bind variable to be used in the handler source.

Finally, in the 'Add a Resource Handler' section, I specify the method as GET, source type as PL/SQL and specify the source as:

begin
    output_name(:name);
end;

As per the following screenshot:

If you then navigate to the resource handler and use the Test button, you should be able to see it in action.

Basically the format of the URL will be:

/workspace_name/URI_prefix/uri_template/bind_variable

Which in my case, is:

/test1/messages/welcome/bob

These services also have the benefit of being able to associate authentication, to lock them down a bit more. For more information, I suggest checking out the ORDS Developers Guide: http://www.oracle.com/technetwork/developer-tools/rest-data-services/documentation/listener-dev-guide-1979546.html