PL/SQL Gateway

This chapter demonstrates how a mod_plsql application is migrated to Oracle REST Data Services (ORDS).

Oracle REST Data Services is a Java EE-based alternative for Oracle HTTP Server and mod_plsql. An Oracle HTTP Server mod_plsql application can be migrated to ORDS by defining new ORDS configuration files. The mod_plsql database resources such as before procedures, after procedures, request validation functions, owa_custom packages, doc upload procedures and doc tables require no change when you are migrating to ORDS.

Oracle HTTP Server mod_plsql Authentication

Oracle HTTP Server mod_plsql applications are configured in a database access descriptor (DAD) file.

The following example mod_plsql application provides the methods to authenticate the requests against the Oracle Database:

Example Oracle HTTP Server DAD file

This section provides an example Oracle HTTP Server DAD file.

The following dads.conf file includes three locations demonstrating the basic, basic dynamic and custom authentications and the following directives:

Example 18-1 dads.conf file

# ============================================================================
#                     mod_plsql DAD Configuration File
# ============================================================================
<Location /pls/basic_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseUsername             PRIVILEGED_USER
  PlsqlDatabasePassword             passwordF0R$0RD5Example
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           Basic
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
  PlsqlDocumentTablename            privileged_user.doc_table
  PlsqlDocumentProcedure            privileged_user.upload
</Location>
<Location /pls/basic_dynamic_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           Basic
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
</location>
<Location /pls/custom_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseUsername             PRIVILEGED_USER
  PlsqlDatabasePassword             passwordF0R$0RD5Example
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           CustomOwa
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
</location>

Mapping mod_plsql Settings to ORDS

This section shows the mappings of mod_plsql settings to ORDS.

ORDS allows you to specify configuration files that are similar to a location defined in an Oracle HTTP Server mod_plsql DAD file. Each configuration file is defined in its own directory under ords_conf/databases. The URL mapping for each configuration is specified in a paths file (no extension) located within the corresponding configuration directory (that is, ords_conf/databases/<config-directory>/paths).

ORDS provides the following configurable parameters that can be used when migrating mod_plsql directives:

Table 1 Mappings of mod_plsql Directives to ORDS Settings

mod_plsql Setting ORDS Setting Description
PlsqlDatabaseUserName db.username

Specifies the username to use to log in to the database.

ORDS and mod_plsql are equivalent.
PlsqlDatabasePassword db.password

Specifies the password to use to log in to the database.

ORDS and mod_plsql are equivalent.
PlsqlDatabaseConnectString

Multiple Settings such as:

  • db.hostname

  • db.port

  • db.servicename

  • db.sid

Specifies the connection to an Oracle database.

ORDS and mod_plsql are equivalent.
PlsqlAuthenticationMode security.requestAuthenticationFunction

Specifies the authentication mode to use to allow access.

When security.requestAuthenticationFunction is not specified, ORDS behavior is same as Basic mode of mod_plsql.

When security.requestAuthenticationFunction is specified, ORDS can perform the same action as example dad directive PlsqlAuthenticationMode CustomOwaof mod_plsql.

Example ORDS equivalent configuration parameter:

privileged_user.owa_custom.authorize

ORDS and mod_plsql are equivalent.
PlsqlBeforeProcedure procedure.preProcess

Specifies the procedure to be invoked before calling the requested procedure.

ORDS and mod_plsql are equivalent.
PlsqlAfterProcedure procedure.postProcess

Specifies the procedure to be invoked after calling the requested procedure.

ORDS and mod_plsql are equivalent.
PlsqlRequestValidationFunction security.requestValidationFunction

Specifies an application-defined PL/SQL function that can allow or disallow further processing of the requested procedure.

ORDS and mod_plsql are equivalent.
PlsqlDocumentTablename owa.docTable

Specifies the table in the database to which all documents are uploaded.

ORDS and mod_plsql are equivalent.

PlsqlDocumentProcedure N/A

Specifies the procedure to call when a document download is initiated.

In ORDS the document procedure is the requested resource. It is not defined in the configuration file.

ORDS and mod_plsql are equivalent.
PlsqlDocumentPath N/A ORDS has no equivalent.
PlsqlDefaultPage misc.defaultPage

