Use SODA for REST

Oracle Database Exadata Express Cloud Service supports SODA for REST.

Topics

• About SODA for REST

• SODA for REST Prerequisites

• Set up Users and Roles for SODA for REST

• Determine the SODA for REST HTTP Operation URIs

• Get Started with SODA for REST Operations

About SODA for REST

Oracle Database Exadata Express Cloud Service supports Simple Oracle Document Access (SODA) using Representational State Transfer (REST).

SODA for REST can be used from any modern programming language capable of making HTTP requests. For further details including a complete list of SODA for REST HTTP operations available for the SODA for REST API, see REST Data Services SODA for REST Developer's Guide.

SODA for REST Prerequisites

Prerequisites must be satisfied in order to use SODA for REST.

The following must be performed or known before attempting to use SODA for REST:

• You must enable SODA for your service. See Enable SODA for Your Service.

• You must provide the appropriate roles to users using SODA for REST. See Assign User Roles for SODA for REST.

• You must know how to determine the REST URI. See Determine the SODA for REST HTTP Operation URIs.

Set up Users and Roles for SODA for REST

Service users that want to use SODA for REST must be assigned the appropriate role and must use the proper authentication mechanism.

Topics

• Assign User Roles for SODA for REST

• Create Custom Roles

• About Authentication Mechanisms

Assign User Roles for SODA for REST

Before a user can access SODA for REST, users must be assigned the predefined roles of Database Administrator or Database Developer and your service must be enabled for SODA for REST. It is also possible to create custom roles for accessing SODA for REST.

The initial Service Administrator User for the service is automatically assigned the Database Administrator and Database Developer roles. Additional users need to be manually assigned these roles. You can change the roles assigned to users from the Infrastructure Console. Roles can also be assigned during user creation. See Adding Users and Assigning Roles in Getting Started with Oracle Cloud.

To assign Database Administrator and Database Developer roles to a user:

1. Sign in to your account. See Sign In To Your Account.
   Infrastrucutre Console displays.
2. Click Users from the navigation menu.
   The Users page displays showing all users provisioned for your service.
3. Find the user you want to assign the role to, and click the menu icon.
   A drop down list appears.
4. Select Manage Roles.
   The Manage Roles dialog display.

   
   

   
   Description of the illustration manage_roles_dialog.png

   
   
5. Under Available Roles, select the Database Administrator and Database Developer roles you want to assign to the user and click the arrow to move them to Selected Roles.
   All roles you want to assign to this user are listed under Selected Roles.
6. Click Save.

Create Custom Roles

In addition to the predefined roles, such as Database Administrator and Database Developer, you can create your own custom roles. A custom role, for example, allows you to guard access to a particular SODA document collection, instead of a whole schema.

The custom role name is created and defined from the Infrastructure Console Users page. The custom role can then be assigned to users. You need to perform PL/SQL commands to define what the custom role does. See Security in Oracle REST Data Services SODA for REST Developer’s Guide.

To create your own custom role:

1. Sign in to your account. See Sign In To Your Account.
   Infrastructure Console displays.
2. Select Users from the navigation menu
   
   Description of the illustration eecs_ic_users.pngThe Users page displays showing all users provisioned for your service.
   Description of the illustration ic_users.png
3. Select Custom Roles.
   The Custom Roles page appears.
4. Click Add.
   The Add Custom Role dialog display.

   
   

   
   Description of the illustration add_custom_role.png

   
   
5. Fill out the fields in the dialog. The Role Name is the only required field. This name is used later to identify the role when assigning it to a user.
6. Click Add.
   The Custom Roles page appears and lists the added custom role.
7. Add this new custom role to a user. Follow steps in Assign User Roles for SODA for REST, but instead of adding the Database Administrator and Database Developer roles, add the custom role.
8. Now you need to define what this custom role does by creating and executing PL/SQL commands that define the privilege mapping for this custom role. For example, assuming the EmployeeRole custom role has been created and assigned to a user, in the schema that holds the collection, execute this:

   Note:

   Before running this example, you must connect to the schema that holds the collection with SQL*Plus, SQLcl, or SQL Developer.

   declare l_patterns owa.vc_arr; 
   begin
      l_patterns(1) := '/soda/latest/employee';
      l_patterns(2) := '/soda/latest/employee/*';
      ords.create_role('EmployeeRole');
      ords.create_privilege(p_name => 'EmployeePrivilege', p_role_name => 'EmployeeRole');
      ords.create_privilege_mapping(p_privilege_name => 'EmployeePrivilege', p_patterns => l_patterns);   commit;
    end;

   This example creates a privilege mapping that ensures only users with this custom role named EmployeeRole, can access the employee collection. For more information about this example, see Security in Oracle REST Data Services SODA for REST Developer’s Guide.

About Authentication Mechanisms

Simple Oracle Document Access (SODA) for REST is built on top of Oracle REST Data Services (ORDS), which provides various authentication mechanisms, such as basic authentication, OAuth, and so on.

See Authentication Mechanisms in Oracle REST Data Services SODA for REST Developer’s Guide.

Determine the SODA for REST HTTP Operation URIs

