Enabling ORDS Database API

11 Enabling ORDS Database API

This section describes how to enable the Oracle REST Data Services (ORDS) Database API.

ORDS database API is a database management and monitoring REST API embedded into Oracle REST Data Services. Depending on the database version and configuration, ORDS database API provides services such as manage pluggable databases, export data and review database performance. By default, the ORDS database API feature is disabled when you install ORDS for the first time.

Basic Setup to Enable ORDS Database API
This section explains the basic setup to enable the ORDS database API.
Advanced Setup to Enable the ORDS Database API
This section describes the configuration options for using ORDS database API with various database topologies.
Creating a Default Administrator
This section describes how to create and use the default administrator user for the non-CDB or PDB connections.
Configuration of Database API Environment Services
This section describes how to configure ORDS Database API environment services.
Configuration of Database API with Open Service Broker API Compatible Platforms
This section describes how to configure and use the ORDS database API with Open Service Broker API compatible platforms.

11.1 Basic Setup to Enable ORDS Database API

This section explains the basic setup to enable the ORDS database API.

To enable the ORDS database API, set the database.api.enabled property to true and then restart ORDS:

ords config set database.api.enabled true

To access the ORDS database API, you can use one of the following available authentication methods available:

Database authentication using database username and password
Through a mid-tier user with the SQL Administrator, or System Administrator role

Note:

There are certain endpoints that are accessible only by certain roles. The REST APIs for Oracle Database documentation provides information on which roles can access each endpoint.

To enable database authentication, you must set the restEnabledSql.active property to true as shown in the following code snippet and then restart ORDS:

ords config set restEnabledSql.active true

For the database authentication, ensure that the administrator schema is ORDS enabled and is granted with the DBA role in an 11gR2 environment or the PDB_DBA role for 12c and higher versions of the database before the schema is used to execute the database API queries in the database. This is done for each non-CDB or pluggable database in which you want to use the database. For more information, refer to "REST-Enabling the Oracle Database Schema" and "ORDS_ADMIN.ENABLE_SCHEMA" sections.

Note:

In the following example, sqlplus command-line utility is used to connect to the SALESPDB database as the system user to configure the PDBADMIN user in that database. The mechanism to connect to the database and performing the steps will differ depending on your environment settings.

For example, to use PDBADMIN schema, in the SALESPDB database for ORDS Database API services, use the following commands in the database.

sqlplus system@SALESPDB
GRANT PDB_DBA TO PDBADMIN;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(p_schema => 'PDBADMIN');
END;
/

The PDBADMIN user is now ready to use the ORDS database API services.

To list the tables in the database, send a GET request to https://<server>/ords/salespdb/pdbadmin/_/db-api/stable/database/objects/tables/

On request, you must provide the username and password. If you are using a browser, ORDS provides a link to login and authenticate the request. Once you are authenticated, your browser will have an access cookie, and you do not have to specify the user credentials until that cookie expires.

The same service can be accessed through command line utilities such as curl:

curl --user pdbadmin:password https://<server>/ords/salespdb/pdbadmin/_/db-api/stable/database/objects/tables/

An OpenAPI V3 document that describes the available ORDS database API services can be accessed at https://<server>/ords/<my database>/<my admin schema>/_/db-api/stable/metadata-catalog/openapi.json. With the exception of https://<server>/ords/<my database>/<my admin schema>/_/db-api/stable/databases/pdbs/, all other ORDS database API services are made available.

Related Topics

Parent topic: Enabling ORDS Database API

11.2 Advanced Setup to Enable the ORDS Database API

This section describes the configuration options for using ORDS database API with various database topologies.

Note:

Disabling management services: When the value of database.api.management.services.disabled property is set to true, the following ORDS Database API services are disabled:

DBCA Jobs: DELETE, GET and POST
DBCA Templates: GET
Oracle Home Environment: GET
PDB Lifecycle: DELETE, GET, POST
Open Service Broker- DELETE, GET and PUT

Pluggable Database Lifecycle Management
This section describes how to enable the Pluggable Database (PDB) lifecycle management operations. Pluggable Database management is performed in the Container Database (CDB) and includes create, clone, plug, unplug and delete operations.
Disabling PDB Lifecycle Management
This section describes how to disable the PDB lifecycle management services.

Parent topic: Enabling ORDS Database API