Specifies the default procedure to call if none is specified in the URL.

ORDS and mod_plsql are equivalent.
PlsqlErrorStyle debug.printDebugToScreen

Specifies the error reporting mode for mod_plsql errors.

debug.printDebugToScreen is equivalent to PlsqlErrorStyle DebugStyle, otherwise there is no equivalent.

ORDS and mod_plsql are equivalent.
PlsqlExclusionList security.exclusionList {::nomarkdown}<p>Specifies a pattern for procedures, packages, or schema names which are forbidden to be directly run from a browser.</p><p>ORDS and mod_plsql are equivalent.</p><p>See <a href=”/pls/topic/lookup?ctx=en/database/oracle/oracle-rest-data-services/
26.3/orddg&id=AELIG-GUID-37AA1468-DCB3-4D8B-868C-1910A0C04D68”>Understanding Configurable Parameters</a>. </p> {:/}    
PlsqlIdleSessionCleanupInterval jdbc.InactivityTimeout

Specifies the time (in minutes) in which the idle database sessions should be closed and cleaned.

Value can be 0 to N seconds. Where, 0 (default) means that the idle connections are not removed from pool.

ORDS and mod_plsql are equivalent.
PlsqlMaxRequestsPerSession jdbc.MaxConnectionReuseCount

Specifies the maximum number of requests a pooled database connection should service before it is closed and re-opened.

Default value is 1000.

ORDS and mod_plsql are equivalent.
PlsqlInfoLogging N/A See Understanding Configurable Parameters.
PlsqlLogDirectory N/A See Understanding Configurable Parameters.
PlsqlLogEnable N/A See Understanding Configurable Parameters.
PlsqlSessionStateManagement N/A <p>Specifies how package and session state should be cleaned up at the end of each request.</p>ORDS always performs: dbms_session.modify_package_state(dbms_session.reinitialize) at the end of each request.
PlsqlAlwaysDescribeProcedure N/A <p>Specifies whether the mod_plsql application should describe a procedure before trying to run it.</p>ORDS always describes procedure on first access, and then the definition is cached. Changes in signature are detected and recached.
PlsqlConnectionValidation N/A

Specifies the mechanism the mod_plsql module should use to detect terminated connections in its connection pool.

ORDS always validates connections on borrow.
PlsqlFetchBufferSize N/A

Specifies the number of rows of content to fetch from the database for each trip, using either owa_util.get_page or owa_util.get_page_raw.

ORDS materializes results as a 32K VARCHAR or CLOB if results are greater than 32K, so not applicable.
PlsqlNLSLanguage N/A

Specifies the NLS_LANG variable.

ORDS, Java, and JDBC use unicode.
PlsqlTransferMode N/A <p>PlsqlTransferMode specifies the transfer mode for data from the database back to the mod_plsql application. </p>ORDS always uses unicode.
PlsqlBindBucketLengths N/A

Specifies the rounding size to use while binding the number of elements in a collection bind.

Rarely used in mod_plsql, and JDBC has no equivalent concept.
PlsqlBindBucketWidths N/A

Specifies the rounding size to use while binding the number of elements in a collection bind.

Rarely used in mod_plsql and JDBC has no equivalent concept.
PlsqlCacheCleanupTime N/A ORDS has no equivalent.
PlsqlDMSEnable N/A ORDS does not support DMS.
PlsqlSessionCookieName N/A ORDS does not offer session management for PL/SQL Gateway calls.
PlsqlCacheDirectory N/A ORDS has no equivalent.
PlsqlCacheEnable N/A ORDS has no equivalent.
PlsqlCacheMaxAge N/A ORDS has no equivalent.
PlsqlCacheMaxSize N/A ORDS has no equivalent.
PlsqlCacheTotalSize N/A ORDS has no equivalent.
PlsqlCGIEnvironmentList N/A ORDS has no equivalent.
PlsqlConnectionTimeout N/A ORDS has no equivalent.
PlsqlPathAlias N/A ORDS has no equivalent.
PlsqlPathAliasProcedure N/A ORDS has no equivalent.
PlsqlUploadAsLongRaw N/A ORDS has no equivalent.

Example ORDS Configuration Files

The following sections show how the example mod_plsql application can be migrated to ORDS.

