H Getting Started with RESTful Services

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

1. Create a database schema named ordstest for use in trying 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
   

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

3. 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)
     );
   

4. 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;
   

5. 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 H-1 Enabling the Schema of the EMP Table for REST

   
   Description of "Figure H-1 Enabling the Schema of the EMP Table for REST"

   Enable schema: Enable this option.

   Schema alias: Accept ordstest for the schema alias.

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

   Click Next.

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

7. 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 H-2 REST Enabling the EMP Table

   
   Description of "Figure H-2 REST Enabling the EMP Table"

   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.

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

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

9. 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 H-3 Testing the REST Enabled Table

   
   Description of "Figure H-3 Testing the REST Enabled Table"

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 H-9 Edit Privilege Dialog Box

   
   Description of "Figure H-9 Edit Privilege Dialog Box"

   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.

2. 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).

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

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

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

   The contents of the JSON document are displayed.

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.

• 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. 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 Create a RESTful Service from a SQL Query) are permitted to register OAuth clients.

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/
      

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

   3. 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

   4. 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.

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

   3. 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 http://tools.ietf.org/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.