Introduction

This document describes how to get started with developing RESTful Services using Oracle REST Data Services.

Prerequisities

• Ensure sure you have completed installation of Oracle REST Data Services (ORDS) and configured it to connect to an Oracle database.
• Ensure that you have installed Oracle SQL Developer 4.1 in order to be able to edit RESTful Services.
• It is strongly recommended that you install a browser extension that enables you to view JSON in the web browser.
• For Google Chrome, JSON Formatter is recommended.
• For Mozilla Firefox, JSON View is recommended.

About Examples in this document

This document assumes your environment has been configured as follows:

• ORDS has been installed and configured, and is running in Standalone mode on the following server, port and context path: localhost:8080/ords/
• ORDS is configured to connect it's default connection to an Oracle Database listening on localhost:1521 and the database has a service name of orcl.

Lesson 1 - REST enable a database table

Create a database schema

Let's create a database schema, that we can use to try out RESTful Services. In SQL Developer connect to the database as sys and enter the following in the SQL Worksheet:

CREATE USER ordstest IDENTIFIED BY password;
GRANT "CONNECT" TO ordstest;
GRANT "RESOURCE" TO ordstest;
GRANT UNLIMITED TABLESPACE TO ordstest

• where password is the password for the ordstest user.

Connect to ordstest Schema

In SQL Developer create a connection to the ordstest schema, connect to it and open an SQL worksheet.

Create a database table

Enter the following in the SQL Worksheet to create an example table:

CREATE TABLE EMP (
  EMPNO NUMBER(4,0), 
	ENAME VARCHAR2(10 BYTE), 
	JOB VARCHAR2(9 BYTE), 
	MGR NUMBER(4,0), 
	HIREDATE DATE, 
	SAL NUMBER(7,2), 
	COMM NUMBER(7,2), 
	DEPTNO NUMBER(2,0), 
	CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
  );

Insert some sample data

Execute the following insert statements to add some sample data to the EMP table:

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

Enable the EMP table for REST

In SQL Developer right click on the ordstest connection, and choose 'REST Services| Enable RESTful Services...', the following dialog appears:

• Ensure the 'Enable Schema' check box is ticked
• To make demonstrating the service easier we will disable authorisation, ensure the 'Authorization required' check box is not ticked.
• Click the 'Next' Button, the following dialog appears:

Next right click on the EMP table, and choose 'Enable RESTful Services...', the following dialog appears:

• Ensure the 'Enable Object' check box is ticked
• To make demonstrating the service easier we will disable authorisation, ensure the 'Authorization required' check box is not ticked.
• Click the 'Next' Button, the following dialog appears:

• Click the 'Finish' button

The EMP table is now exposed as a REST HTTP endpoint.

Test the REST Endpoint

In a web browser enter the following URL:

http://localhost:8080/ords/ordstest/emp/

• The ORDSTEST schema has been exposed at the /ordstest/ path
• The EMP table has been exposed at the /emp/ table

A JSON document similar to the following will be displayed:

Lesson 2 - Create a RESTful Service from an SQL Query

Configure RESTful Services User

Oracle REST Data Services provides a REST API (called the Resource Modules API) that enables Oracle SQL Developer to create and edit RESTful Service definitions. Access to the Resource Modules API is protected, a user with the correct role must be provisioned and the created user's credentials must be used when accessing the API from SQL Developer.

In the folder where Oracle REST Data Services was installed, enter the following command in a command prompt:

java -jar ords.war user test_developer "SQL Developer"

• You will be prompted to enter a password
• This creates a user named test_developer and grants the user the role named SQL Developer
• Only users with the SQL Developer role are permitted to access the Resource Modules API
• The user details are stored in a file named credentials in the ORDS configuration folder

Tip: It is not recommended to store user credentials in the credentials file in production deployments, instead users should be provisioned in the host application server.

Create RESTful Service

Tip: To complete this step Oracle REST Data Services must be running Tip: To complete this step the ordstest schema must have been created, and the schema enabled for use with ORDS, see Lesson 1 in this tutorial.

Create RESTful Connection

• In SQL Developer select 'View|REST Data Services|Development' from the main menu.
• The 'REST Development' pane will appear on the left of the screen.
• Right click on the 'REST Data Services' label, choose 'Connect...'
• The following dialog is displayed:

• Click the '+' button, the following dialog appears:

• Enter ordstest for the 'Connection Name'
• Enter test_developer for the 'UserName'
• Enter localhost for the 'Hostname'
• Enter 8080 for the 'Port'
• Enter ordstest for the 'Workspace'
• Press 'OK' to close and save the connection
• Press 'OK' to use the newly created connection
• Enter the password for the test_developer user in the prompt that appears.