Example Configuration File for Basic Authentication

Example 18-2 ords_conf/databases/basic_auth/pool.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Saved on Wed Jul 25 10:22:37 UTC 2018</comment>
    <entry key="db.username">PRIVILEGED_USER</entry>
    <entry key="db.password">!passwordF0R$0RD5Example</entry>
    <!-- http://localhost:8086/ords/pls/basic_auth/sample_plsql_app.sample_public_proc-->
    <!-- http://localhost:8086/ords/pls/basic_auth/sample_plsql_app.privileged_public_proc-->
    <entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>
    <entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>
    <entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>
    <entry key="owa.docTable">sample_plsql_app.doc_table</entry>
</properties>

Example Configuration File for Basic Dynamic Authentication

Example 18-3 ords_conf/databases/basic_dynamic_auth/pool.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Saved on Wed Jul 25 10:22:37 UTC 2018</comment>
    <!--  NOTE THAT IF THIS USER HAS EXECUTE PRIVILEGE ON THE RESOURCE THEN jdbc.auth.enabled IS IGNORED -->
    <!--  IF THIS USER DOES NOT HAVE EXECUTE PRIVILEGE ON THE RESOURCE THEN jdbc.auth.enabled IS INVOKED AND THE CREDENTIALS OF A PRIVILEGED USER HAS TO BE PROVIDED-->
    <entry key="db.username">NON_PRIVILEGED_USER</entry>
    <entry key="db.password">!passwordF0R$0RD5Example</entry>
    <entry key="jdbc.auth.enabled">true</entry>
    <!-- INVOKE jdbc.auth.enabled :   http://localhost:8086/ords/pls/basic_dynamic_auth/sample_plsql_app.sample_privileged_proc -->
    <!-- IGNORE jdbc.auth.enabled :   http://localhost:8086/ords/pls/basic_dynamic_auth/sample_plsql_app.sample_public_proc   -->
    <!-- Because jdbc.auth.enabled is ignored when referencing the sample_public_app, the beforeProc,afterProc and validationFunc must be accessible by NON_PRIVILEGED_USER -->
    <!-- The following objects are executed by the same credentials used to access the resource -->
    <!-- If the resource can be accessed by the db.username then that connection is used to access these methods -->
    <!-- If the resource cannot be accessed by the db.username then jdbc.auth.enabled is invoked and those credentials as used to access these methods -->
    <entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>
    <entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>
    <entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>
</properties>

Example Configuration file for Custom Authentication

Example 18-4 ords_conf/databases/custom_auth/pool.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Saved on Wed Jul 25 10:22:37 UTC 2018</comment>
    <entry key="db.username">PRIVILEGED_USER</entry>
    <entry key="db.password">!passwordF0R$0RD5Example</entry>
    <!-- http://localhost:8086/ords/pls/custom_auth/sample_plsql_app.sample_proc -->
    <!-- privileged_user.owa_custom.authorize requires the following as the custom login -->
    <entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>
    <entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>
    <entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>
    <entry key="security.requestAuthenticationFunction">privileged_user.owa_custom.authorize</entry>
</properties>

Example ORDS URL Mapping

This section shows the example mapping between base-path url and the configuration files.

Example 18-5 ords_conf/databases/basic_auth/paths

/pls/basic_auth

Example 18-6 ords_conf/databases/basic_dynamic_auth/paths

/pls/basic_dynamic_auth

Example 18-7 ords_conf/databases/custom_auth/paths

/pls/custom_auth

Example ORDS Default Configuration

This section shows the example default configuration setting for ORDS.

The settings.xml file provides the database connection details used by all configurations. Note: To turn off procedure validation caching, set security.maxEntries value to 0. This is necessary to emulate Oracle HTTP Server mod_plsql.

Example 18-8 ords_conf/global/settings.xml

<?xml version = '
1.0' encoding = 'UTF-8'?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
   <!-- by default security.maxEntries = 2000 which means 2000 procedures validity will be cached-->
   <!-- this is fine for applications like apex where the validation of a procedure does not change  -->
   <!-- for applications migrating from mod_plsql the cache should be disabled so that procedures validity is determined for each request -->
   <!-- this is done by setting security.maxentries to 0 -->
   <entry key="security.maxEntries">0</entry>
   <entry key="db.hostname">oracle-ee</entry>
   <entry key="db.port">1521</entry>
   <entry key="db.servicename">orclpdb1</entry>
