This document describes how to get started with developing RESTful Services using Oracle REST Data Services.
This document assumes your environment has been configured as follows:
localhost:8080/ords/
localhost:1521
and the database has a service name of orcl
.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
password
is the password for the ordstest
user.In SQL Developer create a connection to the ordstest
schema, connect to it and open an SQL worksheet.
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)
);
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;
In SQL Developer right click on the ordstest
connection, and choose 'REST Services| Enable RESTful Services...', the following dialog appears:
Next right click on the EMP
table, and choose 'Enable RESTful Services...', the following dialog appears:
The EMP
table is now exposed as a REST HTTP endpoint.
In a web browser enter the following URL:
http://localhost:8080/ords/ordstest/emp/
ORDSTEST
schema has been exposed at the /ordstest/
pathEMP
table has been exposed at the /emp/
tableA JSON document similar to the following will be displayed:
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"
test_developer
and grants the user the role named SQL Developer
SQL Developer
role are permitted to access the Resource Modules APIcredentials
in the ORDS configuration folderTip: 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.
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.
ordstest
for the 'Connection Name'test_developer
for the 'UserName'localhost
for the 'Hostname'8080
for the 'Port'ordstest
for the 'Workspace'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.
test
for the 'Module Name'/test
for the 'URI Prefix7
/emp/
for the 'URI Template'7
The resource module is created, the next step is to define the query for the GET
resource handler.
test
node under the 'Modules' node in the 'REST Development' view/emp/
node and right click the GET
node and select 'Open'GET /emp/
, enter the following SQL query in the worksheet:
select * from emp
test
node under the 'Modules' node in the 'REST Development' viewIn a web browser enter the following URL:
http://localhost:8080/ords/ordstest/test/emp/
ORDSTEST
schema has been exposed at the /ordstest/
path/test/emp/
pathA JSON document similar to the following will be displayed:
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.
test
for the 'Name' valueExample Privilege
for the 'Title' valueDemonstrate controlling access with Privileges
for 'Description' valuetest
Module is listed in the 'Protected Modules' shuttle box.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.
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.
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.
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.
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.
In a web browser enter the following URL:
http://localhost:8080/ords/ordstest/oauth/admin/clients/
test_developer
userTest Client
for the 'Name' fieldAn example OAuth Client
for the 'Description' fieldhttp://example.org/redirect
for the 'Redirect URI' fieldinfo@example.org
for the 'Support e-mail' fieldhttp://example.org/support
for the 'Support URI' fieldExample Privilege
for the 'Required Privileges' fieldThe 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.
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
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.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.test_developer
userhttp://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 purposeshttp://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.-i_Ows8j7JYu0p07jOFMEA..
, the value will change on every approval, please make note of the actual access token you received.3600
seconds, i.e one hour.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..
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.
curl -i -H'Authorization: Bearer -i_Ows8j7JYu0p07jOFMEA..' http://localhost:8080/ords/ordstest/test/emp/
-i_Ows8j7JYu0p07jOFMEA..
with the value of the access token you noted in the previous stepHTTP/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"}
]
}
Authorization
header is omitted then a 401 Unauthorized
status will be returned insteadPlease consult the Oracle REST Data Services Developer Guide for further information on developing, securing and accessing RESTful Services.