2 Configuring Oracle REST Data Services (Advanced)

This section explains how to configure Oracle REST Data Services for connecting to multiple databases for routing requests, and it refers to other documentation sources for other configuration information.

Note:

Oracle REST Data Services must be restarted after making configuration changes. See your application server documentation for information on how to restart applications.

Topics:

• Configuring Multiple Databases

• Support for Oracle RAC Fast Connection Failover

• Configuring Security, Caching, Pre- and Post Processing, Environment, and Excel Settings

• Configuring REST-Enabled SQL Service Settings

• Configuring the Maximum Number of Rows Returned from a Query

• Configuring ICAP Server Integration for Virus Scan

• Configuring ORDS with Kerberos Setup

• Configuring the Custom Error Pages

• Configuring ORDS Metadata Cache

• Developing RESTful Services for Use with Oracle REST Data Services

2.1 Configuring Multiple Databases

Oracle REST Data Services supports the ability to connect to more than one database. This section describes different strategies for routing requests to the appropriate database.

Topics:

• About the Request URL

• Configuring Additional Databases

• Routing Based on the Request Path Prefix

• Routing Based on the Request URL Prefix

2.1.1 About the Request URL

Oracle REST Data Services supports a number of different strategies for routing requests to the appropriate database. All of these strategies rely on examining the request URL and choosing the database based on some kind of match against the URL. It is useful to recap the pertinent portions of a request URL. Consider the following URL:

https://www.example.com/ords/sales/f?p=1:1

This URL consists of the following sections:

• Protocol: https

• Host Name: www.example.com

• Context Root: /ords

  The context root is the location at which Oracle REST Data Services is deployed on the application server.

• Request Path: /sales/f?p=1.1

  This is the portion of the request URL relative to the context root.

For different applications, it may be important to route requests based on certain prefixes in the request path or certain prefixes in the full request URL.

There are two steps to configuring multiple databases:

1. Configuring the database connection information

2. Configuring which requests are routed to which database

2.1.2 Configuring Additional Databases

When you first configure Oracle REST Data Services, you configure a default database connection named: apex. You can create additional database connections using the setup command.

Tip:

To see full help for the setup command type:

java -jar ords.war help setup

To create a database connection type the following:

java -jar ords.war setup --database <database name>

Where:

• <database name> is the name you want to give the database connection.

You are prompted to enter the information required to configure the database. After you have configured the additional databases, define the rules for how requests are routed to the appropriate database.

2.1.3 Routing Based on the Request Path Prefix

You create request routing rules using the map-url command.

Tip:

To see full help for the map-url command type:

java -jar ords.war help map-url

If you want to route requests based just on matching a prefix in the request path portion of the URL, use the map-url command as follows:

java -jar ords.war map-url --type base-path --workspace-id <workspace name> <path prefix> <database name>

Where:

• <workspace name> is the name of the Oracle Application Express workspace where RESTful services for this connection are defined. This may be omitted if RESTful Services are not being used.

• <path prefix> is the prefix that must occur at the start of the request path.

• <database name> is the name of the database connection configured in the previous step.

2.1.3.1 Example of Routing Based on the Request Path Prefix

Assuming Oracle REST Data Services is deployed on a system named example.com at the context path /ords, then create the following rule:

java -jar ords.war map-url --type base-path --workspace-id sales_rest /sales sales_db

This rule means that any requests matching https://example.com/ords/sales/... are routed to the sales_db database connection. The sales_rest workspace defined within the sales_db database is searched for RESTful Services definitions.

The previous rule matches all of the following requests:

https://example.com/ords/sales/f?p=1:1
https://example.com/ords/sales/leads/
https://www.example.com/ords/sales/forecasting.report?month=jan  (If www.example.com resolves to the same system as example.com.)

The previous rule does not match of any of the following requests:

http://example.com/ords/sales/f?p=1:1  (The protocol is wrong.)
https://example.com:8080/ords/sales/f?p=1:1  (The port is wrong: 443 is default for https, but don't specify if using default.)
https://example.com/ords/f?p=1:1  (Missing the /sales prefix.)
https://example.com/pls/sales/leads/  (The context path is wrong.)

2.1.4 Routing Based on the Request URL Prefix

If you want to route requests based on a match of the request URL prefix, use the map-url command as follows:

java -jar ords.war map-url --type base-url --workspace-id <workspace name> <url prefix> <database name>

Where:

• <workspace name> is the name of the Oracle Application Express workspace where RESTful services for this connection are defined. This may be omitted if RESTful Services are not being used.

• <url prefix> is the prefix with which the request URL must start.

• <database name> is the name of the database connection.

2.1.4.1 Example of Routing Based on the Request URL Prefix

Assuming Oracle REST Data Services is deployed on a system named example.com at the context path /ords, then create the following rule:

java -jar ords.war map-url --type base-url --workspace-id sales_rest https://example.com/ords/sales sales_db

This rule means that any requests matching https://example.com/ords/sales/... are routed to the sales_db database connection. The sales_rest workspace defined within the sales_db database is searched for RESTful Services definitions.

The previous rule matches all of the following requests:

https://example.com/ords/sales/f?p=1:1
https://example.com/ords/sales/leads/
https://example.com/ords/sales/forecasting.report?month=jan

The previous rule does not match of any of the following requests:

http://example.com/ords/sales/f?p=1:1  (The protocol is wrong.)
https://example.com:8080/ords/sales/f?p=1:1  (The port is wrong: 443 is default for https, but don't specify if using default.)
https://example.com/ords/f?p=1:1  (Missing the /sales segment of the base URL.)
https://example.com/pls/sales/leads/  (The context path is wrong.)
https://www.example.com/ords/sales/forecasting.report?month=jan  (The host name is wrong.)

2.2 Support for Oracle RAC Fast Connection Failover

Oracle REST Data Services support the Fast Connection Failover (FCF) feature of Oracle Real Application Clusters (Oracle RAC).

Oracle REST Data Services runs with the Universal Connection Pool (UCP) in all the Application Server environments that it supports, such as WebLogic, Tomcat. UCP in turn supports Fast Connection Failover . To enable FCF, Oracle Notification Service (ONS) must to be enabled. To enable ONS, add entries to the list of properties in the Oracle REST Data Services defaults.xml configuration file as shown in the following code snippet:

<entry key="jdbc.enableONS">true</entry>
<entry key= "jdbc.ONSConfig">nodes=racnode1:4200,racnode2:4200\nwalletfile=/oracle11/onswalletfile</entry>

ONS is the messaging facility used to send the Fast Application Notification (FAN) events. When ONS is enabled, Oracle REST Data Services automatically enables FCF. To Enable specific FCF capabilities such as fail over or other advanced FCF capabilities such as load balancing, you need to add entries in the configuration file for the custom connection as shown in the following code snippet:

<entry key="db.connectionType">customurl</entry>
<entry key="db.customURL">jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(ADDRESS_LIST=
		(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=prod_scan.example.com)(PORT=1521)))
		(CONNECT_DATA=(SERVICE_NAME=ISPRD)))|</entry>

After updating the defaults.xml configuration file, Oracle REST Data Services need to be restarted for the changes to take effect.

UCP supports Fast Connection Failover. FCF listens and responds to FAN events to deal with the following two scenarios:

• Unplanned outages: When RAC detects an instance failure, it generates a FAN Down event which FCF picks up.  FCF then terminates all connections to the failed instance and directs all future requests to the surviving RAC instances.

• Planned outages: For instance, when a Database Administrator (DBA) wants to gracefully shut down a RAC instance for performing some maintenance activity. The instance shutdown generates a FAN Planned Down event which FCF picks up. FCF then directs all new requests to other RAC instances and drains or allows currently active transactions to complete.

Note:

Long running transactions may need to be terminated forcefully.

