How to build Database REST APIs crossing multiple schemas

I have an application that has objects across multiple schemas and I require:

• one set of REST APIs that can manipulate my data in any one of those schemas
• a single set of credentials/OAuth Client(s) that can be used for all of the APIs

Can this be done with an Oracle Database?

Yes!

This post will demonstrate one method for implementing this ‘central schema’ approach, via VIEWs. Our API Schema will have multiple views defined, and via those views, we’ll be able to work with the data in our tables, across the entire database.

What is an ‘API Schema?’

This is the schema in the database where the REST APIs will all be defined. When said APIs are called, the underlying database work (SQL & PL/SQL calls) is all executed via the user associated with said schema.

But is this the best way?

‘Best’ is such a fun word. It’s generally agreed upon that a more holistic approach would involve PL/SQL APIs built ‘overtop’ said VIEWs or TABLEs. So the APIs would never do direct SELECT or INSERTs on a table, but instead simply call PL/SQL programs to do said work.

However, for simplicity sake, we’re going to build REST APIs directly on the objects.

For a great discussion on this methodology, commonly known as ‘SMARTDB’ or ‘THICKDB,’ I highly recommend this post from Philipp Salivsberg.

Our Example

I have two tables, (EMPS & UNTAPPD), in two different schemas (HR & JEFE). I have a third schema (CENTRAL_APIS), where I will be granting full CRUD privs directly on those two tables. I will then create two corresponding views, and then AutoREST enable those two different views.

Wait, what is AutoREST? It’s a feature where you can simply point to a TABLE, VIEW, or even a PL/SQL program, and we automatically publish REST APIs to work with said database objects (AutoREST Docs.)

Creating the API Schema

 
-- use GOOD passwords
CREATE USER central_apis IDENTIFIED BY oracle;
grant CONNECT, resource TO central_apis;
 
-- grant only what is needed, I need full CRUD, so I get full CRUD privs
grant INSERT, UPDATE, DELETE, SELECT ON hr.emps TO central_apis;
grant INSERT, UPDATE, DELETE, SELECT ON jefe.untappd TO central_apis;
 
-- ENABLE REST
BEGIN
    ORDS_ADMIN.ENABLE_SCHEMA(
        p_enabled             => TRUE,
        p_schema              => 'CENTRAL_APIS',
        p_url_mapping_type    => 'BASE_PATH',
        p_url_mapping_pattern => 'central_apis',
        p_auto_rest_auth      => FALSE
    );
    COMMIT;
END;
/

So the API schema (CENTRAL_APIS) is ready for the VIEWs. We can create those, via GUI or Code. Now, I don’t normally create tables called EMPLOYEES_TBL or views called EMPS_VW, but in this case I’m making an exception so it’s quite clear we’re doing everything via the VIEW.

Here’s the opposite take from our friends in SQL Server land where they advocate always putting ‘TABLE’ in your table names.

Anyways, here’s the VIEW code:

CREATE VIEW CENTRAL_APIS.EMPS_VW ( ID, NAME, SALARY, JOB, "{}EVERYTHING_ELSE" ) AS
SELECT *
  FROM hr.emps 
;
 
ALTER VIEW CENTRAL_APIS.EMPS_VW 
    ADD CONSTRAINT EMPS_VW_PK PRIMARY KEY ( ID ) DISABLE ;
 
 
CREATE VIEW CENTRAL_APIS.UNTAPPD_VW ( BEER_NAME, BREWERY_NAME, BEER_TYPE, BEER_ABV, BEER_IBU, COMMENTS, VENUE_NAME, VENUE_CITY, VENUE_STATE, VENUE_COUNTRY, VENUE_LAT, VENUE_LNG, RATING_SCORE, CREATED_AT, CHECKIN_URL, BEER_URL, BREWERY_URL, BREWERY_COUNTRY, BREWERY_CITY, BREWERY_STATE, FLAVOR_PROFILES, PURCHASE_VENUE, SERVING_TYPE, CHECKIN_ID, BID, BREWERY_ID, PHOTO_URL, GLOBAL_RATING_SCORE, GLOBAL_WEIGHTED_RATING_SCORE, TAGGED_FRIENDS, TOTAL_TOASTS, TOTAL_COMMENTS ) AS
SELECT *
  FROM jefe.UNTAPPD 
