Migrating from mod_plsql to ORDS

7 Migrating from mod_plsql to ORDS

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.

Topics:

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:

Basic authentication: The username and password are stored in the DAD file and so the end user is not required to log in. This method is useful for web pages that provide public information.
Basic dynamic authentication: The users provide credentials in a browser HTTP basic authentication dialog box. The only way to log out is to close all the instances of the browser.
Custom authentication: Enables applications to invoke a user-written authentication function to authenticate the users within the application and not at the database level.

Related Topics

Oracle HTTP Server mod_plsql

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:

PlsqlBeforeProcedure
PlsqlAfterProcedure
PlsqlRequestValidationFunction
PlsqlDocumentTablename
PlsqlDocumentProcedure

Example 7-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 ords_conf/ords/conf directory and the configuration file is then mapped to a particular URL using the ords_conf/ords/url-mapping.xml file. ORDS provides the following configurable parameters that can be used when migrating mod_plsql directives:

Table 7-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 CustomOwa`of mod_plsql. Example ORDS equivalent configuration parameter: `<entry key="security.requestAuthenticationFunction">privileged_user.owa_custom.authorize</entry>` 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`	Specifies a pattern for procedures, packages, or schema names which are forbidden to be directly run from a browser. See Understanding Configurable Parameters. ORDS and mod_plsql are equivalent.
`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	Specifies how package and session state should be cleaned up at the end of each request. ORDS always performs: `dbms_session.modify_package_state`(`dbms_session.reinitialize`) at the end of each request.
`PlsqlAlwaysDescribeProcedure`	N/A	Specifies whether the mod_plsql application should describe a procedure before trying to run it. 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	`PlsqlTransferMode` specifies the transfer mode for data from the database back to the mod_plsql application. 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.

Topics:

Example Configuration File for Basic Authentication

Example 7-2 ords_conf/ords/conf/basic_auth.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>
    <!-- Example url -->
    <!-- See url-mapping.xml -->
    <!-- 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 7-3 ords_conf/ords/conf/basic_dynamic_auth.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>
    <!-- Example url -->
    <!-- See url-mapping.xml -->
    <!-- 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 7-4 ords_confs/ords/conf/custom_auth.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>
    <!-- Example url -->
    <!-- See url-mapping.xml -->
    <!-- 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 7-5 ords_conf/ords/url-mapping.xml

<?xml version="1.0" encoding="UTF-8"?>
<pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
    <pool name="basic_auth" base-path="/pls/basic_auth" updated="2018-07-17T20:52:29.045Z" />
    <pool name="basic_dynamic_auth" base-path="/pls/basic_dynamic_auth" updated="2018-07-17T20:52:29.045Z" />
    <pool name="custom_auth" base-path="/pls/custom_auth" updated="2018-07-17T20:52:29.045Z" />
</pool-config>

Example ORDS Default Configuration

This section shows the example default configuration setting for ORDS.

The defaults.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 7-6 ords_conf/ords/defaults.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>

ORDS Authentication

ORDS has the ability to perform HTTP Basic Authentication by providing a one to one mapping from mod_plsql. In ORDS more secure methods of authentication are available.

Topics:

Related Topics

Developing Oracle REST Data Services Applications

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:

The value for <entry key="jdbc.auth.enabled">true</entry> entry must be true..
The security.requestAuthenticationFunction entry must not be specified.
When ORDS response prompts a Basic HTTP Authentication dialog box in a browser, the credentials provided by the user must have the required privileges, then the resource is made available.

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 ;
/

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 7-7 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 7-8 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 7-9 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 7-10 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 7-11 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 7-12 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>