Defaulting  PL/SQL Gateway Security


Oracle REST Data Services 3.0.7 went out yesterday.  There's an important change that went in to better secure installations by default.  It has always been the case that we recommend customers set the validations for the plsql gateway.  There has always been a validation configuration option to lock down what procedures are accessible which was outlined in this blog post http://krisrice.blogspot.com/2013/01/apex-listener-plsql-validations.html


The change is that starting in this patch when the plsql gateway is enabled AND the db username is APEX_PUBLIC_USER, ORDS setup will add in the configurations setting  security.requestValidationFunction  to be set to the apex security procedure  WWV_FLOW_EPG_INCLUDE_MODULES.AUTHORIZE



Customized PL/SQL Gateway Security

This is just a defaulted settings so if you have a custom procedure just edit the file as normal. Alsoin the blog post that describes how this works there are new features over this basic procedure approach such as binding values like flow_id ( app id ), flow_setup_id ( page id ).  The uses of this are you could have one install of ORDS that is external facing and expose only one set of applications based on the APEX application ID. Then an internal ORDS setup that doesn't limit at all to have intranet applications all hosted out of one production database.

There's also the ability to use javascript for securing the PL/SQL gateway which has been around since version 2.0.  The main benefit with this approach is there doesn't have to be a trip to the database to exec a plsql procedure.  That means ORDS can stop a request before the database is ever called in any capacity.  The variables available for this are outlined in this blog post: http://krisrice.blogspot.com/2012/12/apex-listener-20.html



Here's the note in the README


Important Changes to Note

APEX_PUBLIC_USER & wwv_flow_epg_include_modules.authorize

In line with security best practice and as recommended by the Oracle Application Express Documentation when a database pool is configured to use the APEX_PUBLIC_USER, Oracle REST Data Services will automatically set the value of thesecurity.requestValidationFunction setting to be:
    WWV_FLOW_EPG_INCLUDE_MODULES.AUTHORIZE
This activates the white list of callable procedures which ships with Oracle Application Express and prohibits calls to other procedures. Please consult the Oracle Application Express Documentation for more information about this procedure and how to customize it's behaviour.