Sunday, July 24, 2016

Securing Oracle Application Express when using Oracle REST Data Services (ORDS)

If you are using Oracle REST Data Services as the "PL/SQL Gateway" for Oracle Application Express, ensure that your ORDS configuration includes the following line:

wwv_flow_epg_include_modules.authorize

It is important that you do this, and let me explain why.

Fundamentally, the APEX "engine" is really nothing more than a big PL/SQL program running inside the Oracle Database.  When a browser makes a request for a page in an APEX application, that request is mapped to a PL/SQL procedure which is running inside the database.  If you examine an APEX URL in your browser, you may see something like 'f?p=...', and this is invoking a PL/SQL procedure in the database named 'F' with a parameter named 'P'.

There are a number of procedures in the APEX engine which are intended to be invoked from a URL.  But there may be other procedures in your database, possibly owned by users other than the Application Express user, which are not intended to be called from a URL.  In some cases, these other procedures could leak information or introduce some other class of security issue.  There should be a simple list of procedures which are permitted to be invoked from a URL, and all others should be blocked.  This is known as a "whitelist", and fortunately, there is a native facility in APEX which defines this whitelist.  You just need to tell ORDS about this whitelist.

When you configure ORDS with the following entry in the configuration file:

wwv_flow_epg_include_modules.authorize

You are instructing ORDS to validate the PL/SQL procedure requested in the URL using the PL/SQL function wwv_flow_epg_include_modules.authorize.  This whitelist will contain all of the necessary entry points into the APEX engine, nothing more, nothing less.

If you rely upon functionality in your application which makes use of PL/SQL procedures not defined in this whitelist, this functionality will break when you specify the security.requestValidationFunction.  I often encounter customers who invoke PL/SQL procedures in their application schema to download files, but there are better (and more secure) ways to do this, which would not break when implementing this whitelist.

Like any change to infrastructure or configuration, you should thoroughly test your applications with this setting prior to introducing it into a production environment.  But if one or two things break because of this change, don't use that as an excuse to not implement this configuration change.  Identify the issues and correct them.  While there is a method in place to extend the whitelist, in practice, this should be seldom used.

If you're using ORDS as a mod_plsql replacement for your PL/SQL Web Toolkit application and not using APEX, then please avoid this configuration setting.  APEX typically won't be installed in your database, and the whitelist will be irrelevant for your application.

The function wwv_flow_epg_include_modules.authorize has been around for more than 10 years (our teammate Scott added it in 2005), and it has been a part of the embedded PL/SQL Gateway and mod_plsql default configuration for a long time.  And while it has been documented for use with ORDS, a reasonable person might ask why this isn't simply part of the default configuration of APEX & ORDS.  I did confirm with the ORDS team that this will be included in the default configuration when using the PL/SQL Gateway of ORDS, beginning in ORDS 3.0.7.

5 comments:

Hari said...

Thanks for the tip. Glad to know that this setting will be default from ORDS 3.0.7.

Regards,
Hari

Richard Martens said...
This comment has been removed by the author.
Richard Martens said...

Joel, thanks, we definately should implement this by default.
Is there an option to extend the whitelist?
I remember the Apex Listener 1.x had an option for that.

Unknown said...

Hi Richard,

the package wwv_flow_epg_include_modules actually calls the function wwv_flow_epg_include_mod_local in the APEX schema, this is typically where you would extend the whitelist.

This is also a very old mechanism, see here: http://daust.blogspot.co.uk/2006/04/xe-calling-stored-procedures.html.

The advantage is that this works across all gateways (ords, epg, ohs) in the same way.

I don't like too much that we have to modify a function in the apex schema. In order to avoid that you can always write your own wrapper and call wwv_flow_epg_include_modules.authorize to check for the apex whitelist. If it still returns false you can use your own additional whitelist.

Then you would certainly have to register your own public function in the ords configuration.

Cheers,
~Dietmar.

E. O'Brien said...

Just a warning to folks using security.requestValidationFunction to replace the mod_plsql version by virtually the same name (PLSQLrequestValidationFunction). It does not work the same. First, it is not per database ("DAD") setup. It must be in defaults.xml and applicable to all database connections configured. Second, it is cached whether you like it or not (3.0.8) which means that result from the first hit/user applies until ORDS is restarted. That is fine for a procedure name white list applicable to all, but it is not fine for authorization per user. Finally, if you turn this feature on and have APEX, you are forced to use wwv_flow_epg_include_modules.authorize (i.e. in any wrapper function you implement) or something will not work like the retrieval of workspace images. I would rather have the option to turn on or off the cache (which would seem useful for rest calls too with SSO). I was able to use preProcess instead along with redirection to block unauthorized users for non-APEX applications (i.e. older PL/SQL web toolkit applications).