The existing Modules and Privileges will be loaded, if this is your first time using this tutorial, then there will be no Modules or Privileges yet.

Create Module

• Right click on the 'Modules' node in the 'REST Development' view, click 'New Module...', the following dialog will appear:

• Enter test for the 'Module Name'
• Enter /test for the 'URI Prefix
• Ensure the 'Publish' check box is ticked
• Set the 'Pagination Size' to 7
• Click the 'Next' button, the following dialog is displayed:

• Enter /emp/ for the 'URI Template'
• Click the 'Next' button, the following dialog is displayed:

• Ensure the 'Requires Secure Access' check box is not ticked
• Set the 'Pagination Size' to 7
• Click the 'Next' button, the following dialog is displayed:

• Click the 'Finish' button

The resource module is created, the next step is to define the query for the GET resource handler.

Define Resource Handler Query

• Expand the test node under the 'Modules' node in the 'REST Development' view
• Expand the /emp/ node and right click the GET node and select 'Open'
• An SQL Worksheet opens for GET /emp/, enter the following SQL query in the worksheet: select * from emp
• Right click on the test node under the 'Modules' node in the 'REST Development' view
• Click 'Upload...'. A confirmation dialog will appear confirming the module has been uploaded.

Test RESTful Service

In a web browser enter the following URL:

http://localhost:8080/ords/ordstest/test/emp/

• The ORDSTEST schema has been exposed at the /ordstest/ path
• The RESTful Service has been exposed at the /test/emp/ path

A JSON document similar to the following will be displayed:

Lesson 3 - Protecting Resources

About Protected Resources

Up to this point we have deliberately disabled security on the RESTful endpoints we have created, because it is easier to test them without security. In this lesson we will protect the /test/emp/ service, requiring users to authenticate before accessing the service.

Controlling access to protected resources is done by defining 'Privileges'. Privileges restrict access to only users having at least one of a set of specified roles. A privilege is then associated with one or more Resource Modules, before those Resource Modules can be accessed the user must be authenticated, and then authorised to ensure they have one of the required roles.

Create a Privilege

• Right click on the 'Privileges' node in the 'REST Development' view, click 'New Privileges...', the following dialog will appear:

• Enter test for the 'Name' value
• Enter Example Privilege for the 'Title' value
• Enter Demonstrate controlling access with Privileges for 'Description' value
• Ensure the test Module is listed in the 'Protected Modules' shuttle box.
• Click 'Apply'
• Right click on the test Privilege and click 'Upload'. A confirmation dialog will appear confirming the privilege has been uploaded.

We have created a Privilege that protects the test module. We have not restricted it to any particular role, this will just require that the user be authenticated before accessing the test module.

Test RESTful Service

In a web browser enter the following URL:

http://localhost:8080/ords/ordstest/test/emp/

You will now be prompted to sign in. Click the link to sign-in and enter the test_developer credentials. The contents of the JSON document will now be displayed.

Lesson 4 - Register an OAuth Client Application

About OAuth 2.0

OAuth 2.0 is a standard Internet protocol that provides a means for HTTP servers providing REST APIs to give limited access to third party applications on behalf of an end user.

• The author of the third party application must register the application to gain client credentials.
• Using the client credentials the third party application starts a web flow that prompts the end-user to approve access.

So before a third party application can access a REST API it must be registered and the user must approve access.

This lesson outlines how to complete these steps. It is not a full featured demonstration of how to create and integrate a third party application, it just outlines the concepts involved.

About Third Party Users

Before a third party can register an application they must be assigned a user identity that enables them to register applications. Users possessing the SQL Developer (such as the test_developer user created in Lesson 1) are permitted to register OAuth clients.

Tip In a real application you may wish to provision particular users that can register OAuth clients, these users should be granted the OAuth Client Developer role.

Register a Client Application

In a web browser enter the following URL:

http://localhost:8080/ords/ordstest/oauth/admin/clients/

• You will be prompted to sign in, click the link and enter the credentials for the test_developer user
• Click the 'New Client' button
• Enter Test Client for the 'Name' field
• Enter An example OAuth Client for the 'Description' field
• Enter http://example.org/redirect for the 'Redirect URI' field
• Enter info@example.org for the 'Support e-mail' field
• Enter http://example.org/support for the 'Support URI' field
• Choose Example Privilege for the 'Required Privileges' field
• Click the 'Create' button

The client registration is created, and the 'Authorization URI' for the client is displayed. We have created a client that will use the 'Implicit Grant' authorisation flow.

