Miscellaneous Configuration Options of Oracle REST Data Services

4 Miscellaneous Configuration Options of Oracle REST Data Services

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:

You must restart Oracle REST Data Services after making any configuration changes. For ensuring High Availability, Oracle recommends that you use a load balancer in front of multiple ORDS instances, so that rolling restarts can be achieved.

Topics:

4.1 Using the REST Enabled SQL Service with MySQL Database

This section describes the ORDS feature which is only supported with MySQL database running on Oracle Cloud Infrastructure.

You can setup your own ORDS instance to use the REST Enabled SQL Service with a MySQL database over JDBC. The connection details are specified similar to how you specify for other ORDS connection pool. For MySQL JDBC connections, the db.connectionType is always customurl. The db.customURL property must be a valid JDBC connection string for the database. The host machine running the database must be accessible to the host machine with ORDS instance running. The MySQL database accounts to be used must be configured to permit logins from the host machine with ORDS instance running. ORDS supports connecting to Oracle hosted MySQL database servers such as the MySQL Data Service or an Oracle Compute Instance running MySQL server.

4.1.1 Understanding the Database Credentials Source Setting

For each request received, ORDS creates a JDBC connection for executing the SQL statements in the request. To create the JDBC connection, ORDS can be configured to use the pool connection details, or uses the basic authorization credentials in the request. The credentials are specified using the db.credentials source configuration property. Possible values are POOL (default value) or REQUEST.

Note:

To access the REST Enabled SQL Service, a client must have the ORDS SQL developer role.

If you are using the default value as POOL, then the credentials in the pool configuration are used for processing any SQL statements in the request. However, the client must provide credentials for an identity management system to authorize them and assign them the SQL developer role. Only then the client can access to the REST Enabled SQL service.

If the value is set to REQUEST, then the username and password specified in the pool configuration are still required. However, these credentials are only used to verify the connection details in the pool, when the pool is used for the first time. The username and password in the basic authorization header are used to make a new JDBC connection with the target database. If a connection is established, then the client is considered to have the SQL developer role. This authorizes them to invoke the REST Enabled SQL service. The new JDBC connection is used during the request lifecycle and then closed.

4.1.2 Configuring a Pool for MySQL Database

Pool configuration is required to use ORDS with MySQL database. You can configure the pool through the ORDS command line interface.

You must configure ORDS to be able to use a MySQL database with Oracle REST Data Services (ORDS) running in a customer managed environment. Depending on where you install Oracle REST Data Services for your customer managed environment, do one of the following:

If your customer managed environment for Oracle REST Data Services runs in Oracle Cloud Infrastructure, then use an Oracle YUM repository and perform a YUM install of ORDS.
If your customer managed environment for Oracle REST Data Services runs in some other environment, then download ORDS from the Oracle REST Data Services Download page.

To use ORDS with MySQL database, you do not need any installation to the database, you only need to do a pool configuration. You can configure the pool through the ORDS command line interface.

To configure the pool for MySQL database, perform the following steps:

Note:

The credentials in a request are used to execute the SQL statements. The db.username specified in the MySQL database is the user who has all the permissions to create a connection and is used to verify the overall pool configuration details.

ords config --db-pool mysql set db.connectionType customurl
ords config --db-pool mysql set db.customURL "jdbc:mysql://10.0.1.23/?sslMode=REQUIRED"
ords config --db-pool mysql set db.username user_only_has_permission_to_connect_and_nothing_more
ords config --db-pool mysql set db.credentialsSource request
ords config --db-pool mysql set restEnabledSql.active true
ords config --db-pool mysql secret db.password

In the preceding example,

Any relevant properties for the JDBC driver can be specified in the db.customURL property. In the preceding example, db.customURL value, sslMode is set to REQUIRED, rather than the default value PREFERRED, to ensure a secure connection between ORDS and the MySQL server.
The database pool is called mysql. However, the pool can have any name. The default pool can be configured as a MySQL connection pool. You can have multiple pools defined for as many MySQL databases you wish to use.
The db.username specified is a MySQL database user who has sufficient permissions to create a connection. This database account is used to verify the overall pool configuration details.

4.1.2.1 Configuring ORDS for Supported Containers

This section describes how to use ORDS with connection pool configuration in a supported container for a MySQL database.

Specifying the Configuration Location

When you are running ORDS in a standalone mode using the ords serve command, there are options for specifying the configuration directory location. When deploying the ords.war in a supported container such as Apache Tomcat or WebLogic Server, the configuration directory location must be specified by setting the config.url system property. The mechanism for doing this varies for different container products.

To set the config.url system property before starting Apache Tomcat, run the following command:
```
export JAVA_OPTS="-Dconfig.url=/scratch/my_ords_config"
```
To set config.url system property before starting WebLogic Server, run the following command:
```
export JAVA_OPTIONS="-Dconfig.url=/scratch/my_ords_config"
```
Alternatively, use the ords war command to create a deployable web application archive file which has the config.url context parameter explicitly set and any jar files from lib/ext folder are included.

MySQL JDBC Jar for ORDS

ORDS does not distribute a MySQL JDBC jar. For ORDS to create a JDBC connection to a MySQL database, the relevant JDBC jar must be in the runtime classpath. Use OCI YUM mysql-connector-java or download MySQL Connector/J from https://www.mysql.com/ and then copy the jar file to the relevant location for one of the server modes: Standalone, Apache Tomcat, or WebLogic Server.

Note:

The minimum required version of MySQL Connector/J is 8.0.27.

The OCI YUM RPM distribution of ORDS creates a symbolic link to the OCI YUM mysql-connector-java JDBC jar.

OCI YUM RPM


-- Install MySQL Connector/J community edition
sudo yum install mysql-connector-java
 