</properties>

Oracle REST Data Services Functionality

Oracle REST Data Services is a J2EE-based servlet which offers increased functionality including a web-based configuration, enhanced security, and file caching.

The following sections explains the different functionalities equivalent to mod_plsql module.

Basic Authentication

This section describes the basic authentication implemented using ORDS.

The database credentials are specified in the ORDS configuration file. The db.username must have the required privileges to access the resources. Note: The entry security.requestAuthenticationFunction is not specified.

Basic Dynamic Authentication

This section describes how basic dynamic authentication is implemented using ORDS.

A default db.username and db.password must be specified in ORDS configuration file when providing basic dynamic authentication for accessing the resources.

The resources that cannot be accessed using this type of authentication can be accessed if the following conditions are satisfied:

Note: If the credentials are provided through the browser HTTP authentication dialog box, then the only way to log out is to close all the instances of the browser.

Custom Authentication

This section describes how custom authentication is implemented using ORDS.

A function is specified to perform the custom authentication. This function has access to the owa variables. Resources are only available if the following function returns a TRUE value:

<entry key="security.requestAuthenticationFunction">privileged_user.owa_custom.authorize</entry>

The authentication function must have signature as shown in the following code snippet:

/**
* OWA_CUSTOM used in mod_plsql when the following is used in the dad configuration file
  PlsqlAuthenticationMode       Custom
  In ORDS environment this can reside in any schema as long as the connection has execute privileges
  In mod_plsql this has to reside in the connections schema as you cannot specify the name of the schema,package or function
  ex: PlsqlAuthenticationMode           CustomOwa
*/
CREATE OR REPLACE PACKAGE OWA_CUSTOM  AS
/**
 * Response:
  >IF Failed
   WWW-Authenticate in response header
   Authorization Required
   You are not authorized to access the requested resource. Check the supplied credentials (e.g., username and password).
 */
FUNCTION authorize RETURN BOOLEAN;
END OWA_CUSTOM ;
/

Oracle REST Data Services Database Authentication

This section describes how to use the database authentication feature to provide basic authentication for PL/SQL gateway calls.

Database authentication feature is similar to dynamic basic authentication provided by mod-plsql where the user is prompted for the database credentials to authenticate and authorize access to PL/SQL stored procedures.

Installing Sample Database Scripts

This section describes how to install the sample database scripts.

The unzipped Oracle REST Data Services installation kit contains the sample database scripts that create a basic demo scenario for the database authentication.

The following code snippet shows how to install the sample database schema:

 examples\db_auth $ cd sql/
sql $ sql system/<password>

SQLcl: Release Release
18.1.1 Production on Fri Mar 23 14:03:18 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> @install <chosen-password>
  <div class="infoboxnote" markdown="1">
  **Note:**



  - You need to adjust the SQLcl connect string and the user credentials to suit your environment. For this demo scenario, SQLcl connects to the database with service name `orcl`



  - `` is the password you assigned to `EXAMPLE_USER1` and `EXAMPLE_USER2` database users. Make a note of this password value for later reference.



  </div>

The sample database schema creates the following database users:

Enabling the Database Authentication

This section describes how to enable the database authentication feature.

Database authentication specifies if the PL/SQL Gateway calls can be authenticated using the database users. Defaults to value false. To enable the feature, set to value to true. Oracle recommends not to use this feature. This feature is used only to facilitate customers migrating from mod_plsql. To enable the database authentication feature, do one of the following:

$ ords config --db-pool default set jdbc.auth.enabled true
 ORDS: Release 25.2 Production on Mon Apr 28 10:08:44 2025
 Copyright (c) 2010, 2025, Oracle.
 Configuration:
     /Users/ords_config
 The setting named: jdbc.auth.enabled was set to: true in configuration: default

Note: The jdbc.auth.enabled setting can be configured per database pool. Alternatively, it can be configured in ords_config/global/settings.xml file so that it is enabled for all pools.

