Getting Started with Oracle REST Data Services

This tutorial is designed to let you get started quickly developing RESTful services using Oracle REST Data Services.

Getting Started with RESTful Services

Prerequisites

The following are the prerequisites before you start performing the steps in this tutorial:

Note: The latest version of SQLcl can be downloaded from one of the following:

This tutorial assumes the following:

The examples in this tutorial assume that Oracle REST Data Services has been installed and configured in a single instance database or Pluggable Database (PDB). The examples and images in this tutorial refer to the PDB as ORCLPDB1.

Client Applications used in this tutorial for Creating the RESTful Services

The examples in this tutorial use the following client applications:

Web browser requirements

For a complete list of currently supported web browsers, refer to Oracle Software Web Browser Support Policy.

Creating a RESTful Service Using Oracle SQLcl

This section describes the steps for creating a RESTful service using Oracle SQLcl.

Note: Oracle recommends that you perform the following steps in this tutorial using the specified names for schemas and database objects. After you have completed the tutorial, you can follow the same steps again using alternate schema and database object names.

Perform the following steps to create a RESTful service:

  1. Creating a New Database User and REST-enabling Schema

  2. REST-enabling a Schema

  3. Accessing the Database Actions

  4. Auto REST-enabling a Table

  5. Testing the Auto REST-enabled Object

Creating a New Database User and REST-enabling Schema

To create a new user, perform the following steps:

  1. Using SQLcl, connect to your database as an administrator or using an account with the DBA role. Figure 1: Connecting to the Database as an Administrator or an User with DBA Role

Description of the illustration connecting-database-administrator-or-user-dba-role.png

  1. Run the following commands to create a new ORDSTEST user with the required privileges, roles, and tablespace:

    CREATE USER ORDSTEST IDENTIFIED BY <password>;
GRANT "CONNECT" TO ORDSTEST;
GRANT "RESOURCE" TO ORDSTEST;
GRANT UNLIMITED TABLESPACE TO ORDSTEST;

    Figure 2: ORDSTEST User with required Privileges, Roles, and Tablespace

Description of the illustration ordstest-user-required-privileges-roles-and-tablespace.png

REST-enabling a Schema

To REST-enable a schema, connect to your database as the ORDSTEST user and then run the following PL/SQL procedure:

ORDS_ADMIN.ENABLE_SCHEMA

Figure 3: Executing the ORDS.ENABLE_SCHEMA PL/SQL Procedure

Description of the illustration executing-ords-enable-schema-procedure.png

Figure 1-4 ORDS.ENABLE_SCHEMA PL/SQL Procedure

See Also: ORDS PL/SQL Package Reference

Accessing the Database Actions

To access the Database Actons, perform the following steps:

Now that ORDSTEST user schema is REST-enabled, you can now access Database Actions as the ORDSTEST user.

  1. Navigate to the URL: http://[server]:[port]/ords/sql-developer to display the Sign-in page. Figure 5: Accessing Database Actions as the ORDSTEST User

Description of the illustration log_in_database_actions.png

  1. Sign-in as the ORDSTEST user with <username>: ORDSTEST and <password>: [Password].

  2. Click on Sign in button.

  3. Database Actions Launchpad home page appears. It comprises of six main categories: Development, Data Studio, Administration, Monitoring, Downloads, and Related Services. Each category consists of feature-based icons that you can click to navigate to the respective pages available to the ORDSTEST user. Figure 6: Database Actions Launchpad

Description of the illustration database_actions_launchpad.png

See Also: About Oracle Database Actions

Auto REST-enabling a Table

Perform the following steps to connect as a new user and auto REST-enable a table: Note: The tasks listed in this section are completed with the user logged-in as an ORDSTEST user.

  1. Creating a Database Table

  2. Inserting the Sample Data

  3. REST-enabling the EMP Table

Creating a Database Table

Perform the following steps to create an EMP table: 1. From the Database Actions launchpad, select SQL, under the Development category of the dashboard. Figure 7: Selecting SQL Worksheet

Description of the illustration selecting-sql-worksheet.png

  1. After the SQL Worksheet is loaded, enter the following SQL query to create the EMP 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)
        );