2.3 Configuring Security, Caching, Pre- and Post Processing, Environment, and Excel Settings

To configure security, caching, pre- and post- processing, environment, and Excel settings, see Using SQL Developer Oracle REST Data Services Administration (Optional).

2.4 Configuring REST-Enabled SQL Service Settings

This section explains how to configure the REST- Enabled SQL service.

Note:

Enabling the REST- Enabled SQL service enables authentication against the Oracle REST Data Service enabled database schemas. This makes the database schemas accessible over HTTPS, using the database password. Oracle highly recommends that you provide strong secure database passwords

REST- Enabled SQL service is a feature of Oracle REST Data Service. By default, the REST Enabled SQL service is turned off. To enable the REST- Enabled SQL service and the REST- Enabled SQL Export service, perform the following steps:

1. Locate the folder where the Oracle REST Data Services configuration file is stored.

2. Open the defaults.xml file and add: <entry key="restEnabledSql.active">true</entry>.

3. Save the file.

4. Restart Oracle REST Data Services.

2.5 Configuring the Maximum Number of Rows Returned from a Query

To configure maximum number of rows returned from a query, perform the following steps:

1. Locate the folder where the Oracle REST Data Services configuration file is stored.

2. Open the defaults.xml file and update the value of the misc.pagination.maxRows parameter:<entry key=”misc.pagination.maxRows”>1500</entry>

   Note:

   The default value for misc.pagination.maxRows is 500.

3. Save the file.

4. Restart Oracle REST Data Services.

2.6 Configuring ICAP Server Integration for Virus Scan

This section explains how to configure ORDS to integrate with ICAP server for virus scan.

ORDS PL/SQL gateway supports the offloading of virus scanning responsibilities to an Internet Content Adaptation Protocol (ICAP) compliant virus scan server when the files are uploaded. The hostname and port of the virus scan server is specified in the icap.server, icap.port, and icap.secure.port global configuration properties.

APEX uses ORDS PL/SQL gateway. Once configured, this ICAP integration is also applied to file uploads in APEX.

To configure ORDS to integrate with ICAP server, perform the following steps:

1. Locate the folder where the Oracle REST Data Services configuration file is stored.

2. Open the defaults.xml file and add:

   <entry key="icap.port">1234</entry>
   <entry key="icap.server">name_or_ip</entry>

3. Save the file.

   Restart Oracle REST Data Services.

ICAP server must support the following requirements:

• ICAP version 1.0
• Antivirus service named AVSCAN
• Antivirus service that supports action=SCAN
• Previews of at least 4 bytes
• Return header named X-Infection

Once configured, when a file is uploaded through PL/SQL Gateway, ORDS makes a request similar to the following:

RESPMOD icap://<icap_server>:<icap_port>/AVSCAN?action=SCAN ICAP/1.0
Host: <icap_server>:<icap_port>
Preview: 4
Allow: 204
Encapsulated: req-hdr=0 res-hdr=153 res-body=200

2.7 Configuring ORDS with Kerberos Setup

This section explains how ORDS can be configured to reference a Kerberos file-based ticket cache and make a connection to an Oracle Database Kerberos authenticated user with the ORDS Runtime Privilege.

To configure ORDS with Kerberos setup, perform the following steps:

1. Create a new user using external authentication
2. Set up the environment variables
3. Provide a valid ticket
4. Add ORDS pool settings

1. Create a New User using External Authentication
   Create a new Oracle Database user using external authentication (Kerberos) and provision the user as an ORDS runtime user.

   CREATE USER ORDS_PUBLIC_KRBUSER IDENTIFIED EXTERNALLY AS '<kerberos_principal_name>';
   GRANT CONNECT TO "ORDS_PUBLIC_KRBUSER";
   BEGIN
        ORDS_ADMIN.PROVISION_RUNTIME_ROLE(
            p_user => 'ORDS_PUBLIC_KRBUSER',
            p_proxy_enabled_schemas => TRUE);
   END;
   /