11.2.1 Pluggable Database Lifecycle Management

This section describes how to enable the Pluggable Database (PDB) lifecycle management operations. Pluggable Database management is performed in the Container Database (CDB) and includes create, clone, plug, unplug and delete operations.

You cannot have an ORDS enabled schema in the container database. To perform the PDB lifecycle management operations, the default CDB administrator credentials, db.cdb.adminUser and db.cdb.adminUser.password must be defined in the connection pool. In this case, specifying an user schema in the URI is not required.

To define the default CDB administrator credentials, perform the following steps:

Create the CDB administrator user and grant the SYSDBA privilege. In this example, the user is called C##DBAPI_CDB_ADMIN. However, any suitable common user name can be used.
```
CREATE USER C##DBAPI_CDB_ADMIN IDENTIFIED BY <PASSWORD>;
GRANT SYSDBA TO C##DBAPI_CDB_ADMIN CONTAINER = ALL;
```

Set the db.cdb.adminUser and db.cdb.adminUser.password properties for the connection pool.

ords config set db.cdb.adminUser "C##DBAPI_CDB_ADMIN as SYSDBA"
ords config secret db.cdb.adminUser.password

The ORDS role, SQL Administrator must be used to access the https://<server>/ords/_/db-api/stable/database/pdbs/ services.

Parent topic: Advanced Setup to Enable the ORDS Database API

11.2.2 Disabling PDB Lifecycle Management

This section describes how to disable the PDB lifecycle management services.

You can enable ORDS database API and disable the PDB related services at https://<server>/ords/_/db-api/stable/databases/pdbs/.

When the optional CDB administrator credentials are not set, a HTTP 503 Service Unavailable response is produced if a user attempts to access https://<server>/ords/_/db-api/stable/databases/pdbs/.

To clearly indicate that the PDB operations are disabled for the ORDS installation, set the database.api.management.services.disabled property to true as shown in the following code snippet and then restart ORDS:

ords config set database.api.management.services.disabled true

This produces a response, HTTP 503 Service Unavailable with an explanatory reason.

Parent topic: Advanced Setup to Enable the ORDS Database API

11.3 Creating a Default Administrator

This section describes how to create and use the default administrator user for the non-CDB or PDB connections.

The ORDS database API service operations are not schema specific. By configuring the default administrator credentials, db.adminUser and db.adminUser.password in the connection pool, you can execute the corresponding SQL statements as the default administrator user. The ORDS database API endpoints can be executed using a specified ORDS enabled schema if the schema has the DBA role. However, it is not necessary to do so when the default administrator credentials are configured.

Note:

The user credentials must be the same across all the pluggable databases and therefore it is recommended to create the common user in the CDB.

To create the default administrator and grant the DBA role, perform the following steps:

Create the default administrator user and grant the DBA role. In this example, the user is called C##_DBAPI_DEFAULT_ADMIN. However, any suitable common user name can be used as shown in the following code snippet:
```
CREATE USER C##_DBAPI_DEFAULT_ADMIN IDENTIFIED BY <PASSWORD> CONTAINER = ALL;
```
```
GRANT DBA TO C##_DBAPI_DEFAULT_ADMIN CONTAINER = ALL;
```
Set the db.adminUser and db.adminUser.password properties for the connection pool as shown in the following code snipet:
```
ords config set db.adminUser C##_DBAPI_DEFAULT_ADMIN
ords config secret db.adminUser.password
```

A schema is not required to be provided in the URI request.

For example, https://<server>/ords/salespdb/_/db-api/stable/database/datapump/jobs/ lists all the data pump jobs in the salespdb, and queries in that database are executed as the db.adminUser user.

The ORDS role SQL Administrator, is required to use the database API services.

Parent topic: Enabling ORDS Database API

11.4 Configuration of Database API Environment Services

This section describes how to configure ORDS Database API environment services.

Starting with ORDS 19.2 release, on a system with ORDS installed, you can perform the set of environment services operations.

For example, the following endpoint lists all the databases discovered in the Oracle Home:

https://<server>/ords/_/db-api/stable/environment/databases/

You must have the ORDS System Administrator role to use the ORDS database API environment services. The environment services provide information about the database Oracle Home on the host machine and a RESTful interface to the Oracle Database Configuration Assistant to create or delete the databases.