Inserting the Sample Data

  1. After the EMP table is successfully created, insert the following sample data:

        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;
REST-enabling the EMP Table

Perform the following steps to auto REST-enable the table: 1. From the navigator panel, right mouse-click on the table name, navigate to REST and then click Enable. Figure 8: Auto REST-enabling the EMP table

Description of the illustration selecting-rest-enable-navigator-panel.png

  1. A REST Enable Object screen appears. After reviewing the parameters that got automatically generated, click on Enable located at the bottom of the screen.

  2. Figure 9: REST Enable Object Screen

Description of the illustration inspecting-parameters-emp-auto-rest-slider.png

  1. A message slider appears confirming that the EMP table has been REST-enabled. Figure 10: Table REST-enabled Confirmation

Description of the illustration ords-confirming-successful-auto-rest-emp-table.png

Testing the Auto REST-enabled Object

Note: An icon next to the database objects indicates that the database objects have been auto-REST enabled.

Figure 11: Icon showing Database Object is Auto REST-enabled

Description of the illustration plug-icon-indicating-auto-rest-enabled-table.png

Note: If you do not see the icon, then click Refresh in the Navigator Panel to display the icon.

To review and retrieve the REST endpoints for the EMP table, perform the following steps:

  1. Right-click on the name of the object, click REST and then select cURL command: Figure 12: Locating Curl Command to Test the Rest Endpoint

Description of the illustration locating-curl-command-test-rest-endpoint.png

  1. A slider cURL for the table EMP appears with the following HTTP methods available to an auto-REST enabled resource:

    • GET ALL

    • GET Single

    • POST

    • BATCH LOAD

    • PUT

    • DELETE

  2. Copy the URL portion of the GET ALL cURL command.

  3. Open a new browser and paste the URL in the address bar, and press Enter on your keyboard. Figure 13: Results Atfer Testing the Emp URL in the Browser

Description of the illustration results-atfer-testing-emp-url-browser.png

  1. A list of the first 25 items in the EMP table are displayed.

  2. If you collapse the preceding items list, it reveals the other helpful links that are automatically included with all the auto-REST enabled resources.

    Description of the illustration other-useful-links.png

Creating a RESTful Service Through the REST Workshop

This section explains how to create a RESTful service using REST Workshop of Database Actions. The REST Workshop enables you to create and edit the RESTful service definitions.

Perform the following steps to create and test a RESTful service in the REST Workshop: 1. Navigate to the REST Workshop

  1. Create a Module

  2. Create Template

  3. Create Handler

  4. Test the RESTful Service

Navigate to the REST Workshop

Perform the following steps to navigate to the REST Workshop:

  1. Log in as the ORDSTEST user and navigate to the Database Actions Launchpad.

  2. Select REST under the Development section. Figure 14: Navigating to the REST Workshop from the Launchpad

Description of the illustration navigating-rest-workshop-launchpad.png

Create a Module

To create a module, perform the following steps: 1. After the REST workshop screen loads, click on Modules widget.

Figure 15: Modules Dashboard

Description of the illustration modules-dashboard.png

  1. Modules dashboard appears. Click on Create Module button located at the upper right-hand corner of the dashboard.

  2. A Create Module slider appears. Figure 16: Entering Values in the Create Module Screen

Description of the illustration entering-values-modules-fields.png

Enter the following values in the respective fields:

- **Module Name**: Any desired name for the connection. For example, `demo.module`.

- **URI Prefix**: `/demo/`

- **Pagination Size**: `25`

- In the **Protected by Privilege** field, select `Not Protected`
  1. Click on Create, the module settings are saved, and a confirmation message is displayed to confirm that the module is created.

Create Template

Perform the following steps to create a template:

  1. After creating a Module, you will be automatically taken to the Create Template screen. Click on Create Template button.

    Description of the illustration create-template.png

  2. A Create Template screen appears. In the URI Template field, enter emp/. Note: For this demonstration, retain the detault settings.

Figure 17: Create Template Screen

Description of the illustration create-template1.png

  1. You are automatically taken to the Create Handler page.

Create Handler

