Enabling ORDS Database API

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

10.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 value to true as shown in the following code snippet and then restart ORDS:

java -jar ords.war set-property database.api.enabled true

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

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

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

java -jar ords.war set-property jdbc.auth.enabled 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 example, execute the following command to use PDBADMIN schema in the SALESPDB database for the ORDS database API services:

GRANT PDB_DBA TO PDBADMIN;
ORDS_ADMIN.ENABLE_SCHEMA(p_schema => 'PDBADMIN');

The ORDS database API services are now ready for use.

To list the tablespaces, send a GET request tohttps://<server>/ords/salespdb/pdbadmin/_/db-api/stable/database/storage/tablespaces/.

On request, you must provide the username and password through basic authentication. If you are using a browser, ORDS will provide a link to login and authenticate the request. Once you are authenticated, your browser will have an access cookie, which is valid for next 30 minutes.

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.

10.2 Advanced Setup to Enable the ORDS Database API

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

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

echo db.cdb.adminUser=C##DBAPI_CDB_ADMIN as SYSDBA > cdbAdmin.properties
echo db.cdb.adminUser.password=<PASSWORD> >> cdbAdmin.properties
java -jar ords.war set-properties --conf apex_pu cdbAdmin.properties
rm cdbAdmin.properties

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

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

java -jar ords.war set-property database.api.management.services.disabled true

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

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

echo db.adminUser=C##_DBAPI_PDB_ADMIN > pdbAdmin.properties
    echo db.adminUser.password=<PASSWORD> >> pdbAdmin.properties
    java -jar ords.war set-properties --conf apex_pu pdbAdmin.properties
    rm pdbAdmin.properties

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.