2. Set up the Environment Variables

   Note:

   Ensure that you have a Kerberos configuration file krb5.conf and a file-based ticket cache

   Set up the following environment variables:

   export KRB5_CONFIG=<path to krb5.conf>
   export KRB5CCNAME=<path to credential cache>

3. Provide a Valid Ticket
   Provide a valid ticket in the ticket cache to get authenticated when connecting to the Oracle Database.

   kinit <principal>

4. Add ORDS Pool Settings
   Add the following pool settings to the pool.xml file using the ticket in the ticket cache:

   <entry key="oracle.net.authentication_services">(KERBEROS5)</entry>
   <entry key="oracle.net.kerberos5_mutual_authentication">true</entry>

   Run the following command when ORDS is starting up:

   -Djava.security.krb5.conf="<path to krb5.conf>"

   For example, to run ORDS in standalone mode with Kerberos, execute the following command:

   java -Djava.security.krb5.conf=$KRB5_CONFIG -jar ords.war

2.7.1 Configuring ORDS with Kerberos Setup Using Command-line Interface

This section explains how to configure ORDS with Kerberos setup. ORDS can be configured to reference a Kerberos file-based ticket cache and make a connection to an Oracle Database Kerberos authenticated user through command-line interface.

Use the following commands to configure ORDS with Kerberos setup:

java -jar ords.war configdir configkrb                                                      
java -jar ords.war setup                                                                    
java -jar ords.war set-property --conf apex_pu oracle.net.authentication_services "(KERBEROS5)"
java -jar ords.war set-property --conf apex_pu oracle.net.kerberos5_mutual_authentication true
export KRB5_CONFIG=<path to krb5.conf>
export KRB5CCNAME=<path to credential cache>
kinit <principal>

2.8 Configuring the Custom Error Pages

This section explains how to configure a custom error page instead of the error page generated by Oracle REST Data Services.

To configure a custom error page, perform the following steps:

1. Locate the folder where the Oracle REST Data Services configuration file is stored.

2. Open the defaults.xml file and update the value of the error.externalPath parameter:

   <entry key=”error.externalPath">/path/to/error/pages/folder/</entry>

   Where:

   • /path/to/error/pages/folder is the path to a folder containing files that define the error pages. The files are stored in {status}.html format.

     Where, {status} is the HTTP status code for which you want to create a custom error page.

3. Save the file.

4. Restart Oracle REST Data Services.

Example 2-1 Configuring custom error page for “HTTP 404” status code

To configure a custom error page for the “HTTP 404 – Not Found” status, perform the following steps:

1. Create a file named 404.html.

2. Save it under /usr/local/share/ords/error-pages/ folder.

3. Configure the error.externalPath parameter to point to /usr/local/share/ords/errro-pages/ folder.

4. Save the file.

5. Restart Oracle REST Data Services.

2.9 Configuring ORDS Metadata Cache

This section explains how to configure the ORDS Metadata Cache.

As the number of REST services grow, the overhead of querying the database for corresponding metadata can have a negative impact on the overall service performance and throughput. Overtime, the queries for ORDS_METADATA views take longer time to complete. These queries are executed for every request. The ORDS metadata cache can help improve the overall response time for REST services when the number of services grow to an extent that querying the ORDS_METADATA views for every request becomes expensive. The ORDS metadata cache can temporarily hold a copy of privilege and module metadata in memory to reduce the number of database queries performed when a REST service request is received. The cache is disabled by default so that the changes made to the metadata are applied immediately for any subsequent request.

Table 2-1 Configuration Properties for ORDS Metadata Cache

Property	Data Type	Default Value	Description
cache.metadata.enabled	Boolean	false	Specifies a setting to enable or disable metadata caching.
cache.metadata.timeout	Duration	30s	Specifies a setting that determines for how long the metadata record remains in the cache. Longer the duration, it takes longer to view the applied changes.

2.10 Developing RESTful Services for Use with Oracle REST Data Services

