User-specific web services using ORDS

Web services are an efficient means of providing access to web resources to a variety of clients. This access can be provided regardless of the client by relying on a standardized architecture to ensure interoperability and predictable behavior.

REST 

Currently, REST, or Representational State Transfer, is the most popular of these web service architectures. Access to web service resources can be wide open, allowing unlimited, anonymous access to the resource, or it can be restricted to varying degrees using different security schemes.

ORDS 

Oracle Rest Data Services, or ORDS, provides the ability to easily create web services designed to expose database resources. ORDS allows for the protection of these services using several methods, including OAuth2. This works fine to allow access to the data for only those users that have been registered, but what if you also need to restrict access further, allowing users to see only their data?

Imagine that you are a wholesaler that wants to provide your customers with a simple web service that gives them a listing of the items included in their last order. Because you do not want Store A to know what Store B is ordering and vice versa, you can restrict the rows returned based on who is calling the service.

To determine if this was possible I used as references the following blogs by Tim Hall:

Oracle REST Data Services (ORDS): Authentication (https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication)

Oracle REST Data Services (ORDS): HTTP Headers (OWA_UTIL) and ORDS-Specific Bind Variables (https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-http-headers-and-ords-specific-bind-variables)

 

Following Tim’s example, I first logged in as SYS to create a user.

CREATE USER OAUTH_DEMO IDENTIFIED BY "oracle_4U"  
DEFAULT TABLESPACE "USERS" QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO OAUTH_DEMO;

 

Then, connecting as the OAUTH_DEMO user, create and populate the data table. You will notice that the table has a username column. This column will be used to identify the row as belonging to a specific user.

 

create table inventory (
    inventory_id  number
  , username      varchar2(255)  not null
  , item          varchar2(2000) not null
  , quantity      number default 0 not null
  , constraint pk_inventory primary key (inventory_id)
);

insert into inventory (inventory_id, username, item, quantity) values (1, 'Store A', 'Item #0001', 10); 
insert into inventory (inventory_id, username, item, quantity) values (2, 'Store B', 'Item #0002', 20); 
insert into inventory (inventory_id, username, item, quantity) values (3, 'Store A', 'Item #0003', 30); 
insert into inventory (inventory_id, username, item, quantity) values (4, 'Store B', 'Item #0004', 40);
insert into inventory (inventory_id, username, item, quantity) values (5, 'Store A', 'Item #0005', 50); 
insert into inventory (inventory_id, username, item, quantity) values (6, 'Store B', 'Item #0006', 60);
insert into inventory (inventory_id, username, item, quantity) values (7, 'Store A', 'Item #0007', 70); 
insert into inventory (inventory_id, username, item, quantity) values (8, 'Store B', 'Item #0008', 80);
insert into inventory (inventory_id, username, item, quantity) values (9, 'Store A', 'Item #0009', 90); 
insert into inventory (inventory_id, username, item, quantity) values (10, 'Store B', 'Item #0010', 100); 
insert into inventory (inventory_id, username, item, quantity) values (11, 'Store A', 'Item #0011', 101);
insert into inventory (inventory_id, username, item, quantity) values (12, 'Store B', 'Item #0012', 102); 
insert into inventory (inventory_id, username, item, quantity) values (13, 'Store A', 'Item #0013', 103);
insert into inventory (inventory_id, username, item, quantity) values (14, 'Store B', 'Item #0014', 104);
insert into inventory (inventory_id, username, item, quantity) values (15, 'Store A', 'Item #0015', 105);
insert into inventory (inventory_id, username, item, quantity) values (16, 'Store B', 'Item #0016', 106); 
insert into inventory (inventory_id, username, item, quantity) values (17, 'Store A', 'Item #0017', 107);
insert into inventory (inventory_id, username, item, quantity) values (18, 'Store B', 'Item #0018', 108);
insert into inventory (inventory_id, username, item, quantity) values (19, 'Store A', 'Item #0019', 109);
insert into inventory (inventory_id, username, item, quantity) values (20, 'Store B', 'Item #0020', 200); 
commit;

 

The next step is to REST-enable the schema with an alias of “services”, create a module called “inventory”, a template called “items” and a GET handler for that template.

