SQLDev Web: Securing RESTful Services in Oracle Autonomous Database with OAuth2

I’ve been talking about using SQL Developer Web to work with your RESTful Web Services. It’s a major new feature of SQL Developer Web 20.3. You really should glance through these posts before continuing on here:

• Details with Code and Examples
• Introduction and Overview

What I haven’t talked about yet are how this works in the Oracle Cloud (Autonomous Database) and the Security features.

So let’s fix that now.

How it works in Oracle Autonomous Database

Pretty much the same as it works in any ORDS and Oracle Database deployment. If you’re logged in to a REST Enabled Schema – you’ll see the REST card in your home screen.

Note that the Oracle Autonomous Shared services were updated in the last day or 5, and the Autonomous Dedicated environments will be upgraded soon. You’ll want to see version (20.3) listed in the About box.

The ORDS/REST/OAuth2 mechanisms aren’t new for Oracle Autonomous Database in the Oracle Cloud. You can see this awesome post from our developer advocate Todd Sharpe, where he covers how to secure your RESTful Web Services with OAuth2.

Todd is great because he always shares ALL of the code he uses for his examples and fancy hello world applications. That includes al of the PL/SQL ORDS API calls he made to secure his service and to create his ORDS OAuth2 Clients.

You can still do that…and probably should have that scripted/source controlled.

What IS new is the User Interface

Here’s what I want to show you:

• secure a RESTFul Web Service
• create a Role
• create an OAuth2 Client
• access the secured RESTful Web Service

Securing a RESTful Web Services

I have a RESTful Web Service, a simple GET that tallies up sales for a given ZipCode. Under the covers, it takes advantage of a newer database feature, SQL Macros (Tutorial on LiveSQL)

You can use whatever RESTful Web Service/REST Enabled Object you want, but when we go to create the ORDS privilege, substitute your module or resource template patterns for mine.

You can run this code in your REST Enabled schema using your SQL worksheet. The Sales History (SH) data is already available to all of your Oracle Autonomous instances.

CREATE OR REPLACE EDITIONABLE FUNCTION "F_AVG_SALES_MEN_WOMEN" (n in integer) return sys_refcursor as
 f_results sys_refcursor;
-- n is number of decimal points precision for amount sold and t_observed stats
-- takes advantage of the statisticall function ROLLUP command

begin
 open f_results for
 select SUBSTR(CUST_INCOME_LEVEL, 1, 22) INCOME_LEVEL,
       round(avg(DECODE(CUST_GENDER, 'M', AMOUNT_SOLD,
                  null)), n)                             SOLD_TO_MEN,
       round(avg(DECODE(CUST_GENDER, 'F', AMOUNT_SOLD,
                  null)), n)                            SOLD_TO_WOMEN,
       round(stats_t_test_indep(CUST_GENDER, AMOUNT_SOLD, 'STATISTIC',
                          'F'), n)                           T_OBSERVED,
       stats_t_test_indep(CUST_GENDER, AMOUNT_SOLD) TWO_SIDED_P_VALUE
  from SH.CUSTOMERS    C,
       SH.SALES        S
 where C.CUST_ID = S.CUST_ID
 group by rollup(CUST_INCOME_LEVEL)
 order by INCOME_LEVEL,
          SOLD_TO_MEN,
          SOLD_TO_WOMEN,
          T_OBSERVED;
 return f_results;
end f_avg_sales_men_women;
/

CREATE OR REPLACE EDITIONABLE FUNCTION "TOTAL_SALES" (zip_code varchar2) return varchar2 SQL_MACRO(TABLE) is
begin
  return q'{
   select cust.cust_postal_code as zip_code,
             sum(amount_sold) as revenue
   from sh.customers cust, sh.sales s
   where cust.cust_postal_code = total_sales.zip_code 
   and s.cust_id = cust.cust_id
   group by cust.cust_postal_code
   order by cust.cust_postal_code
  }';
