Adding OAuth to ORDS

Adding security over the APIs across multiple layers was something that we considered when putting this project together. This perspective was reinforced at the #DigitalDefence hackathon in Nov 2020. Check out what happened (here).

Here we will focus on the different REST APIs exposing the data hosted by Autonomous Data Warehouse. We started off with HTTP Basic Authentication but quickly turned to using OAuth. Here we’ll explore more about the OAuth side and how to get that started.

What we’ll cover off here is something that we can automate. Note – that this has been automated with Terraform and Oracle Resource Manager so everything has been scripted to make this possible. Let’s’ walk through what it takes to adding OAuth to ORDS. If you want to get straight to the Terraform project and repository, check this out (here)

Back in 2019, we published this article about enabling REST APIs to expose data in Autonomous Database – Check out what we did there (here). Back then we went through a process of exposing these APIs with APEX which is still possible. It’s even made simpler with AutoREST which enables a full CRUD API on a table which a couple of clicks in APEX – Check out AutoREST (here).

Over the next few bits here, we’ll start off with a standard Autonomous Database provisioned. In the repository, there’s a couple things we do at the application-level and then for each table that we want to AutoREST-enable.

Application-Level Stuff

This is something that you need to once. You can find the PL/SQL (here) for the stuff we do at the application-level.

Enable ORDS

Firstly we need to enable ORDS for a specific schema and map it to an URL pattern. Note that the url mapping pattern will be used in the eventual API URL.

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'OCIARCADE',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'ociarcade',
    p_auto_rest_auth      => TRUE
  );
  COMMIT;
END;
/

Create OAuth User and Role

We also want to create two objects an OAuth user and an OAuth role and map the user to that role. Eventually that role is assigned permissions against different objects like tables and metadata.

BEGIN
  ORDS.CREATE_ROLE(p_role_name=>'ociarcade api');
  OAUTH.CREATE_CLIENT(
    p_name            => 'ociarcade_api',
    p_grant_type      => 'client_credentials',
    p_owner           => 'Name',
    p_description     => 'ociarcade oauth client user',
    p_support_email   => 'email@domain.com',
    p_privilege_names => '');
  OAUTH.GRANT_CLIENT_ROLE(
    p_client_name => 'ociarcade_api',
    p_role_name   => 'ociarcade api'
  );
  COMMIT;
END;
/

Shh … the secret … this object needs to be protected otherwise access to the secrets can be compromised. From here you get the id and secret that is required to get the bearer token.

select id, client_id, client_secret from user_ords_clients where name = 'ociarcade_api';

API-Level Stuff

This is something that you need to for each table / API that you want to expose. You can find the PL/SQL (here) for the score table API that we use.

REST-Enable The Object

The first thing to do here is to use the Auto-REST capability to enable the table to be exposed as a REST API. We also enabled authentication on the API.

BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE, -- Default  { TRUE | FALSE }
    p_schema       => 'OCIARCADE',
    p_object       => 'SCORE_TABLE',
    p_object_type  => 'TABLE', -- Default  { TABLE | VIEW }
    p_object_alias => 'score_table',
    p_auto_rest_auth => TRUE
  );

  COMMIT;
END;
/

Grant Privileges

The next thing we did is to define a privilege that enabled different roles to use this ORDS-enabled object. The patterns below are for both the API itself as well as a metadata catalog API.

DECLARE
  l_priv_roles owa.vc_arr;
  l_priv_patterns owa.vc_arr;
BEGIN
  l_priv_roles(1) := 'oracle.dbtools.role.autorest.OCIARCADE.SCORE_TABLE';
  l_priv_roles(2) := 'ociarcade api';
  l_priv_roles(3) := 'oracle.dbtools.autorest.any.schema';
  l_priv_roles(4) := 'SQL Developer';
  l_priv_patterns(1) := '/metadata-catalog/score_table/*';
  l_priv_patterns(2) := '/score_table/*';

  ords.define_privilege(
    p_privilege_name     => 'oracle.dbtools.autorest.privilege.OCIARCADE.SCORE_TABLE',
    p_roles              => l_priv_roles,
    p_patterns           => l_priv_patterns,
    p_label              => 'oracle.dbtools.autorest.privilege.OCIARCADE.SCORE_TABLE',
    p_description        => 'oracle.dbtools.autorest.privilege.OCIARCADE.SCORE_TABLE'
  );
  COMMIT;
END;
/

Test It …

From here, it’s pretty much the same for any other OAuth enabled REST API. It’s the usual two step process. The easiest thing is to show some code.

Here’s the fragment (using axios) to get the bearer token.

axios.post('https://'+ORDS_HOSTNAME+'/ords/ociarcade/oauth/token', 'grant_type=client_credentials', { auth:{username:API_USER,password:API_PASSWORD}})

Here’s the fragment (using axios) to use the bearer token.

axios.get('https://'+ORDS_HOSTNAME+'/ords/ociarcade/score_table/?q={"game_id":'+game_id+'}', { headers: { 'Authorization': 'Bearer '+access_token }})

Here’s the real code from the api-server where I grabbed these from (here). I did substitute ociarcade and the request query to try and simplify the code above.

If you want a full example of the stuff above – there is one in the documentation (here) that uses “employee” data and uses curl to execute the REST APIs.

And if you want to know more about Oracle REST Data Services itself – check it out (here) or try it out with Oracle Live Labs (here) with a full workshop to guide you through it. If you need an environment to try this out and start developing – head here and sign-up. https://www.oracle.com/au/cloud/free/.