Perform the following steps to create the Get handler and review the SQL query results: Note: For this tutorial, a GET method is created.

  1. Click on Create Handler button to display the Create Handler screen. Verify the following settings:

    • Method: GET

    • Items Per Page: 7

    • Source Type: Collection Query

    Figure 18: Create Handler

Description of the illustration create-handler.png

  1. In the Source field, enter the following SQL query:

    SELECT
  INITCAP(ENAME) name,
  lower(job) job,
  TO_CHAR(sal,'9G999','NLS_NUMERIC_CHARACTERS=",."') salary,
  hiredate
FROM
emp

    Click on the Create button to automatically open the Resource Handlerpage with a confirmation message indicating that the handler is created.

  2. You can then test the SQL query. To do so, click on the Play icon. The results of the query appears in the output window. Figure 19: Resource handler SQL query results

Description of the illustration results-handler-sql-query.png

Test the RESTful Service

Perform the following steps to test the RESTful service: 1. Click on the Open in a new tab icon. Figure 20: New Tab for Testing the Endpoint

Description of the illustration new-tab-testing-restful-service.png

  1. A new browser tab appears. Enter the Get URI in the browser tab.

  2. A JSON response is returned in the browser window. Figure 21: JSON Response from GET Method

Description of the illustration endpoint-json-response.png

Creating a Privilege Using Database Actions

This section describes how to create a privilege and control access to protected resources.

Privileges are defined to control access to protected resources. Privileges restrict access to those users who have access to at least one from a set of the specified roles. A privilege is then associated with one or more resource modules. Before accessing those resource modules, the user must be authenticated and then authorized to ensure that the user has one of the required roles.

Steps to Create a Privilege

This section describes the steps to control access to the protected resources.

Perform the following steps to create a privilege: 1. Navigate to the REST Workshop

  1. Under the Security menu item, select Privileges. Figure 22: Selecting Privileges Menu Option

Description of the illustration privileges-menu-option.png

  1. Click on Create Privilege to display the Create Privilege screen.

  2. Enter the following values in the respective fields:

    • Label: Demo module privilege

    • Name: demo.module.privilege

    • Description: A Privilege created for demonstrating privileges for the demo.module Resource Module.

    • Navigate to the Protected Modules table. Move the demo.module Resource Module from the left column to the right column. This ensures that the demo.module Resource Module is associated with this Privilege. Figure 23: Associating Resource Module with the Privilege

Description of the illustration demo-module-moved-selected-column.png

- Click on the **Create** button. A confirmation message appears indicating successful creation of a new privilege.

Note: The newly created privilege can now be viewed in the Privileges dashboard.

Figure 24: Privilege Created

Description of the illustration privilege-created-confirmation.png

  1. To test the Privilege created, navigate to the path of the demo.module. Figure 25: Navigating to the demo module to Test the Privilege

Description of the illustration navigate-demo-module-test-privilege.png

  1. Copy the emp/ URI. Figure 26: Copying the URI to Test the Privilege

Description of the illustration copy-uri-test-privilege.png

  1. Sign out of Database Actions. Open a new browser window. Paste the URI in the address bar and press Enter

  2. A 401 unauthorized error message is displayed to indicate that the resource is protected. Notice that a sign-in prompt appears. Since this Privilege has not been associated with a specific role, any user who has been granted the Connect role can sign-in to view the response from this request.

  3. Sign-in with your database credentials to view the resource. Figure 27: Sign-in to Test Path Privilege

Description of the illustration sign-test-path-privilege.png

  1. After you sign-in, the contents of the JSON document can be viewed.

Register an OAuth Client Application to Access the REST API

This topic explains how to register your applications (called third-party applications here) to access a REST API.

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 authenticated end user.

Before a third party application can access a REST API:

Note: In a real application, you must provision specific users who can register OAuth clients. Such users must be granted the OAuth Client Developer role.

Note: The following example is not intended to serve as a full-featured demonstration for creation and integration for a third party application. The example provided in this section, only outlines the core concepts of the OAuth 2.0 protocol.

Registering your Application to Access a REST API

Perform the following steps to register your application to access a REST API:

  1. From the REST Workshop dashboard, select OAuth Clients option from the Security menu. Figure 28: Navigating to OAuth Client Menu Option