This example code snippet shows how jdbc.auth.enabled setting is enabled for all pools.

$ ords config set --global jdbc.auth.enabled true

ORDS: Release
25.2 Production on Mon Apr 28 10:08:44 2025
Copyright (c) 2010, 2025, Oracle.
Configuration:
    /Users/ords_config
The global setting named: jdbc.auth.enabled was set to: true

After you update the configuration settings, restart the Oracle REST Data Services for the changes to take effect.

Configuring the Request Validation Function

This section describes how to temporarily disable the request validation function.

If you want to invoke only a whitelisted set of stored procedures in the database through the PL/SQL gateway, then you must configure Oracle REST Data Services to use a request validation function (especially when you are using Oracle APEX).

The demo sample procedure used for testing the database authentication feature is not whitelisted, so you must temporarily disable the request validation function.

To disable the request validation function, perform the following steps:

  1. Navigate to the <Current Configuration directory>/global directory.

  2. Open the settings.xml file, which stores the Oracle REST Data Services configuration information.

  3. Look for security.requestValidationFunction entry and remove it from the file.

  4. Save the file.

  5. Restart Oracle REST Data Services, if it is already running. Note: In production environment, you must use a custom request validation function that whitelists the stored procedures you want to access for your application

Testing the Database Authenticated User

This section describes how to test if the database user is authenticated.

Assuming that Oracle REST Data Service is running in a standalone mode on local host and on port 8080, access the following URL in your web browser:

http://localhost:8080/ords/sample_plsql_app.sample_proc

The browser prompts you to enter credentials. Enter example_user1 for user name and enter the password value you noted while installing the sample schema.

The browser displays ‘Hello EXAMPLE_USER1!’ to demonstrate that the database user was authenticated and the identity of the user was propagated to the database through the OWA CGI variable named REMOTE_USER..

Uninstalling the Sample Database Schema

To uninstall the database schema, run the commands as shown in the following code snippet:

db_auth $ cd sql/
sql $ sql system/<password>

SQLcl: Release Release
18.1.1 Production on Fri Mar 23 14:03:18 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @uninstall

ORDS Features

This section describes the ORDS features that are useful when you are migrating from a mod_plsql application to ORDS.

Topics:

Request Validation Function

This section explains the use of request validation function.

The request validation function restricts the access to resources. The request validation function is provided with the name of the resource being requested and returns TRUE or FALSE value in response.

If the request validation function returns a FALSE value, then ORDS terminates the request.

Example 18-9 security.requestValidationFunction

<entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>

You can choose any name for the validation function. However, the signature must be in the following format:

CREATE OR REPLACE FUNCTION validationfunc(procedure_name VARCHAR2) RETURN BOOLEAN IS.

Pre Process Feature

This section describes the procedure.preProcess ORDS configuration parameter.

The procedure.preProcess ORDS configuration parameter allows a comma delimited list of procedures that are executed before the requested resource.

Example 18-10 procedure.preProcess

Following example code snippet shows a use case for logging in:

<entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>

Post Process Feature

This section describes the procedure.postProcess ORDS configuration parameter.

The procedure.postProcess ORDS configuration parameter allows a comma delimited list of procedures that are executed after the requested resource.

Example 18-11 procedure.postProcess

Following example code snippet shows a use case for logging out:

<entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>

File Upload Feature

This section describes the ORDS file upload feature.

The ORDS configuration parameter owa.docTable, defines the table name where the uploaded files persist.

Example 18-12 Table upload

CREATE TABLE DOC_TABLE (
     NAME               VARCHAR(256)   UNIQUE NOT NULL,
     MIME_TYPE          VARCHAR(128),
     DOC_SIZE           NUMBER,
     DAD_CHARSET        VARCHAR(128),
     LAST_UPDATED       DATE,
     CONTENT_TYPE       VARCHAR(128),
     CONTENT            LONG RAW,
     BLOB_CONTENT       BLOB );

Example 18-13 Procedure upload

You can choose to have any name for the upload function. However, the signature must match the following POST request:


--The parameters of the procedure should match the parameters of the request
--The procedure is called after ORDS performs the file upload/insert.
--This procedure can rollback the file INSERT as it is in the same transaction as the INSERT
CREATE OR REPLACE PROCEDURE upload (filename VARCHAR2 DEFAULT NULL)