Tip Make a note of the 'Client Identififer' assigned to the client and the 'Authorization URI' value. These values are used to start the authorization flow.

Approving a Client Application

In a real third party client application, the client will initiate the approval flow by directing a web browser to the 'Authorization URI'. The end user will be prompted to sign in and approve access to the client application. The browser will be redirected back to the client's registered 'Redirect URI' with a URI fragment containing the access_token for the approval.

To simulate this process, enter the 'Authorization URI' that was noted in the previous step in a web browser, the URL should look like the following:

http://localhost:8080/ords/ordstest/oauth/auth?response_type=token&client_id=5B77A34A266EFB0056BE3497ED7099.&state=d5b7944-d27d-8e2c-4d5c-fb80e1114490&_auth_=force

• The client_id value must be the value of the client identifier assigned to the application, make sure you are using the correct client_id value. Do not use the value in the example above, replace it with the client identifier assigned to your application.
• The state value should be a unique unguessable value that the client remembers, and can use later to confirm that the redirect received from ORDS is in response to this authorisation request. This value is used to prevent Cross Site Request Forgery attacks, it is very important, cannot be omitted, and must not be guessable or discoverable by an attacker.
• You will be prompted to sign in, click the link and enter the credentials for the test_developer user
• Review the access being requested and click the 'Approve' button
• The browser is redirected to a URL similar to the following:

http://example.org/redirect#token_type=bearer&access_token=-i_Ows8j7JYu0p07jOFMEA..&expires_in=3600

• http://example.org is a sample domain provided by ICANN, that can be used for demonstration purposes
• When registering the OAuth client we specified http://example.org/redirect as the 'Redirect URI', on completion of the approval request, the browser was redirected to this registered redirect URI. Appended to the URI is the information about the access token that was generated for the approval.
• In a real application the third party application would respond to the redirect to the redirect URI by caching the access token, redirecting to another page to show the user that they are now authorised to access the REST API, and include the access token in every subsequent request to the REST API.
• In this tutorial we will just make note of the access token value and manually create a HTTP request with the access token included, as explained in the next step.
• In the above example the value of the access token is -i_Ows8j7JYu0p07jOFMEA.., the value will change on every approval, please make note of the actual access token you received.
• Note that the token expires, in this example it expires after 3600 seconds, i.e one hour.

Issuing an Authorized Request

Once an access token has been acquired, the client application needs to remember the access token and include it with every request to the protected resource. The access token must be included in the HTTP Authorization request header as shown below:

Host: localhost:8080
GET /ords/ordstest/test/emp/
Authorization: Bearer -i_Ows8j7JYu0p07jOFMEA..

Create HTTP Request

To demonstrate creating a valid HTTP request we will use the cURL command line tool (Install cURL using the preceding link if necessary), in a real application this request would be performed by the client making a HTTP request, for example performing an XMLHttpRequest.

• Enter the following command in a command prompt

curl -i -H'Authorization: Bearer -i_Ows8j7JYu0p07jOFMEA..' http://localhost:8080/ords/ordstest/test/emp/

• Replacing -i_Ows8j7JYu0p07jOFMEA.. with the value of the access token you noted in the previous step
• Output similar to the following should be displayed:

HTTP/1.1 200 OK
Content-Type: application/json
Transfer-Encoding: chunked

{
 "items":[
  {"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-17T00:00:00Z","sal":800,"comm":null,"deptno":20},
  {"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30},
  {"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00Z","sal":1250,"comm":500,"deptno":30},
  {"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-01T23:00:00Z","sal":2975,"comm":null,"deptno":20},
  {"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-27T23:00:00Z","sal":1250,"comm":1400,"deptno":30},
  {"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"1981-04-30T23:00:00Z","sal":2850,"comm":null,"deptno":30},
  {"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-08T23:00:00Z","sal":2450,"comm":null,"deptno":10}
 ],
 "hasMore":true,
 "limit":7,
 "offset":0,
 "count":7,
 "links":[
  {"rel":"self","href":"http://localhost:8080/ords/ordstest/test/emp/"},
  {"rel":"describedby","href":"http://localhost:8080/metadata-catalog/test/emp/"},
  {"rel":"first","href":"http://localhost:8080/ords/ordstest/test/emp/"},
  {"rel":"next","href":"http://localhost:8080/ords/ordstest/test/emp/?offset=7"}
 ]
}

• The JSON document is returned successfully, if the Authorization header is omitted then a 401 Unauthorized status will be returned instead

Further Reading

Please consult the Oracle REST Data Services Developer Guide for further information on developing, securing and accessing RESTful Services.