Description of the illustration navigate-oauth-clients-menu-option.png

  1. The OAuth Clients dashboard appears. From the OAuth Clients dashboard, click on Create OAuth Client. Figure 29: Create OAuth Client

Description of the illustration click-create-oauth-client.png

  1. The Create OAuth Client slider appears. Figure 30: Checking Client Credentials Selected

Description of the illustration checking-client-credentials-selected.png

  1. Enter the following values in the Create OAuth Client slider:

    • Name: example_oauth_client

    • Description: An example OAuth 2.0 client using the Client Credentials grant type.

    • Support URI: https://example.com

    • Support Email: email@example.com

    Figure 31: Entering Values in Create OAuth Client Slider

Description of the illustration entering-values-create-oauth-screen-fields.png

  1. Click on Create.

  2. After returning to the OAuth Clients dashboard, you can find the newly-created OAuth client. Figure 32: OAuth Client Created

Description of the illustration oauth-client-created-dashboard.png

  1. After the OAuth Token appears, select the correct shell environment. Figure 33: Selecting Correct Shell Environment

Description of the illustration selectin-correct-shell-your-environment.png

  1. Copy brearer token curl command to your clipboard.

  2. Using the Client ID and Client Secret provided to you, issue the following curl command to obtain an Access Token:

    curl \

--user Bx2vfYZkLa9D8_CZXvFL0Q..:xH1nn_CPtitiW5vOUQXrrg.. \

--data 'grant_type=client_credentials' \
http://localhost:8080/ords/ordstest/oauth/token

  3. You will receive an Access Token, with expiration time. Figure 34: Access Token with Expiration

Description of the illustration access-token-expiration.png

  1. You can now access the emp/ endpoint. Create your curl command ensuring that you have included the Access Token as a header in your curl command:

    curl -H "Authorization: Bearer PWMGyNYtzhg9J1r85_oJGQ" http://localhost:8080/ords/ordstest/demo/emp/ | jq

    Figure 35: jq Response from the Get Request

Description of the illustration jq-response-get-request.png

Note: You can optionally pipe in the jq command so that the JSON response payload is structured in a readable format.

Creating a RESTful Service Using Oracle SQL Developer

This section describes the steps involved in developing the RESTful services using Oracle SQL developer desktop application (aka client).

Topics:

REST-Enable a Database Table

To enable a table for REST access, follow these steps.

Note: It is recommended that you follow the steps as closely as possible, including using the specified names for schemas and database objects. After you have successfully completed the tutorial using this approach, feel free to try it again using other values if you wish.

  1. Create a user ordstest with the following privileges or roles:
CREATE USER ordstest IDENTIFIED BY <password>;
GRANT "CONNECT" TO ordstest;
GRANT "RESOURCE" TO ordstest;
GRANT UNLIMITED TABLESPACE TO ordstest

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

  2. Create a database table. For example, enter the following in the SQL Worksheet to create an example table named EMP:

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)
  );
  1. Insert some sample data into the table. For example:
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;

  1. Enable the schema of the EMP table for REST. In SQL Developer, right-click the ordstest connection, and select REST Services > Enable RESTful Services to display the following wizard page:

    Figure 36: Enabling the Schema of the EMP Table for REST

Description of the illustration enable-rest-services-1.jpg

**Enable schema**: Enable this option.

**Schema alias**: Accept `ordstest` for the schema alias.

**Authorization required**: For simplicity, this tutorial does not require authorization, so disable this option.

Click **Next**.

  1. On the RESTful Summary page of the wizard, click Finish.

  2. Enable the EMP table. In SQL Developer, right-click EMP table in the Connections navigator, and select REST Services > Enable RESTful Services to display the following wizard page:

    Figure 37: REST Enabling the EMP Table

Description of the illustration enable-rest-services-3.jpg

**Enable object**: Enable this option (that is, enable REST access for the EMP table).

**Object alias**: Accept `emp` for the object alias.

**Authorization required**: For simplicity, this tutorial does not require authorisation, so disable this option.

  1. On the RESTful Summary page of the wizard, click Finish.

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