-- Confirm JDBC jar is installed
ls -l /usr/share/java/mysql-connector-java.jar
 
-- Install ORDS from OCI YUM repository
sudo yum install ords
 
-- Note that ORDS RPM install will create a symbolic link to ORDS installation lib/ext/ directory
ls -l /opt/oracle/ords/lib/ext/

4.1.2.1.1 ORDS Running in Standalone Mode

To be in the runtime classpath when running ORDS in a standalone mode, the MySQL JDBC jar must be first added to the Extension folder. The Extension folder is lib/ext directory in the ORDS distribution and is created through the OCI YUM RPM installation process that is outlined in the preceding section.

4.1.2.1.2 ORDS Deployed in Apache Tomcat

Note:

When you are using Apache Tomcat, to avoid getting java.sql.SQLException: No suitable driver errors, it is necessary to explicitly set the JDBC Driver classname in the pool.

To set the JDBC driver classname in the pool, run the following command:

ords config --db-pool mysql set jdbc.driverName com.mysql.cj.jdbc.Driver

To be in the runtime classpath when ORDS is deployed in Apache Tomcat, the MySQL JDBC jar must be added to the server classpath or in the deployed web application. Adding the jar to the server classpath can be achieved in a number of ways, the most common method is adding the jar file to the $CATALINA_HOME/lib directory.

See Apache Tomcat documentation for more options and guidelines to determine the most appropriate deployment environment.

To include the JDBC jar in the deployed web application, ensure that it is in the the lib/ext/ folder and uses the ords war command to create a deployable web application archive file which has the config.url context parameter explicitly set and any jar files from lib/ext folder included.

4.1.2.1.3 ORDS Deployed in Weblogic Server

To be in the runtime classpath when ORDS is deployed in WebLogic Server, the MySQL JDBC jarfile must be added to the server classpath or in the deployed web application. One approach to add the jar file to the server classpath is to specify the jar location in the WEBLOGIC_CLASSPATH environment variable in the commEnv.cmd/sh script.

See WebLogic Server documentation for more options and guidelines to determine the most appropriate deployment environment.

4.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 settings.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.

4.3 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:

Create a new user using external authentication
Set up the environment variables
Provide a valid ticket
Add ORDS pool settings

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

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>
```
Provide a Valid Ticket
Provide a valid ticket in the ticket cache to get authenticated when connecting to the Oracle Database.
kinit <principal>
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

4.4 Authorizing Oracle REST Data Services to Access Oracle Data Guard Protected Users

To access the database schema objects that are protected by an Oracle Data Vault Realm, it is necessary to grant a proxy user authorization to the Oracle REST Data Services Public User.

The following example authorizes the Oracle REST Data Services Public User, ORDS_PUBLIC_USER to proxy the database HR user:

begin
  DBMS_MACADM.AUTHORIZE_PROXY_USER('ORDS_PUBLIC_USER','HR');
end;
/

4.5 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:

Run the following command:
ords --config <configuration_folder> config [--db-pool <pool_name>] set restEnabledSql.active true
Restart Oracle REST Data Services.

4.6 Configuring the Maximum Number of Rows Returned from a Query

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

Run the following command:
ords --config <configuration_folder> config set [--db-pool <pool_name>] misc.pagination.maxRows <number>

Note:
The default value for misc.pagination.maxRows is 10000.
Restart Oracle REST Data Services.

4.7 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:

Run the following command:
ords --config <configuration_folder> config [--db-pool <pool_name>] set icap.port <number> ords --config <configuration_folder> config [--db-pool <pool_name>] set icap.server <name_or_ip>
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

4.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,

Run the following command:

ords --config /path/to/conf config set error.externalPath /path/to/error/pages/folder/

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.
Restart Oracle REST Data Services

Example 4-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:

Create a file named 404.html.
Save it under /usr/local/share/ords/error-pages/ folder.
Configure the error.externalPath parameter to point to /usr/local/share/ords/errro-pages/ folder.
Restart Oracle REST Data Services.

4.9 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.

4.9.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 4-2 Using Grant command

GRANT ORDS_ADMINISTRATOR_ROLE TO HR_ADMIN;

Example 4-3 Using ORDS_ADMIN package method

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

4.9.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 4-4 Using REVOKE command

REVOKE ORDS_ADMINSTRATOR_ROLE FROM HR_ADMIN;

Example 4-5 Using ORDS_ADMIN package method

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

4.10 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.

4.10.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 4-6 Using Grant command

GRANT ORDS_RUNTIME_ROLE TO ORDS_PUBLIC_USER_2;

Example 4-7 Using ORDS_ADMIN package method

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

4.10.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 4-8 Using REVOKE command

REVOKE ORDS_RUNTIME_ROLE FROM ORDS_RUNTIME_USER_2;

Example 4-9 Using ORDS_ADMIN package method

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

4.11 Using OAuth2 in Non-HTTPS Environments

RESTful Services can be protected with the OAuth2 protocol to control access to nonpublic data. To prevent data snooping, OAuth2 requires all requests involved in the OAuth2 authentication process to be transported using HTTPS. The default behavior of Oracle REST Data Services is to verify that all OAuth2 related requests have been received using HTTPS. It will refuse to service any such requests received over HTTP, returning an HTTP status code of 403 Forbidden.

This default behavior can be disabled in environments where HTTPS is not available as follows:

Locate the folder where the Oracle REST Data Services configuration is stored, for example: /path/to/conf
Execute the following command:
ords --config /path/to/conf config set security.verifySSL false
Restart Oracle REST Data Services if it is running.

Note that it is only appropriate to use this setting in development or test environments. It is never appropriate to use this setting in production environments because it will result in user credentials being passed in clear text.

Note:

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

4.12 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 4-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.