BEGIN

  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'OAUTH_DEMO',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'services',
      p_auto_rest_auth      => FALSE);
      
  ORDS.DEFINE_MODULE(
      p_module_name    => 'inventory',
      p_base_path      => '/inventory/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'inventory',
      p_pattern        => 'items',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'inventory',
      p_pattern        => 'items',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'begin
      apex_json.initialize_output (
         p_http_header => true );
      apex_json.open_object;
      apex_json.open_array(''items'');
      for l_row in (select item, quantity from inventory) loop
         apex_json.open_object;
         apex_json.write(''item'', l_row.item);
         apex_json.write(''quantity'', l_row.quantity);
         apex_json.close_object;
      end loop;
      apex_json.close_array;
      apex_json.close_object;

  end;'
        );

  COMMIT; 
END;

 

At this point, we can test the endpoint using cURL.

$ curl -i -k   http://localhost:8080/ords/services/inventory/items
HTTP/1.1 200 OK
Date: Fri, 20 Jul 2018 18:27:26 GMT
Content-Type: application/json
Cache-Control: no-store
Pragma: no-cache
Expires: Sun, 27 Jul 1997 13:00:00 GMT
ETag: "rpnojVeQnH7Gm5lQwc1xjUgCH+bV6Q133FaMhxw1LZlh4ZouSjhOAniB1aBFZY+ajJ/9fY6r/9aRN3pm4xGAlA=="
Transfer-Encoding: chunked

{"items":
[{"item":"Item #0001","quantity":10}
,{"item":"Item #0002","quantity":20}
[SNIP]
,{"item":"Item #0019","quantity":109}
,{"item":"Item #0020","quantity":200}]}

 

All of the items in the inventory table are returned.

We now go on to secure the REST endpoint. This is done by creating an ORDS role, then associating that role with a privilege, and then protecting the endpoint using that privilege. Again, as the OAUTH_DEMO user:

 

DECLARE
  l_arr OWA.vc_arr;
BEGIN
  -- create the role
  ORDS.create_role(
    p_role_name => 'inventory_role');
 
  -- create the privlege
  l_arr(1) := 'inventory_role';
  ORDS.define_privilege (
    p_privilege_name => 'inventory_priv',
    p_roles          => l_arr,
    p_label          => 'AccessToInventory',
    p_description    => 'Allow Access To Inventory data.'
  );

  -- protect the endpoint
  ORDS.create_privilege_mapping(
    p_privilege_name => 'inventory_priv',
    p_pattern => '/inventory/items'
  );     
   
  COMMIT;
END;

Test the endpoint again to see that it is now protected.

 

$ curl -i -k   http://localhost:8080/ords/services/inventory/items
HTTP/1.1 401 Unauthorized
Content-Type: text/html
Content-Length: 23867
[SNIP]

 

Now we can create the OAuth clients and associate them with the inventory_role. Again, as the OAUTH_DEMO user:

 

BEGIN
  OAUTH.create_client(
    p_name            => 'Store A',
    p_grant_type      => 'client_credentials',
    p_owner           => 'DataStore Inc',
    p_description     => 'Store B Representitive',
    p_support_email   => 'person@storeA.com',
    p_privilege_names => 'asset_priv'
  );
  OAUTH.grant_client_role(
    p_client_name => 'Store A',
    p_role_name   => 'inventory_role'
  );

  OAUTH.create_client(
    p_name            => 'Store B',
    p_grant_type      => 'client_credentials',
    p_owner           => 'DataStore Inc',
    p_description     => 'Store B Representitive',
    p_support_email   => 'person@storeB.com',
    p_privilege_names => 'asset_priv'
  );
  OAUTH.grant_client_role(
    p_client_name => 'Store B',
    p_role_name   => 'inventory_role'
  );

  COMMIT;
END;

 

With the OAuth user created, we can query the ORDS_METADATA.USER_ORDS_CLIENTS view to retrieve the client_id and the client_secret for the users. Again, as the OAUTH_DEMO user:

select name, client_id, client_secret from ords_metadata.user_ords_clients;
Store A	wYcK02zAlegUXZPDWY18Xg..	HtwzyfiXKSEUR6635trsWQ..
Store B	iZPyXKGJuqgfA0oSH0nHig..	JYzjgwMHQ77HtPnK64padw..

 

The client_id and client_secret are then used to retrieve an access token from ORDS.

For Store A:

 

$ curl -i -k --user wYcK02zAlegUXZPDWY18Xg..:HtwzyfiXKSEUR6635trsWQ.. --data "grant_type=client_credentials" http://localhost:8080/ords/services/oauth/token
HTTP/1.1 200 OK
Date: Fri, 20 Jul 2018 19:02:55 GMT
Content-Type: application/json
X-Frame-Options: SAMEORIGIN
Transfer-Encoding: chunked