Note: DELETE, PUT, POST, and metadata-catalog endpoints are also auto-generated.

  1. Test the REST endpoint. In a web browser, enter the URL ` http://localhost:8080/ords/ordstest/emp/` as shown in the following figure:

    • The ORDSTEST schema has been exposed at the /ordstest/ path.

    • The EMP table has been exposed at the /emp/ path.

    Figure 38: Testing the REST Enabled Table

Description of the illustration rest_enable_emp.png

Related Topics

Creating a RESTful Service through the Connections Navigator

This section explains how to create a RESTful service by using REST Data Services node in the Connections navigator. Oracle REST Data Services provides an option through the Connections navigator that enables you to create and edit RESTful service definitions.

To create and test a RESTful service by using REST Data Services node in the Connections navigator, follow these steps:

  1. Under ordstest schema, select REST Data Services. Figure 39: REST Data Services option under Connections Navigator

Description of the illustration rest-data-services_option.png

The following steps create and test the RESTful service.

  1. Under REST Data Services node, right-click the Modules node, click New Module, and enter information on the Specify Module page:

    Figure 40: Entering Information on the Specify Module Page

Description of the illustration module_demo.png

**Module Name**: Any desired name for the connection. For example, `demo`

**URI Prefix**: `/demo/`

**Publish - Make this RESTful Service available for use**: Enable (check).

**Pagination Size**: `25`

  1. Click Next, and enter information on the Specify Template page:

    Figure 41: Entering Information on the Specify Template Page

Description of the illustration template_emp.png

**URI Pattern**: `emp/`

Accept the defaults for the remaining options.

  1. Click Next to go to the RESTful Summary page of the wizard, then click Finish. Expand the Modules node to display the resource module that you created.

  2. Expand the module, Demo and right click on the emp/node, select Add handler and then select GET method.

  3. Enter the information on the Create Resource Handler page. Figure 42: Entering Information on Create Resource Handler Page:

Description of the illustration create_resource_handler.png

**Source Type**: `Collection Query`

**Pagination Size**: `7`

Click **Apply**.

Next step is to define the query for the GET resource handler.

  1. In the SQL Worksheet, enter the following query:

    SELECT
   INITCAP(ENAME) name,
  lower(job) job,
  TO_CHAR(sal,'9G999','NLS_NUMERIC_CHARACTERS=",."') salary,
  hiredate
FROM
emp

  2. Click Save REST Handlericon. A confirmation message appears in the Messages - Log pane to confirm that the handler is saved to the database.

Note: If you do not see the Messages - Log pane, go to the View menu and then select Log.

  1. Test the RESTful service. In a web browser enter the URL http://localhost:8080/ords/ordstest/demo/emp/ as shown in the following figure:

    • The ORDSTEST schema has been exposed at the /ordstest/ path.

    • The query has been exposed at the /demo/emp/ path.

    Figure 43: Testing URL in a Web Browser

Description of the illustration conn_nav_get_ex.png

Related Topics

Creating a Privilege under REST Data Services

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 authorized to ensure that the user has one of the required roles.

To protect resources, follow these steps.

  1. Create a privilege. In SQL Developer, right-click the Privileges node under REST Data Services and select New Privileges to display the Create Privilege dialog box:

    Figure 44: Create Privilege Dialog Box

Description of the illustration privilege_demo.png

**Name**: `Demo`

**Title**: `Example Privilege`

**Description**: `Demonstrate controlling access with privileges`

**Protected Modules**: Ensure that the list includes the `Demo` module. Use the arrow button to move it if necessary.

Click **Apply**.

You have now created a privilege that protects the demo module. However, you have not restricted the privilege to any particular role; this will just require that the user be authenticated before accessing the demo module (the next step).

  1. Test the RESTful service. In a web browser enter the following URL:

    http://localhost:port/ords/ordstest/demo/emp/

  2. Click the link to sign in, and enter the test_developer credentials.

Note: To create a test_developer user refer toCreate a RESTful Service from a SQL Query section.

A JSON document similar to the following is displayed:

Figure 45: JSON Document After Signing in

Description of the illustration conn_nav_get_ex.png

Creating a Role

This section explains how to create and delete a role.

