Configuring Oracle REST Data Services for Multiple Databases

3 Configuring Oracle REST Data Services for 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.

3.1 Deploying ORDS with Local Configuration

By default, ORDS configuration is persisted in the ORDS configuration directory files. These files are best managed using the ords config command line command and can be modified manually.

3.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:

Configuring the database connection information
Configuring which requests are routed to which database

3.1.2 Configuring Additional Databases

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

Tip:

To see full help for the install command type:

ords install --help

For adding additional database connection pool, enter the following command:

ords --config <configuration folder> install

Refer to Interactive Command-Line Interface Installation to create an additional database connecion pool.

See Also:

3.1.3 Advanced Routing

By default, additional databases are mapped using the database pool name specified while installing ORDS. Alternatively, custom mapping can be performed based on a path prefix or a hostname as described in the following sections.

Note:

If custom mapping is configured, then default mapping with database pool name is not enabled.

See Also:

Configuring Additional Databases

3.1.4 Routing Based on the Request Path

You can create the request routing rules based on the request path using the paths file. To do this, perform the following steps:

Open or create a file <ords_config_folder>/databases/<database_name>/paths
Enter the paths that you want to be served by this database.

Example location of the paths file

ords_conf/
+-- databases/
|   +-- db1/
|   |   +-- wallet/
|   |   +-- paths
|   |   +-- pool.xml
|   +-- db2/
|       +-- wallet/
|       +-- paths
|       +-- pool.xml
+-- globals/

Example: ords_conf/databases/db1/paths

sales

Example: ords_conf/databases/db2/pathsords_conf/databases/db2/paths

support
/finance/department1

The previous rules match all the following requests:

http://example.com/ords/sales --> db1
http://example.com/ords/sales/leads --> db1
https://www.example.com/ords/sales/forecasting.report?month=jan --> db1 (If www.example.com resolves to the same system as example.com.)

http://example.com/ords/support --> db2
http://example.com/ords/finance/department1 --> db2

3.1.5 Routing Based on the Request Hostname

You can create request routing rules based on the hostname using the hostnames file. To do this, perform the following steps:

Open or create a file <ords_config_folder>/databases/<database_name>/hostnames.
Add the hostnames you want to be served by this database.

Example location of hostnames files:

ords_conf/
+-- databases/
|   +-- db1/
|   |   +-- wallet/
|   |   +-- hostnames
|   |   +-- pool.xml
|   +-- db2/
|       +-- wallet/
|       +-- hostnames
|       +-- pool.xml
+-- globals/

Example: ords_conf/databases/db1/hostnames

www.example.com
example.org

Example ords_conf/databases/db2/hostnames

foo.bar.com

The preceding rules match the requests to their corresponding database connection as follows:

http://www.example.com/ords/ --> db1
http://example.org:8080/ords/f?p=1:1 --> db1
https://foo.bar.com/ords/myschema/resource --> db2

The preceding rules do not match following requests:

http://example.com/ords/ (The hostname is missing the www.)
http://foo.bar.net/ords/myschema/resource --> db2 (hostname is different

3.2 Deploying ORDS with Central Configuration Server

This section describes how to deploy ORDS using a central configuration server.

Overview

By default, Oracle REST Data Services (ORDS) configuration resides locally in files such as the global/settings.xml and databases/default/pool.xml within an ORDS configuration directory. Alternatively, ORDS can acquire its configuration from a REST API called central configuration server API .

The following are the benefits of using a central configuration server API:

Centrally managed configuration provides a single place to update, manage, and secure ORDS configuration.
ORDS pools are dynamically loaded as required, this helps in quick startup time for ORDS.

The Central Configuration Server OpenAPI specification defines the following two REST end points:

Global Configuration Resource
Pool Configuration Resource

Typically, a central configuration server is required only when you are managing multiple ORDS nodes or Database Pool configurations.

Prerequisites

Secure REST API that implements the specified central configuration server API
ORDS global and database pool information is defined in the OpenAPI specification
OAuth2.0 authorization server (for issuing access tokens)
Ability to create an Oracle wallet to store the credentials for accessing the secure central configuration

See Also:

ORDS Central Configuration OpenAPI

3.2.1 OpenAPI Specification

Refer to ORDS Central Configuration OpenAPI

3.2.2 Database Mapping

When ORDS is deployed using central configuration, only the global configuration settings are initially retrieved. Appropriate pool configuration is retrieved and cached in the subsequent requests only after a request is made to ORDS for which it requires a Database connection. The global configuration resource provides a search HREF template for the pool configuration resource. ORDS uses this template to request the pool configuration. The search HREF is templated, meaning the {host} string is replaced by a value that identifies the specific pool.

