05Sep
User Specific Web Services Using ORDS Insum
By: David Schleis On: September 5, 2018 In: ORDS, Web Services Comments: 2

Web services are an efficient means of providing access to web resources to a variety of clients. This access can be provided regardless of client by relying 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.

 

 

 

 

 

 

 

2 Comments:

    • Chandra Sharma
    • October 01, 2018
    • Reply

    Hi,

    Very nice article step by step.
    How I can control POST and UPDATE operations with role based authentication ?
    So if user having role of SELECT only it can not POST or UPDATE.
    Any specific user having POST only can not GET or PUT or DELETE?

    Is there any way to implement in ORDS.

    Chandra

      • David Schleis
      • October 15, 2018
      • Reply

      Hello Chandra

      You use the term “user” in your question. To be clear, this post is about using OAUTH client definitions and ORDS roles and privileges to determine access, not database users, roles and privileges.

      With that said, there may be other ways to accomplish what you are asking, but if you were to create two different interfaces, such as:

      ‘/inventory/get_items’ and ‘/inventory/post_items’

      You could then create specific ORDS roles and privileges for each of these interfaces and assign the desired role to the individual OAUTH client.

      I hope this helps.

Leave reply:

Your email address will not be published. Required fields are marked *