To create a role, follow these steps:

  1. Under REST Data Services, right clickRolesand then click New Role.

  2. In theCreate Role dialog box, enter the name of the role you want to create.

    Figure 46: Entering the New Role Name

Description of the illustration new_role_step2.png

  1. Click Apply, the new role is now created.

    To rename or delete a role, right click on the role name and choose one of the following options:

    • Rename: to change the role name.

    • Delete: to remove the role.

Creating a RESTful Service from a SQL Query

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. This option is available when you do not have direct access to the database. 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.

To create a RESTful service from a SQL query, follow these steps.

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

    Note:

    • If the ORDS_CONFIG environment variable has not been set, then run the following command:
    ords --config [/directory/path/to/ords_config] config user add [Username] [Role]

    • For multiple roles, specify ["Role_01, Role_02, Role_03, and so on"]

    • If the ORDS_CONFIG environment variable has already been set (that is, you would not need to include the --config [/directory/path/to/ords_config] option), then run the following command:

    ords config user add [Username] [Role]
    • For multiple roles, specify ["Role_01, Role_02, Role_03, and so on"] .

    The text within square brackets [] is intended to be replaced with user-defined input or directory path.

    ords config user add test_developer "SQL Developer"

    • You will be prompted to enter a password.

    • This command 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 module’s API.

    • The user details are stored in a file named credentials in the ORDS configuration folder. However, 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.

    The remaining steps create and test the RESTful service.

  2. Create RESTful connection. In SQL Developer, select View > REST Data Services > Development.

  3. In the REST Development pane, right-click REST Data Services > Connect.

  4. In the RESTful Services Connection dialog box, click the + (plus sign) icon to add a connection to the list available for selection.

  5. In the New RESTful Services Connection dialog box, enter the necessary information:

    Figure 47: Entering Information for New RESTful Services Connection

Description of the illustration rest-connection-2.jpg

**Connection Name**: Any desired name for the connection. Example: `ordstest`

**Username**: `test_developer`

**http** or **https**: Select `http` for simplicity in this tutorial.

**Hostname**: `localhost`

**Port**: `8080`

**Server Path**: `/ords`

**Workspace**: `ordstest`

Click **OK**, then enter the password for the `test_developer` user at the prompt.

  1. Create the module. Right-click the Modules node in the REST Development view, click New Module, and enter information on the Specify Module page:

    Figure 48: Entering Information on the Specify Module Page

Description of the illustration resource-module-1.jpg

**Module Name**: Any desired name for the connection. Example: `test`

**URI Prefix**: `/test`

**Publish - Make this RESTful Service available for use**: Enable (check).

**Pagination Size**: `7`

  1. Click Next, and enter information on the Specify Template page:

    Figure 49: Entering Information on the Specify Template Page

Description of the illustration resource-module-2.jpg

**URI Template**: `/emp/`

Accept the defaults for the remaining options.

  1. Click Next, and enter information on the Specify Handler page:

    Figure 50: Entering Information on the Specify Handler Page:

Description of the illustration resource-module-3.jpg

**Method**: `GET`

**Requires Secure Access**: Disable (uncheck) for this tutorial.

**Source Type**: `Collection Query`

**Pagination Size**: `7`

  1. Click Next to go to the RESTful Summary page of the wizard, then click Finish.

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

  2. Define the query for the GET resource handler.

    1. Expand the test node under the Modules node in the REST Development view.

    2. Expand the /emp/ node, right-click the GET node, and select Open.

    3. In the SQL Worksheet that opens for GET /emp/, enter the following SQL query:

    select * from emp

    1. Right-click on the test node under the ‘Modules’ node in the ‘REST Development’ view

    2. Click ‘Upload...’. A confirmation dialog will appear confirming the module has been uploaded.

  3. Test the RESTful service. In a web browser enter the URL http://localhost:8080/ords/ordstest/test/emp/ as shown in the following figure:

    • The ORDSTEST schema has been exposed at the /ordstest/ path.

    • The query has been exposed at the /test/emp/ path.

    Figure 51: Testing the RESTful Service Created from a SQL Query

Description of the illustration sql-query_enable.png

Related Topics

Protect Resources

Up to this point the tutorial has deliberately disabled security on the RESTful endpoints you created, because it is easier to test them without security. In this topic you 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 authorized to ensure that the user has one of the required roles.