You can use one of the following two methods to replace the {host} string to specify the appropriate pool configuration:

Request Host Method
Header Method

3.2.2.1 Request Host Method

Request host method is the default method to replace the {host} string in the search href. The host of the ORDS request is used.

This method is useful when the sub domain of the ORDS service can be used to identify which pool configuration to use.

Examples

Global Configuration: The href templated search value provided by the Global configuration

 href="https://central-config.example.com:8585/central/v1/config/pool/{host}"

Request to ORDS:

GET: https://mydatabase.servername.com/ords/hr/employees/

Where, mydatabase.servername.com is the request host.

ORDS requests the pool configuration from:

https://central-config.example.com:8585/central/v1/config/pool/mydatabase.servername.com

3.2.2.2 Header Method

If the sub domain cannot be used to denote the appropriate pool, then a request header can be used. Using the security.externalHostMappingHeader setting, the Global configuration specifies the header you want to use.

Examples

Global Configuration:

The href templated search value provided by the Global configuration:

href="https://central-config.example.com:8585/central/v1/config/pool/{host}"

Request to ORDS:

GET: -Header "poolname: mydatabase" https://www.servername.com/ords/hr/employees/

ORDS requests the pool configuration from:

https://central-config.example.com:8585/central/v1/config/pool/mydatabase

3.2.3 Global Configuration Settings

In an ORDS central configuration deployment, global configuration settings must meet the following requirements:

Must be in a JSON format
The global settings must be defined in the settings object.
Include a related Search link ending with the {host} template parameter to describe the Pool configuration resource.

Example response for the ORDS global settings

{
  "settings": {
    "restEnabledSql.active": true,
    "feature.sdw": true,
    "security.externalHostMappingHeader": "poolname"
  },
  "links": [
    [
      {
        "rel": "collection",
        "href": "https://central-config.example.com:8585/central/v1/config/"
      },
      {
        "rel": "self",
        "href": "https://central-config.example.com:8585/central/v1/config/"
      },
      {
        "rel": "search",
        "href": "https://central-config.example.com:8585/central/v1/config/pool/{host}",
        "templated": true
      }
    ]
  ]
}

3.2.4 Database Pool Configuration Settings

In an ORDS central configuration deployment, pool configuration settings must meet the following requirements:

Must be in a JSON format
The pool settings defined in the database.pool.settings object.
When you are configuring a database pool for the Oracle Autonomous Database, pool configuration files must include the contents of its db.wallet.zip configuration property encoded in Base64

Example 3-1 db.wallet.zip

Example pool configuration response with the db.wallet.zip file providing a base64 representation of an ADB-S instance wallet.

Note:

The db.username and db.password can be provided as settings. However, it is a best practice to include the credentials in the ADB-S instance wallet before converting it to base64.

{
  "database": {
    "pool": {
      "name": "mydatabase",
      "settings": {
        "db.wallet.zip.service":"mydb_low",
        "db.wallet.zip": "UEsDBBQACAgIAOMid1IAAAAAAAAAAAAAAAALAAAAY3dhbGxldC5zc28BdRuH4TjA...",
        "feature.sdw": true,
        "plsql.gateway.mode": "proxied",
        "restEnabledSql.active": true
      }
    }
  }
}

Example 3-2 db.customURL

Example pool configuration response with the db.customURL providing the pool connection details.

{
  "database": {
    "pool": {
      "name": "mydatabase",
      "settings": {
        "db.username" : "ORDS_PUBLIC_USER",
        "db.password" : "ORDS PUBLIC USER PASSWORD",
        "db.connectionType" : "customurl",
        "db.customURL" : "jdbc:oracle:thin:@(description=(retry_count=3)(address_list=(load_balance=on)
         (address=(protocol=tcp)(host=adb.ap-sydney-1.oraclecloud.com)(port=1521)))
         (connect_data=(service_name=database_pool_1_high.adb.oraclecloud.com)))",
        "feature.sdw": true,
        "plsql.gateway.mode": "proxied",
        "restEnabledSql.active": true
      }
    }
  }
}

3.2.4.1 Using eTag

Pool configuration responses should include an eTag. To identify any changes to a pool configuration, ORDS uses the eTag. If an eTag remains unchanged, then ORDS does not refresh the configuration settings of the pool.

3.2.4.2 Adding Credentials to an Instance Wallet

This section describes how to add credentials to an instance wallet.