;
 
ALTER VIEW CENTRAL_APIS.UNTAPPD_VW 
    ADD CONSTRAINT UNTAPPD_VW_PK PRIMARY KEY ( CHECKIN_ID ) DISABLE ;

The PRIMARY KEY (disabled) constraints on the VIEW are what make it possible for our AutoREST VIEW APIs to do anything more than GET (SELECTs)!

We make developer tools around here, so if you prefer pointing and clicking:

Creating the REST APIs

We only need to ‘AutoREST’ enable the two VIEWs. You can do this with a right-click, or by running some code that looks like this.

BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED        =>  TRUE,
        P_SCHEMA         => 'CENTRAL_APIS',
        P_OBJECT         => 'EMPS_VW',
        P_OBJECT_TYPE    => 'VIEW',
        P_OBJECT_ALIAS   => 'emps_vw',
        P_AUTO_REST_AUTH => FALSE
    );
    COMMIT;
END;

Now let’s play with the APIs!

Our REST development screens include a built-in OpenAPI Documentation page. These are nice because not only do they document the APIs, they let you ‘Try them out,’ too!

Let’s do a PUT

Poor Garfield needs a raise. One dollar or euro isn’t nearly enough to get him a slice of lasagna with his paycheck.

And did it work?

What about about our actual table, in the HR schema?

Securing both sets of APIs with a SINGLE OAuth2 Client

Step 1: Secure the AutoREST Views
Step 2: Create an OAuth2 client having the privs from Step 1
Step 3: Try our REST APIs with our new Client ID and Secret.

Securing the APIs

We simply need to toggle this from FALSE to TRUE

p_auto_rest_auth=> FALSE — bad!
p_auto_rest_auth=> TRUE — good!

Once that’s done, you’ll see in your REST workshop that the status of the endpoints is ‘Green’, which means SECURE.

Creating the OAuth2 Client

We can continue using the GUI to create our OAuth2 client, or we can do this with code. All of the code I’ve shown today is our PL/SQL API for managing your REST APIs.

BEGIN
    OAUTH.CREATE_CLIENT(
        P_NAME            => 'THICKDB',
        P_GRANT_TYPE      => 'client_credentials',
        P_OWNER           => 'CENTRAL_APIS',
        P_DESCRIPTION     => 'Used to access our CENTRAL_APIS REST APIs',
        P_ORIGINS_ALLOWED => '',
        P_REDIRECT_URI    => NULL,
        P_SUPPORT_EMAIL   => '[email protected]',
        P_SUPPORT_URI     => 'https://www.thatjeffsmith.com',
        P_PRIVILEGE_NAMES => 'oracle.dbtools.autorest.privilege.CENTRAL_APIS.EMPS_VW,oracle.dbtools.autorest.privilege.CENTRAL_APIS.UNTAPPD_VW'
    );
 
      ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE( 
      p_client_name    => 'THICKDB',
      p_role_name      => 'oracle.dbtools.role.autorest.CENTRAL_APIS.EMPS_VW'); 
 
  ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE( 
      p_client_name    => 'THICKDB',
      p_role_name      => 'oracle.dbtools.role.autorest.CENTRAL_APIS.UNTAPPD_VW'); 
 
    COMMIT;
END;

Trying the REST APIs

In my REST Client, I can say I want to use OAuth2, Client Credentials workflow. I just need to provide the Client ID, Secret, and the endpoint to use for requesting an access token.

Here I have two requests, using the same Client ID to access data from two separate schemas, via the REST APIs published on my VIEWs.