To protect resources, follow these steps.

  1. Create a privilege. In SQL Developer, right-click the Privileges node in the REST Development view and select New Privileges to display the Edit Privilege dialog box:

    Figure 52: Edit Privilege Dialog Box

Description of the illustration privilege.jpg

**Name**: `test`

**Title**: `Example Privilege`

**Description**: `Demonstrate controlling access with privileges`

**Protected Modules**: Ensure that the list includes the `test` module. Use the arrow button to move it if necessary.

Click **Apply**.

  1. Right click the test privilege and click Upload.

    A dialog box confirms that the privilege has been uploaded.

    You have now created a privilege that protects the test module. However, you have not restricted the privilege to any particular role; this will just require that the user be authenticated before accessing the test module (the next step).

  2. Test the RESTful service. In a web browser enter the following URL:

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

  1. Click the link to sign in, and enter the test_developer credentials.

    The contents of the JSON document are displayed.

Register an OAuth Client Application

This topic explains how to register your applications (called “third-party” applications here) to access a REST API.

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. And before the application can be registered, it must be assigned a user identity that enables the user to register applications. Users possessing the SQL Developer role (such as the test_developer user created in Creating a RESTful Service from a SQL Query) are permitted to register OAuth clients.

Tip:

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

This topic outlines how to complete these actions. It is not a full-featured demonstration of how to create and integrate a third party application; it just outlines the concepts involved.

  1. Register the client application.

    1. In a web browser enter the following URL:
    http://localhost:8080/ords/ordstest/oauth/clients/

    1. At the prompt, click the link to sign in and enter the credentials for the test_developer user.

    2. Click New Client and enter the following information:

      Name: Test Client

      Description: An example OAuth Client

      Redirect URI: http://example.org/redirect

      Support e-mail: info@example.org

      Support URI: http://example.org/support

      Required Privileges: Example Privilege

    3. Click Create.

      The client registration is created, and the Authorization URI for the client is displayed. You have created a client that will use the Implicit Grant authorization flow (explained at https://tools.ietf.org/html/rfc6749#section-4.2).

      Note the Client Identifier assigned to the client and the Authorization URI value. These values are used to start the authorization flow (next major step).

  2. Approve the 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:

    1. In a web browser, enter the Authorization URI that you noted in the previous step. The URL should look like the following (though you should not copy and paste in this example value):
    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. Be sure you are using the correct `client_id` value. Do not use the value in the preceding example; 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 Oracle REST Data Services 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.

    1. At the prompt, click the link to sign in and enter the credentials for the test_developer user.

    2. Review the access being requested, and click Approve.

      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
     When registering the OAuth client, you specified `http://example.org/`redirect as the Redirect URI. On completion of the approval request, the browser is 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 authorized to access the REST API, and including the access token in every subsequent request to the REST API. However, in this tutorial you just make note of the access token value and manually create a HTTP request with the access token included, as explained in the next major step.

 The value of the access token (which in the preceding example is `-i_Ows8j7JYu0p07jOFMEA..`) will change on every approval.

 Note that the access token expires. In the preceding example it expires after 3600 seconds (`&expires_in=3600`), that is, one hour.

  3. Issue an authorized request.

    After an access token has been acquired, the client application must 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 (explained at https://datatracker.ietf.org/doc/html/rfc2616#section-14.8) as in the following example:

Host: localhost:8080
GET /ords/ordstest/test/emp/
Authorization: Bearer -i_Ows8j7JYu0p07jOFMEA..
To emulate creating a valid HTTP request, use the cURL command line tool (if necessary, install cURL). In a real application this request would be performed by the client making an HTTP request, such as an `XMLHttpRequest`. For example:

curl -i -H'Authorization: Bearer -i_Ows8j7JYu0p07jOFMEA..' http://localhost:8080/ords/ordstest/test/emp/
However, in this example replace `-i_Ows8j7JYu0p07jOFMEA..` with the access token value that you previously noted.

Output similar to the following JSON document 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"}
 ]
}
However, if the `Authorization` header is omitted, then the status `401 Unauthorized` is returned instead.

See Also: curl_haxx