Restricting Access to Oracle Database XE

Previous

Next

---

The embedded PL/SQL gateway supports a directive called request-validation-function which enables you to name a PL/SQL function which the embedded PL/SQL gateway will call for each HTTP request. You can use this functionality to restrict the procedures that can be invoked through the embedded PL/SQL gateway. The function returns true if the named procedure in the current request is allowed and false if it is not allowed. You can use this function to enforce access restrictions for Oracle Database XE on a per-database access descriptor (DAD) basis.

Oracle Application Express ships with a request-validation-function named wwv_flow_epg_include_modules.authorize. This function specifies access restrictions appropriate for the standard DAD configured for Oracle Application Express. During installation, scripts also name this function in the request-validation-function directive in the XDB configuration file.

During installation, the installer also creates a PL/SQL function in the Oracle Application Express product schema (FLOWS_xxxxxx). You can change and recompile this function in order to restrict access. The source code for this function is not wrapped and can be found in the database administrators product core directory in the file named wwv_flow_epg_include_local.sql. The source code is as follows:

CREATE OR REPLACE FUNCTION 
wwv_flow_epg_include_mod_local(
    PROCEDURE_NAME IN VARCHAR2)
RETURN BOOLEAN
IS  
BEGIN  
    RETURN FALSE; -- remove this statement when  
you add procedure names to the "IN" list
    IF UPPER(procedure_name) IN (
          '') THEN  
        RETURN TRUE;  
    ELSE  
        RETURN FALSE;  
    END IF;  
END wwv_flow_epg_include_mod_local;
/

To add names of procedures that should be allowed:

1. Remove or comment out the RETURN FALSE statement that immediately follows the BEGIN statement:

   ...
   BEGIN  
       RETURN FALSE; -- remove this statement when 
   you add procedure names to the "IN" list
   ...
   
   

2. Add names to the clause representing procedure names that should be allowed to be invoked in HTTP requests. For example to allow procedures PROC1 and PROC2 the IN list you would write IN ('PROC1', 'PROC2').

After changing the source code of this function, alter the Oracle Application Express product schema (FLOWS_xxxxxx) and compile the function in that schema.

To alter the product schema, FLOWS_xxxxxx :

1. Log in to SQL Command Line (SQL*Plus) as SYS or SYSTEM.

2. Alter the product schema (FLOWS_xxxxxx) by entering the following command:

   ALTER SESSION SET CURRENT_SCHEMA FLOWS_xxxxxx; 
   
   

3. Compile the function wwv_flow_epg_include_local.sql.

The wwv_flow_epg_include_mod_local function is called by Oracle Application Express's request-validation-function which itself is called by the embedded PL/SQL gateway. The Oracle Application Express function first evaluates the request and based on the procedure name, approves it, rejects it, or passes it to the local function, wwv_flow_epg_include_mod_local, which can evaluate the request using its own rules.

When you create new DADs for use with Oracle Application Express through the embedded PL/SQL gateway, the request-validation-function directive should be specified exactly as it is for the DAD created when you install Oracle Database XE, that is, the function wwv_flow_epg_include_modules.authorize should be named in the directive.

If you have no additional restrictions beyond those implemented in the wwv_flow_epg_include_modules.authorize function, there is no need to take any action with respect to the source code for the wwv_flow_epg_include_mod_local function.