Oracle REST Data Services (ORDS) installation and usage

Posted on , updated on by Yannick Jaquier
Oracle ,

Table of contents

Preamble

In the database world technology trend you have surely already heard buzz words like Hadoop and NoSQL. Around those new non-relational databases there is a common open-standard file format massively used to read and write on those new databases call JavaScript Object Notation (JSON).

If you have followed a bit few new web technologies trainings (Vue.js, Angular, React, …) each time they use a back-end database to store information the exchanges are always done through an asynchronous request using promises (Axios for the one I have used in a Vue.js project). The information are, also, always transferred using JSON format. Those exposed services by newest databases flavors are using a REpresentational State Transfer (REST) architecture and expose this is a RESTful web service or RESTful API.

Oracle corporation with their legacy Oracle database (back in the 80’s for first release) have climb on the bandwagon and have created a product called Oracle REST Data Services (ORDS) to make the bridge between newest generation of developers and (becoming old) past generation DBAs (guess in which category I am ?). This product/tool that is a simple jar file to run through Java is creating a RESTful service to expose Oracle database figures through http(s) requests…

To have figures to display I have decided, this time, to use the default HR sample schema that you can create using script located at:

$ORACLE_HOME/demo/schema/human_resources/hr_main.sql

Idea is to display and interact with employees table using a RESTful API. We would be able to display all employees or a particular one specifying an id in provided url as well as inserting, deleting and updating additional ones.

Testing has been done using a VirtualBox virtual machine running Oracle Linux Server release 7.4 and an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit.

ORDS installation

I could have used the JDK of the Oracle home but I rated the release a bit too old:

[oracle@server1 ~]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

As I’m rock’n’roll I have decided to install and use Java 10:

[oracle@server1 ~]$ java -version
java version "10" 2018-03-20
Java(TM) SE Runtime Environment 18.3 (build 10+46)
Java HotSpot(TM) 64-Bit Server VM 18.3 (build 10+46, mixed mode)

There is also an $ORACLE_HOME/ords but I have not been able to make it working so decided to replace it with the latest release available at the time of writing this post:

[oracle@server1 ords]$ java -jar ords.war version
Oracle REST Data Services 18.1.1.95.1251

I have also chosen the multitenant installation even if I have only on pluggable database, the installation is called Installation Enabling Multiple Releases (Recommended). In the advanced installation process you must supply the container (CDB) information and it will eb deployed in all pluggable databases including the seed one (and also in root one for common objects and accounts).

Note:
I have discovered that the configuration directory is relative to the directory where you have started the installation so simply config for me to be in $ORACLE_HOME/ords/config..

[oracle@server1 ords]$ cd $ORACLE_HOME/ords
[oracle@server1 ords]$ java -jar ords.war install advanced
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts
 
Enter the location to store configuration data:config
Enter the name of the database server [localhost]:server1.domain.com
Enter the database listen port [1521]:1531
Enter 1 to specify the database service name, or 2 to specify the database SID [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 SYS AS SYSDBA to verify Oracle REST Data Services schema.
 
Enter the database password for SYS AS SYSDBA:
Confirm password:
 
Retrieving information...
Your database connection is to a CDB.  ORDS common user ORDS_PUBLIC_USER will be created in the CDB.  ORDS schema will be installed in the PDBs.
Root CDB$ROOT - create ORDS common user
PDB PDB$SEED - install ORDS 18.1.1.95.1251 (mode is READ ONLY, open for READ/WRITE)
PDB PDB1 - install ORDS 18.1.1.95.1251
 
Enter 1 if you want to install ORDS or 2 to skip this step [1]:
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]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Apr 11, 2018 5:18:14 PM
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
 
 
Installing Oracle REST Data Services version 18.1.1.95.1251 in CDB$ROOT
... Log file written to /u01/app/oracle/product/12.2.0/dbhome_1/ords/logs/ords_cdb_install_core_CDB_ROOT_2018-04-11_171814_00573.log
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
Completed installation for Oracle REST Data Services version 18.1.1.95.1251. Elapsed time: 00:00:01.690
 