{"access_token":"hzBBv_o-qnkmYjGOIE1z7A..","token_type":"bearer",
"expires_in":3600000}

 

We now call the web service using the provided bearer token to make sure that the endpoint is available using the token.

$ curl -i -k  -H "Authorization: Bearer hzBBv_o-qnkmYjGOIE1z7A.."  http://localhost:8080/ords/services/inventory/items
HTTP/1.1 200 OK
Date: Fri, 20 Jul 2018 20:13:40 GMT
Content-Type: application/json
Cache-Control: no-store
Pragma: no-cache
Expires: Sun, 27 Jul 1997 13:00:00 GMT
ETag: "rpnojVeQnH7Gm5lQwc1xjUgCH+bV6Q133FaMhxw1LZlh4ZouSjhOAniB1aBFZY+ajJ/9fY6r/9aRN3pm4xGAlA=="
Transfer-Encoding: chunked

{"items":
[{"item":"Item #0001","quantity":10}
,{"item":"Item #0002","quantity":20}
[SNIP]
,{"item":"Item #0019","quantity":109}
,{"item":"Item #0020","quantity":200}]}

 

All of the items in the inventory table are returned because we have not yet changed the GET handler. In order to restrict the results returned, we need to know the identity of the remote user. One of the ORDS-specific bind variables available is “:current_user”. This variable holds the client_id of the authenticated user. Using this value, we can get the username by joining with the ORDS_METADATA.USER_ORDS_CLIENTS view.

To filter the results by the authenticated user, modify the query in the GET handler to read:

select item, quantity 
from inventory i
join ords_metadata.user_ords_clients uoc on (uoc.name = i.username)
where uoc.client_id = :current_user

 

Then call the service again.

$ curl -i -k  -H "Authorization: Bearer hzBBv_o-qnkmYjGOIE1z7A.."  http://localhost:8080/ords/services/inventory/items
HTTP/1.1 200 OK
Date: Fri, 20 Jul 2018 20:20:18 GMT
Content-Type: application/json
Cache-Control: no-store
Pragma: no-cache
Expires: Sun, 27 Jul 1997 13:00:00 GMT
ETag: "h3A3/vDHZq5nYWFXxYfxWMW0ypa3EHclzgR0V/M0XKYyyPIfs6SrI+pQAkX5uPKrHMD9Tuf6jgLESYQXwSHijg=="
Transfer-Encoding: chunked

{"items":
[{"item":"Item #0001","quantity":10}
,{"item":"Item #0003","quantity":30}
,{"item":"Item #0005","quantity":50}
,{"item":"Item #0007","quantity":70}
,{"item":"Item #0009","quantity":90}
,{"item":"Item #0011","quantity":101}
,{"item":"Item #0013","quantity":103}
,{"item":"Item #0015","quantity":105}
,{"item":"Item #0017","quantity":107}
,{"item":"Item #0019","quantity":109}]}

 

Only those items associated with Store A are returned.

Using the client_id and client_secret for Store B, a bearer token is acquired and when used to call the web service the following is the result.

$ curl -i -k  -H "Authorization: Bearer E0EaJ0LV3YIp8MiDAPwoLQ.."  http://localhost:8080/ords/services/inventory/items
HTTP/1.1 200 OK
Date: Fri, 20 Jul 2018 20:31:03 GMT
Content-Type: application/json
Cache-Control: no-store
Pragma: no-cache
Expires: Sun, 27 Jul 1997 13:00:00 GMT
ETag: "TdTtX3S1qMb9TF5YYxDPk+Dbl05TjSIVEXA2XCZRDr3ZCmU+egH/fYUC11qKVCpCCnW992A7A5RO2avnLTAkFw=="
Transfer-Encoding: chunked

{"items":
[{"item":"Item #0002","quantity":20}
,{"item":"Item #0004","quantity":40}
,{"item":"Item #0006","quantity":60}
,{"item":"Item #0008","quantity":80}
,{"item":"Item #0010","quantity":100}
,{"item":"Item #0012","quantity":102}
,{"item":"Item #0014","quantity":104}
,{"item":"Item #0016","quantity":106}
,{"item":"Item #0018","quantity":108}
,{"item":"Item #0020","quantity":200}]}

 

Only those items associated with Store B are returned.

This solution assumes that the host has control over who is allowed to sign up for the service and would provide them their bearer token in a secure manner.

 How are your Web Services Now?

Let me know how setting up user-specific web services using ORDS went for you.