end;
/

BEGIN
ORDS.DEFINE_MODULE(
      p_module_name    => 'sales_history',
      p_base_path      => '/sales_history/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'sales_history',
      p_pattern        => 'mf_breakdowns/:n',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'sales_history',
      p_pattern        => 'mf_breakdowns/:n',
      p_method         => 'GET',
      p_source_type    => 'json/query',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select F_AVG_SALES_MEN_WOMEN(:N)
  from DUAL');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'sales_history',
      p_pattern        => 'total_sales/:zipcode',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'sales_history',
      p_pattern        => 'total_sales/:zipcode',
      p_method         => 'GET',
      p_source_type    => 'json/item',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select * from total_sales(:zipcode)');

END;
/

As a quick example, let’s just run the SQL to show off the function for sales in a zipcode.

And if you’re curious to the execution plan…

Once you’ve executed the script I shared with you above, go into the REST development, click on the Modules card, and drill down into the ‘total_sales/:zipcode’ template. You can see I have a GET handler defined.

Copying and pasting the URL into a private/incognito browser, we can see the results of the GET request.

Speaking of ‘Protected’ – if a RESTful Web Service isn’t protected, it means that anyone that can reach the URL, is going to be able to use it, and get your data.

Be on the lookout for these in your REST console page –

So let’s get that from ‘Yellow’ to ‘Green’ – we want our REST APIs to require authenticated AND authorized requests.

Create an ORDS Role

On the Security menu of the REST screen, click that, and then the Create Role button.

ORDS Roles are NOT Database Roles. Database Roles contain database privileges that can be assigned to database users. ORDS Roles exist so that they can be assigned to HTTP(S) authenticated users and clients. The ORDS Roles will have ORDS Privileges.

Once the Role is created, we can move onto the ORDS Privilege.

Create an ORDS Privilege

Look up where is says ‘Security’ in the REST toolbar. Click on the dropdown, and select ‘Privileges.’

The names aren’t important so long as you use a name that you’ll recognize and understand later. Use comments, they’ll make your fellow developers happy.

The Role we previously created, we’re going to tie that Role to our new privilege. And finally, we’re going to associate this Privilege to the entire sales_history module.

Now that the REST API is protected by the Privilege, if we try to access it again, we’ll get

What’s happened, so far

We’ve created a working RESTful Web Service. We’ve used it. We created an ORDS Role and Privilege, and said privilege is tied to the RESTful Web Service’s module. When we try to access the API now, we get a 401.

That’s because we’re not authenticated, or even if we WERE authenticated, we’re not AUTHORIZED. The only way ORDS will let us access these protected resources if if our authenticated user has the appropriate role – ‘SALES’.

Creating the OAuth2 Client

Back to the Security menu, click on OAuth Clients.

We’ll get the slider mechanism, and we can start to fill out the details. Don’t use my website and don’t use my email, or I will find you, and I will…not be happy.

The critical thing here is making sure the Role is assigned to our Client. If you forget this, we’ll still be denied access when we try to access the RESTful Web Service.

Exploring our new OAuth2 Client

You should now see your client card. You can:

• get the client id
• get the secret
• get a bearer token
• get the cURL code to get your bearer token

If you’ve ever read blog posts on ORDS & OAuth2, you’ll see the last steps are running queries in the database to get the Client Id and Client Secret. You can still do that, but I think you’ll find this much more convenient!

Accessing the protected resource

I could do this all in cURL, but I hate cURL. My REST Client (Insomnia) provides a nice, integrated Security mechanism, and I’m going to use that.

Instead of using Basic Auth and providing our database credentials and relying on the ‘SQL Developer’ ORDS Role, we can now use our OAuth2 Client that has the SALES ORDS Role. Much better, and we’re not sharing database credentials with anyone that wants to access the APIs we’ve published.

Going forward my requests only need the Access token. When it expires, simply get a new one.