For more information on how to develop RESTful Services for use with Oracle REST Data Services, see Developing Oracle REST Data Services Applications.

2.11 Managing ORDS Administrator Privilege

Access to the ORDS_ADMIN PL/SQL package is provisioned through the ORDS_ADMINISTRATOR_ROLE. This role can be provisioned through the ORDS_ADMIN package to create additional ORDS administrators.

2.11.1 Provisioning ORDS_ADMINISTRATOR_ROLE to a User

This section describes how to provision ORDS_ADMINISTRATOR_ROLE role to a user.

You can provision ORDS_ADMINISTRATOR_ROLE role to a user by using either the database GRANT command or through the ORDS_ADMIN.PROVISION_ADMIN_ROLE PL/SQL method (as an ORDS Administrator).

Example 2-2 Using Grant command

GRANT ORDS_ADMINISTRATOR_ROLE TO HR_ADMIN;

Example 2-3 Using ORDS_ADMIN package method

BEGIN
  ORDS_ADMIN.PROVISION_ADMIN_ROLE(
    p_user => 'HR_ADMIN');
END;
/

2.11.2 Unprovisioning ORDS_ADMINISTRATOR_ROLE from a User

This section describes how to unprovision ORDS_ADMINISTRATOR_ROLE from a user.

As an ORDS administrator, you can unprovision ORDS_ADMINISTRATOR_ROLE from a user by either using the database REVOKE command or through the ORDS_ADMIN.UNPROVISION_ROLES PL/SQL method.

Example 2-4 Using REVOKE command

REVOKE ORDS_ADMINSTRATOR_ROLE FROM HR_ADMIN;

Example 2-5 Using ORDS_ADMIN package method

BEGIN
  ORDS_ADMIN.UNPROVISION_ROLES(
    p_user => 'HR_ADMIN',
    p_administrator_role => TRUE);
 END;
 /

2.12 Managing ORDS Runtime Privilege

The ORDS_RUNTIME_ROLE database role allows a user to act as a runtime user. A runtime user can manage and configure the runtime connection resources required by an ORDS service instance. The ORDS_PUBLIC_USER is one such database user. When additional runtime users are provisioned, it is possible to configure discrete ORDS service instances with different destination addresses and connection pools but hosted on the same Oracle database container.

It is recommended not to re-use a runtime user for any other purpose as it accumulates the grants necessary to proxy to other users. A runtime user only requires the CREATE SESSION privilege in addition to the ORDS_RUNTIME_ROLE role.

2.12.1 Provisioning ORDS_RUNTIME_ROLE to a User

This section describes how to provision ORDS_RUNTIME_ROLE role to a user.

As an ORDS administrator, you can provision ORDS_RUNTIME_ROLE role to a user by using either the database GRANT command or through the ORDS_ADMIN.PROVISION_ADMIN_ROLE PL/SQL method.

Example 2-6 Using Grant command

GRANT ORDS_RUNTIME_ROLE TO ORDS_PUBLIC_USER_2;

Example 2-7 Using ORDS_ADMIN package method

BEGIN
  ORDS_ADMIN.PROVISION_RUNTIME_ROLE(
    p_user => 'ORDS_PUBLIC_USER_2');
END;
/

2.12.2 Unprovisioning ORDS_RUNTIME_ROLE from a User

This section describes how to unprovision the ORDS_RUNTIME_ROLE role from a user

As an administrator, you can unprovision the ORDS_RUNTIME_ROLE from a user, by either using the database REVOKE command or through the ORDS_ADMIN.UNPROVISION_ROLES PL/SQL method.

Example 2-8 Using REVOKE command

REVOKE ORDS_RUNTIME_ROLE FROM ORDS_RUNTIME_USER_2;

Example 2-9 Using ORDS_ADMIN package method

BEGIN
  ORDS_ADMIN.UNPROVISION_ROLES(
    p_user => 'ORDS_RUNTIME_USER_2',
    p_runtime_role => TRUE);
 END;
 /