Installing Oracle REST Data Services version 18.1.1.95.1251 in PDB$SEED
... Log file written to /u01/app/oracle/product/12.2.0/dbhome_1/ords/logs/ords_cdb_install_core_PDB_SEED_2018-04-11_171818_00681.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/app/oracle/product/12.2.0/dbhome_1/ords/logs/ords_cdb_install_datamodel_PDB_SEED_2018-04-11_171947_00378.log
... Log file written to /u01/app/oracle/product/12.2.0/dbhome_1/ords/logs/ords_cdb_install_apex_PDB_SEED_2018-04-11_171955_00184.log
Completed installation for Oracle REST Data Services version 18.1.1.95.1251. Elapsed time: 00:01:43.21
 
Installing Oracle REST Data Services version 18.1.1.95.1251 in PDB1
... Log file written to /u01/app/oracle/product/12.2.0/dbhome_1/ords/logs/ords_cdb_install_core_PDB1_2018-04-11_172036_00713.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/app/oracle/product/12.2.0/dbhome_1/ords/logs/ords_cdb_install_datamodel_PDB1_2018-04-11_172204_00677.log
... Log file written to /u01/app/oracle/product/12.2.0/dbhome_1/ords/logs/ords_cdb_install_apex_PDB1_2018-04-11_172210_00596.log
Completed installation for Oracle REST Data Services version 18.1.1.95.1251. Elapsed time: 00:01:38.462
 
Completed CDB installation for Oracle REST Data Services version 18.1.1.95.1251.
Total elapsed time: 00:04:00.631
 
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

You can have the configuration directory with:

[oracle@server1 ords]$ java -jar ords.war configdir
Apr 12, 2018 12:31:09 PM
INFO: The config.dir value is /u01/app/oracle/product/12.2.0/dbhome_1/ords/config

I have decided to not start it after installation to understand the real start procedure, the first time you start it a set of questions will be asked. As Internet is know almost full HTTPS this is the option I have chosen. In any case if you plan to use REST Enabled SQL HTTPS has to be chosen:

[oracle@server1 ords]$ java -jar ords.war standalone
Enter the APEX static resources location:
Enter 1 if using HTTP or 2 if using HTTPS [1]:2
Enter the HTTPS port [8443]:
Enter the SSL hostname:server1.domain.com
Enter 1 to use the self-signed certificate or 2 if you will provide the SSL certificate [1]:
2018-04-11 17:30:52.110:INFO::main: Logging initialized @26842ms to org.eclipse.jetty.util.log.StdErrLog
Apr 11, 2018 5:30:53 PM
INFO: HTTPS and HTTPS/2 listening on port: 8443
Apr 11, 2018 5:30:53 PM
INFO: Disabling document root because the specified folder does not exist: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/standalone/doc_root
2018-04-11 17:30:53.408:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT, build timestamp: 2017-11-21T22:27:37+01:00, git hash: 82b8fb23f757335bb3329d540ce37a2a2615f0a8
2018-04-11 17:30:53.464:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2018-04-11 17:30:53.464:INFO:oejs.session:main: No SessionScavenger set, using defaults
2018-04-11 17:30:53.465:INFO:oejs.session:main: Scavenging every 660000ms
Apr 11, 2018 5:30:55 PM
WARNING: The pool named: |apex|| is invalid and will be ignored: The username or password for the connection pool named apex, are invalid, expired, or the account is locked
Apr 11, 2018 5:30:56 PM
INFO: Creating Pool:|apex|pu|
Apr 11, 2018 5:30:56 PM
INFO: Configuration properties for: |apex|pu|
cache.caching=false
cache.directory=/tmp/apex/cache
cache.duration=days
cache.expiration=7
cache.maxEntries=500
cache.monitorInterval=60
cache.procedureNameList=
cache.type=lru
db.hostname=server1.domain.com
db.password=******
db.port=1531
db.servicename=orcl
db.username=ORDS_PUBLIC_USER
debug.debugger=false
debug.printDebugToScreen=false
error.keepErrorMessages=true
error.maxEntries=50
jdbc.DriverType=thin
jdbc.InactivityTimeout=1800
jdbc.InitialLimit=3
jdbc.MaxConnectionReuseCount=1000
jdbc.MaxLimit=10
jdbc.MaxStatementsLimit=10
jdbc.MinLimit=1
jdbc.statementTimeout=900
log.logging=false
log.maxEntries=50
misc.compress=
misc.defaultPage=apex
security.disableDefaultExclusionList=false
security.maxEntries=2000
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
security.validationFunctionType=plsql
 