A SODA for REST HTTP URI includes the host name of your Oracle Database Exadata Express Cloud Service and, for non-default schemas, the schema name. The default schema name is the name of the schema your service was provisioned with and is not included in the URI.

A SODA for REST HTTP Operation has a Universal Resource Identifier (URI) of this form:

https://hostname/apex/schema(only if non-default schema)/soda/latest

where:

• hostname is the host name found in the Service Instance URL displayed on the Service Details page.

• schema is the name of the non default schema. For the default schema the schema name is not included.

Example URL Pattern for Default Schema:

https://myhostname/apex/soda/latest

Example URL Pattern for Non-default Schema:

https://myhostname/apex/myschemaname/soda/latest

To find the host name:

1. Go to the Service Details page for your service. See Access Service Details.
2. On the Service Details page, locate the Service Instance URL. The host name is the first portion of the URL after https:// and before the subsequent /.
   
   

   
   Description of the illustration myhostname.png

   
   

Get Started with SODA for REST Operations

Some basic SODA operations using REST are provided to demonstrate the steps needed to list all collections, create a new collection, insert a document, retrieve a document, and finally to delete the created collections that allow documents to be stored in, and retrieved from, document collections.

For further details on the example operations described below, see Getting Started with SODA for REST and SODA for REST HTTP Operations in Oracle REST Data Services SODA for REST Developer’s Guide.

To perform some simple SODA for REST operations using cURL a common command-line tool:

Note:

The following example operations use Basic Authentication with a fictitious user [e-mail] with password password and the user has a role of either Database Administrator, Database Developer or both. It is not secure to specify usernames and passwords in clear text as part of the cURL command. Although, for the purposes of brevity, the sample commands shown here use clear text for the username and password.

1. Make sure SODA for REST prerequisites have been satisfied. See SODA for REST Prerequisites.
2. To list all collections in the default schema, run this command:

   curl -i -X GET -u [e-mail]:password https://exadata1.oraclecorp.com/apex/soda/latest

   Command results assuming no existing collections in the default schema exist:

   {"items":[],"more":false} 

3. To create a new collection named MyCollection, run this command:

   The curl option -i in the following command is used to include output headers in the response.

   curl -i -X PUT -u [e-mail]:password https://exadata1.oraclecorp.com/apex/soda/latest/MyCollection 

   Command result contains:

   ... HTTP/1.1 201 Created ... 

4. To list all collections in the default schema again, run this command:

   curl -i -X GET -u [e-mail]:password https://exadata1.oraclecorp.com/apex/soda/latest 

   Command result includes:

   The following JSON response shows the default schema contains the MyCollection collection created in a previous step. Metadata for this collection is shown under "properties" as part of the returned JSON (this part of the output is abridged for brevity). 

   {"items":[{"name":"MyCollection",
              "properties":{"schemaName":"BXJHVBGSPT",
                            "tableName":"MyCollection",
                            "keyColumn":{"name":"ID", ... }
                           },
              "links":[{"rel":"canonical",
                        "href":"http://exadata1.oraclecorp.com:80/apex/soda/latest/MyCollection"}]}],
   "more":false}

5. To insert a document, with content {"name" : "Alexander"}, into the MyCollection collection, run this command:

   Note:

   The my.json file used in the following command, must have been created with {"name" : "Alexander"} as the content.

   curl -X POST -u [e-mail]:password --data-binary @my.json -H "Content-Type: application/json" 
   https://exadata1.oraclecorp.com/apex/soda/latest/MyCollection

   Command result looks similar to this:

   Note:

   The response shows auto-generated unique id for the inserted document, as well as etag, and last-modified and created are based on timestamps. The actual values of id, etag, lastModified, and created will be different in the response you get. The id is used in subsequent steps to get the document from the collection.

   {
     "items" : [ 
       { 
         "id" : "2FFD968C531C49B9A7EAC4398DFC02EE",
         "etag" : "C1354F27A5180FF7B828F01CBBC84022DCF5F7209DBF0E6DFFCC626E3B0400C3",
         "lastModified":"2016-07-22T21:25:19.564394Z",
         "created":"2016-07-22T21:25:19.564394Z"
       }
     ],
     "hasMore" : false,
     "count" : 1
   } 

6. To retrieve the inserted document from the collection using the document unique ID, run this command:

   Note:

   In the following command example, the document ID shown is different from the actual document ID you must use to retrieve the document when you run the command. You must replace the document ID, 2FFD968C531C49B9A7EAC4398DFC02EE, with the auto-generated unique document ID you received after running the previous insert document command step.

   curl -X GET -u [e-mail]:password 
   https://exadata1.oraclecorp.com/apex/soda/latest/MyCollection/2FFD968C531C49B9A7EAC4398DFC02EE 

   Command results:

   The response body contains the document inserted in a previous step.

   {"name" : "Alexander"}

7. To drop the collection, run this command:

   curl -i -X DELETE -u [e-mail]:password 
   https://exadata1.oraclecorp.com/apex/soda/latest/MyCollection

   Command results contain:

   ... HTTP/1.1 200 OK ...