Similar to pluggable database lifecycle management, the environment services can be disabled.

To disable the environment services, set the database.api.management.services.disabled property to true as follows and then restart ORDS:

ords config
                set database.api.management.services.disabled true

Parent topic: Enabling ORDS Database API

11.5 Configuration of Database API with Open Service Broker API Compatible Platforms

This section describes how to configure and use the ORDS database API with Open Service Broker API compatible platforms.

The ORDS database API provides a service broker for each registered connection pool. Service brokers compliant with the Open Service Broker API specification, allow platforms to provision a new instance of a service. With ORDS as an Open Service Broker to an Oracle database, customers can provision pluggable databases and database users. The nature of the database dictates the service offering that the ORDS database API provides.

Table 11-1 Open Service Broker Service Catalog

Database Type Service Plans Prerequisites

Container Database

Database Type	Service	Plans	Prerequisites
Container Database	`create-pluggable-database`. Create a new pluggable database in the Oracle multitenant container database.	`clone-database` Create a new pluggable database in the container database by cloning another local pluggable database. Any ORDS REST enabled schemas in the source database is REST enabled in the new database. `create-database` Create a new pluggable database from `PDB$SEED`. The pluggable database administrator account is automatically rest enabled.	Pluggable database lifecycle management must be configured.
Non-Container or Pluggable Database	`create-oracle-database-user` Create and configure an Oracle database user with an account through which the user can log in to the database.	`create-standard-database-user` Create an Oracle database user with the specified roles and privileges. The objects of the user are stored in the default database tablespace. The temporary segments of the user are stored in the default temporary database tablespace. `create-ords-enabled-database-user` Create an Oracle database user with an ORDS enabled schema. The objects of the user are stored in the default database tablespace. The temporary segments of the user are stored in the default temporary database tablespace.	None

create-pluggable-database.

Create a new pluggable database in the Oracle multitenant container database.

clone-database

Create a new pluggable database in the container database by cloning another local pluggable database. Any ORDS REST enabled schemas in the source database is REST enabled in the new database.

create-database

Create a new pluggable database from PDB$SEED. The pluggable database administrator account is automatically rest enabled.

Pluggable database lifecycle management must be configured.

Non-Container or Pluggable Database

create-oracle-database-user

Create and configure an Oracle database user with an account through which the user can log in to the database.

create-standard-database-user

Create an Oracle database user with the specified roles and privileges. The objects of the user are stored in the default database tablespace. The temporary segments of the user are stored in the default temporary database tablespace.

create-ords-enabled-database-user

Create an Oracle database user with an ORDS enabled schema. The objects of the user are stored in the default database tablespace. The temporary segments of the user are stored in the default temporary database tablespace.

None

To register the service broker URL with your Open Service Broker compliant platform, it depends on how the pool is registered with ORDS and the database type. Oracle recommends that you use HTTPS with Open Service Broker endpoints. The process of registering a service broker differs depending on the platform.

The Service Broker URL for ORDS follows the following pattern:

create-oracle-database-user
To register the non-CDB or PDB service catalog, you must use the service broker URL for the non-CDB or PDB pool. The format is as follows:

https://<server>/ords/<my database>/<my admin schema>/_/db-api/stable/openservicebroker/

Using the SALESPDB example with PDBADMIN as an ORDS enabled schema, the URL is as follows:

https://<server>/ords/salespdb/pdbadmin/_/db-api/stable/openservicebroker/

Note:
<my database> can be the default database connection.
This configuration is common when customers are using ORDS directly with a single database. With this configuration, the example URL is https://<server>/ords/pdbadmin_/db-api/stable/openservicebroker/.
Supported Open Service Broker Operations
ORDS database API supports the synchronous provisioning operation. Other Open Service Broker operations such as deprovisioning and service binding are not supported.
Disabling the Service Broker for a Specific Pool
To disable the Open Service Broker services available for a specific pool, set the feature. openservicebroker.exclude property to true by specifying the pool name as follows:
ords config --db-pool <pool-name> set feature.openservicebroker.exclude true
And then restart ORDS.

When you use ORDS directly with a container database and pluggable database mapping at runtime, disabling the Open Service Broker for the container disables the broker for all pluggable databases in the container. In such case, the configuration is defined in the container database pool configuration file.

Parent topic: Enabling ORDS Database API