Adding the database credentials for an ORDS runtime user can be provided using the db.username and db.password settings. However, a better method is to include the credentials in the the ADB-S instance wallet and then provide that in base64 format using the db.wallet.zip setting.

The following command can be used to include the ORDS runtime user credentials. When prompted, provide the instance wallet password.

mkstore -wrl <path to the instance wallet> -createCredential <servicename> <ORDS Runtime user name>
      <ORDS Runtime user password>

3.2.5 Security

The Central Configuration API can be secured using basic authentication or OAuth2. The credentials and other information required to acquire an access token or make basic authentication requests are provided using an ORDS credentials wallet.

ORDS supports the following OAuth2.0 grant types:

Resource Owner Password Credentials Grant
Client Credentials Grant

Credentials are stored as a secret in the Oracle wallet. The mkstore utility can be used to create the ORDS credentials wallet.

Note:

SQLcl provides a mkstore command which can be used to create the appropriate ORDS credentials wallet or modify an instance ADB-S instance wallet.

Table 3-1 ORDS Credential Wallet Storage

Secret Alias	Secret Value	Description
`oauth:client_credentials_mode:[Server Name]`	Value must be one of the following: `basic` `request_body`	Determines how the client credentials are added to the token request. Default value is `basic`
`oauth:client_id:[Server Name]`	`client_id` of the client application	Used with `grant_type=client_credentials`
`oauth:client_secret:[Server Name]`	Client secret of the client application	Used where`grant_type=client_credentials`
`oauth:grant_type:[Server Name]`	When used, must be one of the following: `client_credentials` `password`	The value defaults to `password` when `oauth:username:[hostname]` is included. Otherwise, the default value is `client_credentials`
`oauth:password:[Server Name]`	`password` of the user who needs to be authenticated	Used with `grant_type=password`
`oauth:scope:[Server Name]`	Conforms to Access Scope specification as defined in Access Token Scope	OAuth 2.0 scopes to the request access
`oauth:token_endpoint:[Server Name]`	Absolute URL of an access token endpoint as defined in Token Endpoint	ORDS client uses the `POST` method when accessing the token requests
`oauth:username:[Server Name]`	`username` of the user who needs to be authenticated	Used when `grant_type=password`

3.2.5.1 Demonstration to Create an Entry of a Wallet for OAuth2.0 Authentication

The example in this section demonstrates how to set up a wallet with the mkstore utility for an OAuth2.0 client credentials grant type.

Setting up a wallet with the SQLcl mkstore utility

sql /nolog
SQL> mkstore -wrl /tmp/wallet -create

Creating an entry of a wallet for OAuth2.0 Authentication

The Global configuration resource server in this example is central-config.example.com.

sql /nolog
SQL> mkstore -wrl /tmp/wallet -createEntry oauth:grant_type:central-config.example.com client_credentials
SQL> mkstore -wrl /tmp/wallet -createEntry oauth:token_endpoint:central-config.example.com https://myauthserver.com/oauth/token
SQL> mkstore -wrl /tmp/wallet -createEntry oauth:client_id:central-config.example.com SeqlyQGW4iKgHd@cQ4Xnkg..
SQL> mkstore -wrl /tmp/wallet -createEntry oauth:client_secret:central-config.example.com SuBNKFsft£t924Dsfgsh..

3.2.6 Starting ORDS in a Central Configuration Deployment

ORDS recognizes that a central configuration deployment is required when the following Java options are included in the ords serve command.

bin/ords --java-options "-Dconfig.url=[ORDS Global Configuration Resource Location] 
-Dconfig.wallet=[ORDS Credentials Wallet Location]" serve

Java Options

-Dconfig.url: Provides ORDS with the Global configuration resource and its server is used to reference secrets in the ORDS credentials wallet.
Example: -Dconfig.url=https://central-config.example.com:8585/central/v1/config/
-Dconfig.wallet: Provides ORDS with the ORDS credentials wallet location. Through auto login, it uses the secrets contained in the Oracle wallet to acquire an access token so that the Global and Pool resources can be accessed.
Example: -Dconfig.wallet=/tmp/wallet

You will receive a notification in the command prompt from where the ords serve command was issued, indicating that ORDS is running under a central configuration deployment. ORDS then waits for the incoming HTTPS requests for the database pools that have been configured.

3.2.6.1 Satisfying the HTTPS Requests

ORDS waits for the incoming HTTPS requests for currently configured database pools.

Once the incoming requests arrive, ORDS first determines if the database pool configuration information is cached. If the configuration settings are not available, then ORDS makes a request to the central configuration server to retrieve the pool configuration. The pool is then initialized and the request is handled.