Example 18-14 Curl command for file upload

curl -i -X POST -F 'filename=@helloworld.txt' "http://localhost:8086/ords/pls/basic_auth/example_user1.upload

Cross-Origin Resource Sharing Feature

This section describes the Cross-Origin Resource Sharing (CORS) feature.

By default ORDS does not allow cross-origin calls to its PL/SQL gateway.

Trusted origins can be configured through the security.externalSessionTrustedOrigins configuration parameter that defines a comma separated list of origins that are trusted to make CORS request. If this parameter is empty or not configured, then no CORS requests are allowed for the PL/SQL gateway and results in a 403 Unauthorized status.

<entry key="security.externalSessionTrustedOrigins">http://example.com, https://example.com:8443</entry>

Procedure Allow List

This section describes the Allow List feature for PL/SQL Gateway procedures.

Oracle REST Data Services (ORDS) provides an Allow List feature for PL/SQL Gateway procedures.You can authorize execution of custom procedures by adding them to the Allow List.

Configuring ORDS PL/SQL Gateway Allow List

This section describes how to configure the ORDS PL/SQL Gateway Allow List.

Ensure that your PL/SQL Gateway pool is configured to use the ORDS validation function named ords_util.authorize_plsql_gateway.

./ords config --db-pool <plsql_pool> get security.requestValidationFunction

If PL/SQL Gateway pool is empty, then it is setup using the following command:

./ords config --db-pool <plsql_pool> set security.requestValidationFunction ords_util.authorize_plsql_gateway

Note: To use the feature ORDS PL/SQL Gateway Allow list with APEX, the APEX must be installed in a Pluggable Database.

Authorizing Procedures

This section describes how to authorize the stored procedures.

To authorize the stored procedures you must add them to the PL/SQL Gateway Allow List using the ords_admin.add_plsql_gateway_procedure procedure. You are required to have ORDS_ADMINISTRATOR_ROLE role to execute the procedure.

BEGIN
        ords_admin.add_plsql_gateway_procedure(
                    p_owner => 'MY_SCHEMA',
                    p_package_name => 'MY_PACKAGE', /* Can be null if not a package procedure*/
                    p_procedure_name => 'MY_STORED_PROCEDURE',
                    p_comments => 'Enabling access to Project 1'); /* Optional comments*/
END;

The Allow List stores resolved procedure names. Procedures are resolved before adding them to the list.

Removing Stored Procedures

This section describes how to remove the stored procedures from the PL/SQL Gateway Allow List.

To remove the stored procedures from the PL/SQL Gateway Allow List use ords_admin.remove_plsql_gateway_procedure procedure. You are required to have ORDS_ADMINISTRATOR_ROLE role to execute the procedure.

BEGIN
        ords_admin.remove_plsql_gateway_procedure(
                    p_owner => 'MY_SCHEMA',            /* Schema owning the stored procedure */
                    p_package_name => 'MY_PACKAGE',    /* Can be null if not a package procedure*/
                    p_procedure_name => 'MY_STORED_PROCEDURE');
END;
Removing Stored Procedures in Bulk

This section describes how to remove the stored procedures in bulk from the PL/SQL Gateway Allow List.

To remove the stored procedures from the PL/SQL Gateway Allow List use ords_admin.clear_plsql_gateway_procedures procedure. You are required to have ORDS_ADMINISTRATOR_ROLE role to execute the procedure.

BEGIN
        ords_admin.clear_plsql_gateway_procedures(
                                    p_owner => 'MY_SCHEMA'); /* Remove all procedures owned by this schema. */
END;

Monitoring the Allowed Procedures

To monitor which procedures have been allowed, administrator user with ORDS_ADMINISTRATOR_ROLE can use DBA_PLSQL_GATEWAY_ALLOW_LIST view.

select * from DBA_PLSQL_GATEWAY_ALLOW_LIST

Modifying Synonyms

When you are invoking synonyms through PL/SQL Gateway, ORDS executes the procedure they point to. If an existing synonym is redefined to point to a second procedure, then revoke the EXECUTE privilege from the first procedure to force the synonym to reload and ensure that ORDS executes the second procedure.