Installation of Oracle Restful Data Services 20.4 without using SYSDBA

It’s really hard to come up with good, concise, and short twitter-compatible blog titles, so let’s try with a few more words. What I’d like to share is how to install Oracle Restful Data Services (ORDS) v20.4 without having to connect to the database as SYSDBA. There are good reasons not to connect as SYSDBA, and I’m glad Oracle made it possible to grant the necessary privileges for an ORDS installation to a regular database account. It’s not a new feature at all, the option has been around for a while but I didn’t have time to write about it yet.

Some background before getting started

The installation of Oracle’s Restful Data Services (ORDS) consists of 2 parts:

  1. Deploying the ORDS distribution to a Docker container or virtual machine
  2. Connecting to and configuring its database

Initially it was necessary to connect to the highly privileged SYSDBA account to complete step 2. Thankfully this isn’t necessary anymore. My post describes how to install and configure ORDS 20.4 against an Oracle 19c (non-container) database.

Deploying ORDS

The first step consists of downloading ORDS from Oracle’s website. I am planning on deploying it in one of my Oracle Linux 8 vagrant boxes. There is more to the deployment of ORDS than configuration, which I’m leaving to a later post. More specifically I’ll not concern myself integrating ORDS into a Docker container or even Tomcat 9.x to keep the post simple.

The Ansible playbook I use creates an Oracle account out of habit. It also creates the location I want it to use – /opt/ords – and changes the directory’s ownership to oracle:oinstall. Finally, it unzips ORDS.

Configuring ORDS

ORDS stores its configuration in a directory of your choice. Since I’m lazy I use /opt/ords/config for this purpose. Once the directory is created on the file system you tell ORDS where to find its configuration:

[oracle@ords ords]$ java -jar /opt/ords/ords.war configdir /opt/ords/config
2021-04-21T18:48:23.156Z INFO        Set config.dir to /opt/ords/config in: /opt/ords/ords.war

Once this step is complete it’s time to configure ORDS and its database connection. This step is #2 in the above list and referred to as “installation” in the documentation.

Creating the less-privileged user in the database

As per the Install Guide you need to run a script to grant a non-privileged user the rights to configure ORDS. The script doesn’t create the user so you have to ensure it exists. The user didn’t seem to require any elevated privileges. I went creative and created the installation account:

SQL> create user ordsinstall identified by values '...';

User created.

SQL> grant create session to ordsinstall;

Grant succeeded.

With the user created I could start the minimum privilege script:

SQL> @installer/ords_installer_privileges ordsinstall

The script takes one argument: the account you intend to use for the installation (ordsinstall).

Installing ORDS in the database

The final step is to install ORDS in the database. This can be done in many ways. Trying to keep it simple I went with the interactive installation.

Have a look at the screen output, it should be self-explanatory for the most part. When prompted for the administrator username you provide the account just created (ordsinstall in my case). Since I wanted to try SQL*Developer Web, I chose that option. Your mileage may vary.

[oracle@ords ords]$ java -jar ords.war install advanced
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:1
Enter the name of the database server [localhost]:server3
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:ORCL
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:ordsinstall
Enter the database password for ordsinstall:
Confirm password:
Connecting to database user: ordsinstall url: jdbc:oracle:thin:@//server3:1521/ORCL

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Enter a number to select a feature to enable:
   [1] SQL Developer Web  (Enables all features)
   [2] REST Enabled SQL
   [3] Database API
   [4] REST Enabled SQL and Database API
   [5] None
Choose [1]:1
2021-04-21T19:38:18.012Z INFO        reloaded pools: []
Installing Oracle REST Data Services version 20.4.3.r0501904
... Log file written to /home/oracle/ords_install_core_2021-04-21_193818_00414.log
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
Warning: Nashorn engine is planned to be removed from a future JDK release
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /home/oracle/ords_install_datamodel_2021-04-21_193842_00226.log
... Log file written to /home/oracle/ords_install_apex_2021-04-21_193846_00491.log
Completed installation for Oracle REST Data Services version 20.4.3.r0501904. Elapsed time: 00:00:32.177 

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2
[oracle@ords ords]$

That’s it! ORDS has been configured in the database, and I didn’t have to connect as SYSDBA. I think that’s a big step ahead, and I have meant to write about this topic for a while.

Have fun!