8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Database API - Setup

The Oracle REST Data Services (ORDS) database API allows us to perform some database administration via REST web service calls. This article covers the basic setup.

Related articles.

Assumptions

This article assumes the following.

Enable ORDS Database API

If you have followed this ORDS installation article you may already have the ORDS Database API enabled.

During the installation the following properties can be included in the properties file to enable REST Enabled SQL and the ORDS Database API.

restEnabledSql.active=true
database.api.enabled=true

For an existing installation, the following commands will set these properties in the "defaults.xml" file.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war set-property restEnabledSql.active true
$JAVA_HOME/bin/java -jar ords.war set-property database.api.enabled true

Remember to restart ORDS after altering the settings. We are using Tomcat to run ORDS, so we would restart Tomcat to restart ORDS.

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh

If you are running ORDS in standalone mode, you will then need to restart ORDS, as described here.

There is an additional level of control using the database.api.management.services.disabled parameter, which is set to false by default. Setting this to true during the installation, or using the above command syntax will disable the following services.

Database Credentials

There are two basic approaches for authentication to the database APIs. I can't see it mentioned in the documentation, but it appears certain APIs are specific about which authentication method they will accept. You may well need to configure both types to have access to the full range of APIs.

I believe this is a bug that will be fixed in a future version. If you are using version higher than 20.2, this may be fixed.

Remember also the PDB Lifecycle Management APIs require a slightly different setup, and will only work with ORDS installed in the root container. Their usage is described here.

Database Credentials : Default Administrator

Using a default administrator means we can use application server credentials, rather than having to expose the credentials of the privileged database user to the person calling the APIs.

We create a default user in the root container with the DBA and PDB_DBA roles.

conn / as sysdba
create user c##pdb_default_admin_user identified by DefaultAdminUserPassword1;
grant create session to c##pdb_default_admin_user container=all;
grant dba, pdb_dba to c##pdb_default_admin_user container=all;

We need to add these credentials to the ORDS connection pool. For the default connection pool we would do something like the following. We add the credential parameters to a properties file, set the properties for the connection pool, then delete the properties file.

cat > /tmp/pdb_default_admin_user.properties <<EOF
db.adminUser=c##pdb_default_admin_user
db.adminUser.password=DefaultAdminUserPassword1
EOF

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war set-properties --conf apex_pu /tmp/pdb_default_admin_user.properties
rm /tmp/pdb_default_admin_user.properties

Remember to restart ORDS after altering the settings.

To access the APIs using the default administrator, we need application server credentials mapped to the "System Administrator" role. How you achieve this will vary depending on the way you are running ORDS.

If you are running ORDS under Tomcat, you must add the relevant credentials to the "$CATALINA_BASE/conf/tomcat-users.xml" file. The example below create a new "System Administrator" role and maps a user to that role.

  <role rolename="System Administrator"/>
  <user username="system_admin" password="system_admin_password1" roles="System Administrator"/>

You must then restart Tomcat.

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh

If you are running in standalone mode, you may wish to use the file-based repository. You can create a similar user using the following command, which will prompt for a password.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war user system_admin "System Administrator"

You will then need to restart ORDS, as described here.

Database Credentials : ORDS Enabled Schema

Alternatively we can use an ORDS enabled schema. Access to the APIs will use the credentials of the ORDS enabled schema.

If you have used ORDS before, the ORDS enabled schema approach should be familiar. We create a user in the PDB, give it the DBA and PDB_DBA roles, then enable the schema for ORDS.

conn / as sysdba
alter session set container=pdb1;

create user dbapi_user identified by DbApiUserPassword1;
grant dba, pdb_dba to dbapi_user;

conn dbapi_user/DbApiUserPassword1@//localhost:1521/pdb1

begin
  ords.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'dbapi_user',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'dbapi_user',
    p_auto_rest_auth      => FALSE
  );
    
  commit;
end;
/

This approach doesn't require a restart of ORDS.

Basic Usage

The REST endpoints are described here. The examples below use the curl utility to give an idea of their basic usage.

The examples you are likely to try will include a few common elements.

There are too many variants of the APIs to describe in one article, so we will just show a few to demonstrate the authentication. The URIs used will be a little different depending on if we are using a CDB or PDB installation.

CDB Installation

In this first example we use the "_/db-api/stable/environment/homes/" URI to get a list of the Oracle homes on the server. We use the application server credentials, which map to the default administrator credentials.

curl -ks -X GET \
     --user system_admin:system_admin_password1 \
     https://localhost:8443/ords/_/db-api/stable/environment/homes/ | python3 -mjson.tool
{
    "items": [
        {
            "name": "OraDB19Home1",
            "version": "19.7.0.0.0",
            "default": true,
            "read_only_home": false,
            "links": [
                {
                    "rel": "self",
                    "href": "https://localhost:8443/ords/_/db-api/stable/environment/homes/OraDB19Home1/"
                }
            ]
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/_/db-api/stable/environment/homes/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/_/db-api/stable/metadata-catalog/"
        }
    ]
}

