DBA Hub

📋Steps in this guide1/10

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.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Assumptions

This article assumes the following.
2

Enable ORDS Database API (ORDS Version 22.1 Onward)

If you have followed this ORDS installation article you may already have the ORDS Database API enabled. During the installation the following command line arguments can be used to enable REST Enabled SQL and the ORDS Database API. For an existing installation, the following commands will set these attributes. Remember to restart ORDS after altering the settings. We are using Tomcat to run ORDS, so we would restart Tomcat to restart ORDS. 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 parameter, which is set to by default. Setting this to during the installation, or using the above command syntax will disable the following services. - DBCA Jobs - DBCA Templates - Oracle Home Environment - PDB Lifecycle - Open Service Broker

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
--feature-rest-enabled-sql true
--feature-db-api true

export ORDS_HOME=/u01/ords
export ORDS_CONFIG=/u01/config/ords
export PATH=${ORDS_HOME}/bin:${PATH}

ords --config ${ORDS_CONFIG} config set restEnabledSql.active true
ords --config ${ORDS_CONFIG} config set database.api.enabled true

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

Enable ORDS Database API (ORDS Versions 21.4 or Lower)

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. For an existing installation, the following commands will set these properties in the "defaults.xml" file. Remember to restart ORDS after altering the settings. We are using Tomcat to run ORDS, so we would restart Tomcat to restart ORDS. 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 parameter, which is set to by default. Setting this to during the installation, or using the above command syntax will disable the following services. - DBCA Jobs - DBCA Templates - Oracle Home Environment - PDB Lifecycle - Open Service Broker

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
restEnabledSql.active=true
database.api.enabled=true

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

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

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

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 and roles. 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. 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. You must then restart Tomcat. 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. You will then need to restart ORDS, as described here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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;

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

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

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

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

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 and roles, then enable the schema for ORDS. This approach doesn't require a restart of ORDS.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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;
/
7

Basic Usage

The REST endpoints are described here . The examples below use the utility to give an idea of their basic usage. The examples you are likely to try will include a few common elements. - : We are using a self-signed certificate, which sees as weak. The "-k" options tells to ignore the weak certificate. This is not necessary if you are using a certificate from a certificate authority. - : Run in silent mode, to remove additional output. - : Define the HTTP method used for the call. These will typically be , , , , but there are others. - : Enter the application server credentials defined earlier. - : The JSON output is minified, which is hard to read. This Python utility reformats the JSON to make is more readable. You would not use this for real calls. - : We use this option to pass a JSON document as a raw payload. This can be inline, or from a file using the "@" symbol. - : The POST and DELETE HTTP methods require the Content-Type header to be included. 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.
8

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
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/"
        }
    ]
}

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/"
        }
    ]
}

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/"
        }
    ]
}
9

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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/"
        }
    ]
}

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/"
        }
    ]
}
10

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: - Enabling ORDS Database API - About the REST APIs - Oracle REST Data Services (ORDS) : Database API - PDB Lifecyle Management - Oracle REST Data Services (ORDS) : All Articles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!