Apr 11, 2018 5:30:56 PM
WARNING: *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10, this setting may not be sized adequately for a production environment ***
Apr 11, 2018 5:30:56 PM
WARNING: *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3, this setting may not be sized adequately for a production environment ***
Apr 11, 2018 5:30:57 PM
WARNING: The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named apex_al, are invalid, expired, or the account is locked
Apr 11, 2018 5:30:58 PM
WARNING: The pool named: |apex|rt| is invalid and will be ignored: The username or password for the connection pool named apex_rt, are invalid, expired, or the account is locked
Apr 11, 2018 5:30:58 PM
INFO: Oracle REST Data Services initialized
Oracle REST Data Services version : 18.1.1.95.1251
Oracle REST Data Services server info: jetty/9.4.z-SNAPSHOT
 
2018-04-11 17:30:58.804:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@eadd4fb{/ords,null,AVAILABLE,@Secured}
2018-04-11 17:30:58.821:INFO:oejus.SslContextFactory:main: x509=X509@7ce97ee5(selfsigned,h=[server1.domain.com],w=[]) for SslContextFactory@32c8e539[provider=null,keyStore=oracle.dbtools.standalone.InMemoryResource@73dce0e6,trustStore=oracle.dbtools.standalone.InMemoryResource@73dce0e6]
2018-04-11 17:30:58.888:INFO:oejs.AbstractConnector:main: Started Secured@a5bd950{SSL,[ssl, alpn, h2, http/1.1]}{0.0.0.0:8443}
2018-04-11 17:30:58.888:INFO:oejs.Server:main: Started @33622ms

Obviously you would need to use nohup command because in interactive mode the process is stopped when you quit the terminal…

To make all PDBs addressable by Oracle REST Data Services (Pluggable Mapping) I have finally used below command. A bit different than Oracle official documentation and my DB_DOMIAN parameter is unset:

[oracle@server1 ords]$ java -jar ords.war set-property db.serviceNameSuffix ''
Apr 12, 2018 12:38:47 PM oracle.dbtools.rt.config.setup.SetProperty execute
INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: db.serviceNameSuffix =

Then I have added my single pluggable database with:

[oracle@server1 ords]$ java -jar ords.war setup --database pdb1
Enter the name of the database server [server1.domain.com]:
Enter the database listen port [1531]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [orcl]:pdb1
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:
 
Retrieving information.
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
Apr 12, 2018 3:13:42 PM
INFO: Updated configurations: pdb1_pu
Apr 12, 2018 3:13:42 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 18.1.1.95.1251 is installed.

It has created below configuration file:

[oracle@server1 conf]$ cat $ORACLE_HOME/ords/config/ords/conf/pdb1_pu.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Thu Apr 12 15:13:42 CEST 2018</comment>
<entry key="db.password">@05695FBEB8J4C5B200HG56EB28FF0F27B61F9B56AFF75F8472</entry>
<entry key="db.servicename">pdb1</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
</properties>

I define the routing based on the request path prefix with (nothing original as the url will have the pluggable database name):

[oracle@server1 ords]$ java -jar ords.war map-url --type base-path /pdb1 pdb1
Apr 12, 2018 3:22:25 PM
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]

ORDS setup

When trying with SYS account I have gotten a strange error:

SQL> EXEC ords.enable_schema(p_schema => 'hr', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr');
BEGIN ords.enable_schema(p_schema => 'hr', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr'); END;
 
*
ERROR AT line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: AT "ORDS_METADATA.ORDS", line 1
ORA-06512: AT line 1

So finally executed it with my nominative DBA account:

SQL> exec ords.enable_schema(p_schema => 'hr', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr');
 
PL/SQL procedure successfully completed.

Instead of using the example of the official documentation:

EXEC ords.define_service(p_module_name => 'examples.routes', p_base_path => '/examples/routes/', p_pattern => 'greeting/:name', -
p_source => 'select ''Hello '' || :name || '' from '' || nvl(:whom,sys_context(''USERENV'',''CURRENT_USER'')) "greeting" from dual');

I have decided to try something much simpler for my first test but it failed for a strange error:

SQL> show USER
USER IS "HR"
SQL> EXEC ords.define_service(p_module_name => 'examples', p_base_path => 'examples/', p_method => 'GET', p_pattern => 'greeting/', -
     p_source => 'select sysdate from dual');
BEGIN ords.define_service(p_module_name => 'examples', p_base_path => 'examples/', p_method => 'GET', p_pattern => 'greeting/',  p_source => 'select sysdate from dual'); END;
 
*
ERROR AT line 1:
ORA-01403: no data found
ORA-06512: AT "ORDS_METADATA.ORDS_INTERNAL", line 617
ORA-06512: AT "ORDS_METADATA.ORDS_SECURITY", line 85
ORA-06512: AT "ORDS_METADATA.ORDS_SERVICES", line 117
ORA-06512: AT "ORDS_METADATA.ORDS_SERVICES", line 52
ORA-06512: AT "ORDS_METADATA.ORDS", line 694
ORA-06512: AT line 1

Then the magic idea came to my mind and while I was grumbling about the fact that Oracle could have used a UPPER command for account name I remembered that since 11g account are now case sensitive, and this by default:

SQL> show parameter SEC_CASE_SENSITIVE_LOGON
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             BOOLEAN     TRUE
 
SQL> SELECT parsing_schema, status, auto_rest_auth FROM ords_metadata.ords_schemas;
 
PARSING_SCHEMA                 STATUS                         AUTO_REST_AUTH
------------------------------ ------------------------------ ------------------------------
ORDS_METADATA                  DISABLED                       ENABLED
hr                             ENABLED                        ENABLED

So did a bit of cleaning with (no DISABLE_SCHEMA or using false with ENABLE_SCHEMA is not deleting the line in ORDS_METADATA.ORDS_SCHEMAS:

SQL> EXECUTE ORDS.DROP_REST_FOR_SCHEMA('hr');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> EXEC ords.enable_schema(p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT parsing_schema, status, auto_rest_auth FROM user_ords_schemas;
 
PARSING_SCHEMA                 STATUS                         AUTO_REST_AUTH
------------------------------ ------------------------------ ------------------------------
HR                             ENABLED                        ENABLED

And finally the service definition went well (even if it looks stupid the COMMIT is strongly suggested), I have also decided to format a bit the date display:

SQL> show USER
USER IS "HR"
SQL> EXEC ords.define_service(p_module_name => 'examples', p_base_path => '/examples/', p_method => 'GET', p_pattern => '/greeting/', -
     p_source => 'select to_char(sysdate,''dd-mon-yyyy hh24:mi:ss'') as current_date from dual');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.

Then a simple GET request on the url should provide current date. To be honest I have not expected to spend so much time on this !! Initial request with Curl failed::

[oracle@server1 ~]$ curl https://server1.domain.com:8443/ords/pdb1/hr/examples/greeting/
curl: (60) Issuer certificate is invalid.
More details here: http://curl.haxx.se/docs/sslcerts.html
 
curl performs SSL certificate verification by default, using a "bundle"
 of Certificate Authority (CA) public keys (CA certs). If the default
 bundle file isn't adequate, you can specify an alternate file
 using the --cacert option.
If this HTTPS server uses a certificate signed by a CA represented in
 the bundle, the certificate verification probably failed due to a
 problem with the certificate (it might be expired, or the name might
 not match the domain name in the URL).
If you'd like to turn off curl's verification of the certificate, use
 the -k (or --insecure) option.
[oracle@server1 ~]$ curl -k https://server1.domain.com:8443/ords/pdb1/hr/examples/greeting/
invalid_preface

So decided to activate debugging mode with ($ORACLE_HOME/ords/config/ords/defaults.xml file):

<entry key="debug.debugger">true</entry>
<entry key="debug.printDebugToScreen">true</entry>

Either you edit the file or use:

[oracle@server1 ords]$ java -jar ords.war set-property debug.debugger true
Apr 13, 2018 11:50:21 AM oracle.dbtools.rt.config.setup.SetProperty execute
INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: debug.debugger = true
[oracle@server1 ords]$ java -jar ords.war set-property debug.printDebugToScreen true
Apr 13, 2018 11:50:36 AM oracle.dbtools.rt.config.setup.SetProperty execute
INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: debug.printDebugToScreen = true

But to be honest this has bring nothing to help me… Do not forget to remove it afterwards as it is quite verbose… Then I noticed the HTTPS/2 in ORDS startup output:

INFO: HTTPS and HTTPS/2 listening on port: 8443

And wanted to use the –http2 option to tell curl to use HTTP version 2 but the release available in my OEL 7.4 (curl 7.29.0) at the time of writing this post is too old.

I have tried to download a binary on my Windows desktop and it has worked but I have remembered a Web training video where the presenter has introduced Postman. But I have not been able to make it working… So finally downloaded Insomnia and yeepee got the expected result:

ords01
ords01

If I try with a parameter in the url to get information for only one employee:

SQL> show USER
USER IS "HR"
SQL> EXEC ords.define_service(p_module_name => 'employees', p_base_path => '/employees/', p_method => 'GET', p_pattern => '/:id', -
     p_source => 'select * from employees where employee_id=:id');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.

I can now specify in the url the employee id I would like to display:

ords02
ords02

With dictionary views you can double check what has been done (no ALL_xx or DBA_xx views, account owner is ORDS_METADATA):

SQL> SET lines 200
SQL> col pattern FOR a15
SQL> col name FOR a15
SQL> col uri_prefix FOR a15
SQL> col uri_template FOR a15
SQL> col source_type FOR a20
SQL> SELECT id, parsing_schema, TYPE, pattern, status, auto_rest_auth FROM user_ords_schemas;
 
        ID PARSING_SCHEMA                 TYPE       PATTERN         STATUS                         AUTO_REST_AUTH
---------- ------------------------------ ---------- --------------- ------------------------------ ------------------------------
     10062 HR                             BASE_PATH  hr              ENABLED                        ENABLED
 
SQL> SELECT id, name, uri_prefix, items_per_page, status FROM user_ords_modules;
 
        ID NAME            URI_PREFIX      ITEMS_PER_PAGE STATUS
---------- --------------- --------------- -------------- ------------------------------
     10120 employees       /employees/                 25 PUBLISHED
     10067 examples        /examples/                  25 PUBLISHED
 
SQL> SELECT id, module_id, uri_template FROM user_ords_templates;
 
        ID  MODULE_ID URI_TEMPLATE
---------- ---------- ---------------
     10133      10120 /:id
     10101      10067 /greeting/
 
SQL> SELECT id, template_id, source_type, method, source FROM user_ords_handlers;
 
        ID TEMPLATE_ID SOURCE_TYPE          METHOD     SOURCE
---------- ----------- -------------------- ---------- --------------------------------------------------------------------------------
     10134       10133 json/collection      GET        SELECT * FROM employees WHERE employee_id=:id
     10102       10101 json/collection      GET        SELECT TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss') AS CURRENT_DATE FROM dual

Automatic Enabling of Schema Objects for REST Access (AutoREST)

So far we have seen on how to fetch data from tables but how we modify them ? When looking in official documentation on how to update, delete and insert rows in objects I end up in AutoREST chapter. This is in fact linked to two procedures of ORDS package that remain unused: ENABLE_SCHEMA and ENABLE_OBJECT.

The shortest and self explaining definition of AutoREST is:

AutoREST is a quick and easy way to expose database tables as REST resources.

By default AutoREST is enabled but when using ENABLE_SCHEMA you might want to deactivate AutoREST authentication for easier testing (needless to say you must not do this in production):

SQL> EXEC ords.enable_schema(p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT parsing_schema, TYPE, pattern, status, auto_rest_auth FROM user_ords_schemas;
 
PARSING_SCHEMA                 TYPE       PATTERN         STATUS                         AUTO_REST_AUTH
------------------------------ ---------- --------------- ------------------------------ ------------------------------
HR                             BASE_PATH  hr              ENABLED                        DISABLED

Means we can display meta-data using special url:

ords03
ords03

Objects on the contrary are not enables by default:

SQL> SET pages 1000
SQL> col parsing_object FOR a20
SQL> col object_alias FOR a20
SQL> SELECT parsing_object, object_alias, TYPE, status, auto_rest_auth FROM user_ords_objects;
 
PARSING_OBJECT       OBJECT_ALIAS         TYPE                           STATUS                         AUTO_REST_AUTH
-------------------- -------------------- ------------------------------ ------------------------------ ------------------------------
ADD_JOB_HISTORY      add_job_history      PROCEDURE                      DISABLED                       ENABLED
COUNTRIES            countries            TABLE                          DISABLED                       ENABLED
DEPARTMENTS          departments          TABLE                          DISABLED                       ENABLED
EMPLOYEES            employees            TABLE                          DISABLED                       ENABLED
EMP_DETAILS_VIEW     emp_details_view     VIEW                           DISABLED                       ENABLED
JOBS                 jobs                 TABLE                          DISABLED                       ENABLED
JOB_HISTORY          job_history          TABLE                          DISABLED                       ENABLED
LOCATIONS            locations            TABLE                          DISABLED                       ENABLED
REGIONS              regions              TABLE                          DISABLED                       ENABLED
SECURE_DML           secure_dml           PROCEDURE                      DISABLED                       ENABLED
 
10 ROWS selected.

Let enable EMPLOYEES table with (I have chosen another alias not to mess up with already existing one we have created above). I also deactivate authentication (needless to say you must not do this in production):

SQL> EXEC ords.enable_object(p_enabled => TRUE, p_schema => 'HR', p_object => 'EMPLOYEES', -
     p_object_type => 'TABLE',  p_object_alias => 'emp', p_auto_rest_auth => FALSE);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT parsing_object, object_alias, TYPE, status, auto_rest_auth FROM user_ords_enabled_objects;
 
PARSING_OBJECT       OBJECT_ALIAS         TYPE                           STATUS                         AUTO_REST_AUTH
-------------------- -------------------- ------------------------------ ------------------------------ ------------------------------
EMPLOYEES            emp                  TABLE                          ENABLED                        DISABLED

You can get meta-data with:

ords04
ords04

And here a list of multiple possible queries (not displaying a picture each time):

  • https://server1.domain.com:8443/ords/pdb1/hr/emp/ to display all employees
  • https://server1.domain.com:8443/ords/pdb1/hr/emp/100 to display employee id 100
  • https://server1.domain.com:8443/ords/pdb1/hr/emp/?limit=5 to display first five employees

To insert a row, I get REST current date format with (ISO 8601):

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD')||'T'||TO_CHAR(SYSDATE,'HH24:MI:SS')||'Z' FROM dual;
 
TO_CHAR(SYSDATE,'YYY
--------------------
2018-04-19T16:59:36Z
ords05
ords05

To delete the just inserted row:

ords06
ords06

PUT would be use to perform an upsert (insert or update). Have a look to ORDS official documentation it contains plenty of different cases…

REST-Enabled SQL Service

From official documentation:

The REST Enabled SQL service is a HTTPS web service that provides access to the Oracle Database SQL Engine. You can POST SQL statements to the service. The service then runs the SQL statements against Oracle database and returns the result to the client in a JSON format.

I have activated REST-Enabled SQL Service with:

[oracle@server1 ords]$ java -jar ords.war set-property restEnabledSql.active true
Apr 12, 2018 12:41:30 PM oracle.dbtools.rt.config.setup.SetProperty execute
INFO: Modified: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/defaults.xml, setting: restEnabledSql.active = true

The documentation provide below command with curl:

curl -i -X POST --user ORDSTEST:ordstest --data-binary "select sysdate from dual" -H "Content-Type: application/sql" -k https://localhost:8088/ords/ordstest/_/sql

With Insomnia it gives:

ords07
ords07
ords08
ords08
ords09
ords09

On the right part of the above screen shots we can see the correct result: 107 rows.

Configuring Secure Access to RESTful Services

We have configured our RESTful service with an HTTPS access but what if someone has been able to copy past the url ? Then he would be able to display all our employees information, which is most probably not a good thing. How to restrict this ? Well fortunately it has been implemented and you can secure RESTful API access with two methods:

  • First Party Cookie-Based Authentication
  • Third Party OAuth 2.0-Based Authentication

First Party Cookie-Based Authentication

Start by creating a role:

SQL> show USER
USER IS "HR"
SQL> EXEC ords.create_role(p_role_name => 'employees_role');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.

Create a privilege associated with the role:

SQL>
DECLARE
l_arr OWA.vc_arr;
BEGIN
l_arr(1) := 'employees_role';
ords.define_privilege(p_privilege_name => 'employees_priv', p_roles => l_arr, p_label => 'Employees data', -
                      p_description => 'Securing access to employees data');
COMMIT;
END;
/
 
PL/SQL PROCEDURE successfully completed.

Protect the RESTful API with he newly created privilege with (procedure not documented at the time of writing this post in ORDS 18.1):

SQL> EXEC ords.create_privilege_mapping(p_privilege_name => 'employees_priv', p_pattern => '/employees/*');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.

With ORDS dictionary views it gives:

SQL> SET lines 200
SQL> col name FOR a15
SQL> col label FOR a20
SQL> col description FOR a40
SQL> col pattern FOR a15
SQL> col privilege_name FOR a15
SQL> col role_name FOR a15
SQL> SELECT id, name, schema_id FROM user_ords_roles WHERE name='employees_role';
 
        ID NAME             SCHEMA_ID
---------- --------------- ----------
     10135 employees_role       10062
 
SQL> SELECT id, label, name, description FROM user_ords_privileges WHERE name='employees_priv';
 
        ID LABEL                NAME            DESCRIPTION
---------- -------------------- --------------- ----------------------------------------
     10136 Employees data       employees_priv  Securing ACCESS TO employees data
 
SQL> SELECT privilege_id, privilege_name, role_id, role_name FROM user_ords_privilege_roles WHERE privilege_name='employees_priv';
 
PRIVILEGE_ID PRIVILEGE_NAME     ROLE_ID ROLE_NAME
------------ --------------- ---------- ---------------
       10136 employees_priv       10135 employees_role
 
SQL> SELECT privilege_id, name, pattern FROM user_ords_privilege_mappings WHERE name='employees_priv';
 
PRIVILEGE_ID NAME            PATTERN
------------ --------------- ---------------
       10136 employees_priv  /employees/*

finally as expected the RESTful API is no more accessible (HTTP error 401 Unauthorized):

ords10
ords10

Create a user to access again the RESTful API with:

[oracle@server1 ords]$ java -jar ords.war user hr_user employees_role
Enter a password for user hr_user:
Confirm password for user hr_user:
Apr 17, 2018 4:40:27 PM oracle.dbtools.standalone.ModifyUser execute
INFO: Created user: hr_user in file: /u01/app/oracle/product/12.2.0/dbhome_1/ords/config/ords/credentials

And then either you click on the link if you use a browser or with Insommia you can fill in the authentication tab as follow and you can again access the RESTfull API in a secure manner:

ords11
ords11

Third Party OAuth 2.0-Based Authentication

SQL> EXEC oauth.create_client(p_name => 'My Employees Application', p_grant_type => 'client_credentials', p_owner => 'Yannick', -
     p_description => 'A Vue.JS client to access Employees data', p_support_email => 'yannick@domain.com', p_privilege_names => 'employees_priv');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.
SQL> EXEC oauth.grant_client_role(p_client_name => 'My Employees Application', p_role_name  => 'employees_role');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> COMMIT;
 
COMMIT complete.

With my Insomnia tool the access is then a bit different as I am not required to get the access token first. Insomnia is able to do it in one call. I have chosen OAuth 2 in authentication method and I specify token url, client id and client secret the url I aim to fetch is still the same (the access token has been auto-filled by Insomnia):

ords12
ords12

In Timeline tab of response we can see that Insomnia as fetch the access token (Authorization: Bearer line) and used it to fetch my final url to get employee id 100 information:

ords13
ords13

With ORDS dictionary views it gives:

SQL> col name FOR a30
SQL> col client_name FOR a25
SQL> col response_type FOR a15
SQL> SELECT name, description, response_type, client_id, client_secret FROM user_ords_clients;
 
NAME                           DESCRIPTION                              RESPONSE_TYPE   CLIENT_ID                        CLIENT_SECRET
------------------------------ ---------------------------------------- --------------- -------------------------------- --------------------------------
My Employees Application       A Vue.JS client TO ACCESS Employees data TOKEN           6YoYpRwsaeH19coruhZAsw..         yM49CRbG8WkvAb5AUx07lA..
 
SQL> SELECT * FROM user_ords_client_roles;
 
 CLIENT_ID CLIENT_NAME                  ROLE_ID ROLE_NAME
---------- ------------------------- ---------- ---------------
     10163 My Employees Application       10135 employees_role
 
     SQL> SELECT name, label, description, client_name FROM user_ords_client_privileges;
 
NAME                           LABEL                DESCRIPTION                              CLIENT_NAME
------------------------------ -------------------- ---------------------------------------- -------------------------
employees_priv                 Employees data       Securing ACCESS TO employees data        My Employees Application

ORDS uninstall

To remove ORDS from you database use:

[oracle@server1 ords]$ java -jar ords.war uninstall advanced

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

6 thoughts on “Oracle REST Data Services (ORDS) installation and usage”

  1. Sorin says:

    Hi,

    When I am trying to use some nested json response, with a SELECT like:

    select col1, col2
    cursor(select col3, col4
    from t2 where t2.col1 = t1.col1
    ) as ArtInfo
    from t1

    It does not return anything, but if I run the select statement standalone, it returns data.
    Do you know why when I put this select statement in p_source from ords.define_service, it does not return anything? It’s like it is trying to retrieve data, without end … 2 minutes passed and nothing.
    I want col3 and col4 to be displayed as a nested item of col1 and col2 (which are kind o “header” data)

    Thanks!

    • Yannick Jaquier says:

      Hi,

      This could be a limitation of your ORDS release or simply a feature that it not (yet) supported.

      I would first give a try with:
      select col1, col2, col3, col4
      from t1, t2
      where t2.col1 = t1.col1;

      To eliminate any side effect…

      Then check on MOS or ORDS documentation if any limitation with your syntax..

      Yannick.

  2. Ankush Srivastava says:

    Hello Yannick,

    Thanks for the article.
    We are in process of deploying our REST services on Production using Standalone mode as the other java apps of our project are not on Weblogic/Tomcat.
    The DevOps person is going to deploy it using automation on to the App Server; Nginx config.
    As a developer, I need to tell him what are the Output artifact (jar) into Nexus.
    Would it be just the ORDS.war file?
    And then my developed REST services would be deployed by DBA?
    Can you please tell me if I am missing anything in telling him what are the deployable components for this?

    Many Thanks.

    • Yannick Jaquier says:

      Hello Ankush,

      Thanks for comment !

      I tend to say (being not 100% sure) that yes only ORDS.war file should be needed (testing it should not be too complex).

      I would also check in parallel what are the licensing implication of not running this on your database server where the license is… Not sure Oracle allow the free copy of ORDS.war file…

      Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>