In this example we use the "{pdb-name}/_/db-api/stable/environment/databases/" URI to get a list of the instances on the server. This URL includes the default PDB mapping provided by a CDB installation of ORDS. Once again we use the application server credentials, which map to the default administrator credentials.

curl -ks -X GET \
     --user system_admin:system_admin_password1 \
     https://localhost:8443/ords/pdb1/_/db-api/stable/environment/databases/ | python3 -mjson.tool
{
    "items": [
        {
            "name": "cdb1",
            "type": "CDB",
            "links": [
                {
                    "rel": "self",
                    "href": "https://localhost:8443/ords/pdb1/_/db-api/stable/environment/databases/cdb1"
                }
            ]
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/pdb1/_/db-api/stable/environment/databases/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/pdb1/_/db-api/stable/metadata-catalog/"
        }
    ]
}

In this example we use the "{pdb-name}/{schema-alias}_/db-api/stable/database/version" URI to the version of the database. This time we use the schema credentials associated with the ORDS enabled schema.

curl -ks -X GET \
     --user dbapi_user:DbApiUserPassword1 \
     https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/database/version | python3 -mjson.tool
{
    "inst_id": 1,
    "instance_number": 1,
    "instance_name": "cdb1",
    "host_name": "localhost.localdomain",
    "version": "19.0.0.0.0",
    "version_legacy": "19.0.0.0.0",
    "version_full": "19.7.0.0.0",
    "startup_time": "2020-09-28T12:02:47Z",
    "status": "OPEN",
    "parallel": "NO",
    "thread#": 1,
    "archiver": "STOPPED",
    "log_switch_wait": null,
    "logins": "ALLOWED",
    "shutdown_pending": "NO",
    "database_status": "ACTIVE",
    "instance_role": "PRIMARY_INSTANCE",
    "active_state": "NORMAL",
    "blocked": "NO",
    "con_id": 0,
    "instance_mode": "REGULAR",
    "edition": "EE",
    "family": null,
    "database_type": "SINGLE",
    "instance_version": [
        {
            "banner": "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production"
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/database/version"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/database/"
        }
    ]
}

PDB Installation

In this first example we use the "_/db-api/stable/environment/homes/" URI to get a list of the Oracle homes on the server. We use the application server credentials, which map to the default administrator credentials. This matches what we did in the CDB installation.

curl -ks -X GET \
     --user system_admin:system_admin_password1 \
     https://localhost:8443/ords/_/db-api/stable/environment/homes/ | python3 -mjson.tool
{
    "items": [
        {
            "name": "OraDB19Home1",
            "version": "19.7.0.0.0",
            "default": true,
            "read_only_home": false,
            "links": [
                {
                    "rel": "self",
                    "href": "https://localhost:8443/ords/_/db-api/stable/environment/homes/OraDB19Home1/"
                }
            ]
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/_/db-api/stable/environment/homes/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/_/db-api/stable/metadata-catalog/"
        }
    ]
}

In this example we use the "{schema-alias}_/db-api/stable/database/version" URI to the version of the database. We don't include the PDB mapping, as we are already connected to the PDB. This time we use the schema credentials associated with the ORDS enabled schema.

curl -ks -X GET \
     --user dbapi_user:DbApiUserPassword1 \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/version | python3 -mjson.tool
{
    "inst_id": 1,
    "instance_number": 1,
    "instance_name": "cdb1",
    "host_name": "localhost.localdomain",
    "version": "19.0.0.0.0",
    "version_legacy": "19.0.0.0.0",
    "version_full": "19.7.0.0.0",
    "startup_time": "2020-09-28T12:02:47Z",
    "status": "OPEN",
    "parallel": "NO",
    "thread#": 1,
    "archiver": "STOPPED",
    "log_switch_wait": null,
    "logins": "ALLOWED",
    "shutdown_pending": "NO",
    "database_status": "ACTIVE",
    "instance_role": "PRIMARY_INSTANCE",
    "active_state": "NORMAL",
    "blocked": "NO",
    "con_id": 0,
    "instance_mode": "REGULAR",
    "edition": "EE",
    "family": null,
    "database_type": "SINGLE",
    "instance_version": [
        {
            "banner": "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production"
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/database/version"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/database/"
        }
    ]
}

Thoughts

It is still early days for these APIs, but my biggest concern is the lack of granularity of access control. At the moment it feels like the APIs are an all or nothing thing. I can't see many companies being happy to let anyone other than a DBA access these. I feel like the access should be based on the database user privileges, not ORDS-specific settings.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.