Introduction

In this blog post we will discuss how it is possible to achieve identity propagation from an Oracle Fusion SaaS extension built with VIsual Builder Cloud Service (VBCS) using Oracle REST Data Services (ORDS). This blog article covers various patterns investigated and a pattern which has been implemented together with sample code.

The Common Pattern

A common pattern when extending Oracle Fusion SaaS is to “sync” data to an Oracle Database so that the data can be:

• Accessed faster than it can be from Oracle Fusion REST Services
• Complex SQL queries can be executed across the data (e.g. Unions, Joins etc)
• Data can be merged in a query with data not in Oracle Fusion
• Data can be queried by long running reporting tools without affecting Oracle Fusion SaaS performance
• Data can be queried where there is no suitable REST API from Oracle Fusion
  • In the past we did have instances where data was not querable via REST from Fusion, this is rarely the case now, however we often see usecases where we need to execute “many” REST calls to get the data we need. Unlike SQL, REST APIs does not easily allow “joining” of data from other REST endpoints, especially when they appear unrelated but are.

This is very common when the data is either not available in SaaS REST services, or multiple REST requests would be needed to get the data (ie master/detail/detail/detail datasets) , or when the data in SaaS needs to be joined with external data. The pattern here is to sync the data into a Oracle Database (ideally ATP Database) and then execute the query there.

My Data Is In ATP Database Can I Use ORDS?

What is ORDS? ORDS is a free Oracle REST Data Services extension to the Oracle database. Simply put it allows you to publish data from the Oracle Database using several increasingly powerful techniques.

• Using Auto-REST
  • Auto-REST exposes the database tables as REST APIS with a comprehensive query mechanism. Your database tables are exposed as your API (ie not API centric)
• SQL Queries
  • You can create API endpoint which call SQL queries , optionally passing query parameters to the query. This allows you to use all the power of Oracle Database SQL with your database tables. For advanced users you can query JSON Data, Spatial data and even integrate with Graph Queries
• PL/SQL
  • PLSQL can be written to query data from a single or multiple database table and then reformat the results into what ever JSON response you want for the client. Aggregations, filtering is all possible and you have complete control of the API

Each one of these techniques also allow the passing of parameters via query parameters or header variables. For security ORDS supports a Basic Authentication (not recommended) and OAuth2.0 Client Credential (Recommended)

Given the data is wholly stored in the database, this approach sounds like a perfect fit.

However ….

The main issue with accessing the data from ORDS is that identity propagation from Fusion to ORDS is not supported. ORDS users are ATP database users and these users do not match Oracle Fusion SaaS users and ORDS doesn’t use Oracle Identity Cloud Service (IDCS) for its users.

If your SaaS doesn’t need data security, and authentication is just fine then this wont be a problem , but this is rarely the case.

Solutions Investigated

Whilst investigating a solution for my project I explored a few options, here are two of them which I felt would work (spoiler alert: one is much better than the other!)

Option 1: Get The Database To Authenticate The OAuth Token From Oracle VBCS

In this architecture you expose the data through ORDS, only use PL/SQL procedures and get the database to validate the IDCS token.

The flow would be

• VBCS generates a OAuth token from IDCS
• Call an OAuth 2.0 Client Credential secured ORDS endpoint to get your data, we would also pass the OAuth IDCS generated JWT token as a header.
• The PL/SQL Procedure would then call a library PL/SQL function which would: 
  • Call the /userinfo service on IDCS using the IDCS OAuth token as the authentication token
  • If the token is valid then IDCS will return the username associated with the token otherwise else a 401 unauthorized error will be returned
  • The PL/SQL function could also call Oracle Fusion Applications and retrieve a list of Fusion Roles from the HCM module
  • The PLSQL Function can then return this username , and the roles, to the PL/SQL Procedure allowing it to implement the data security for the API

Advantages

• Only uses the Database and VBCS, no other middleware needed so simple to maintain

Disadvantages

• The ORDS endpoint can be accessed by any VBCS Client, ( via JavaScript) and thus any ORDS API can be called using the OAuth Client Credential without adding any extra data
  • The Client Credential itself is secured and not visible from the client, but the client is able to execute any call via the middletier
• The database is exposed to the internet , many customers prefer the database to be in a private subnet
• Solution will only work for PL/SQL ORDS modules 
  • Technically you can call the PL/SQL function from an SQL predicate to validate the token but it becomes tricky to get right and optimal
• Every ORDS call (PL/SQL ORDS API MUST check the validity of the token before progressing.
  • This means code quality is important and if this is accidentally missed out, we may find ourselves having a security issue
• If PL/SQL calls the IDCS endpoint “too often” it would receive a HTTP-429 (too many requests) error. This is normal behaviour where IDCS tries to protect itself from DDOS attacks.
• The PL/SQL function, which checks the authentication, would need to cache the OAuth token in a temporary table to make it efficient. The PL/SQL function would also need to deal with cleaning up old expired tokens. 

Based on the disadvantages above we decided not to implement this system.

Option 2 : Using API Gateway as a Custom Authentication Proxy

The solution we finally chose was to use Cloud Native technologies (i.e. API Gateway and Cloud Functions) and build a façade to the ORDs APIs

This approach implements the following flow:
(hint : you can use the numbers on the diagram to follow the flow)

• The ORDS REST API is protected by an OAuth 2.0 client credentials configuration
  • This ensures no one can access the endpoints without credentials.
• API Gateway is configured so that
  • REST URLs matching a pattern which matches the ORDS pattern are captured and processed
• VBCS calls API Gateway with a valid OAuth 2.0 authentication token.
• API Gateway calls a authorizer Cloud Function to check the token is valid
  • API  Gateway has an out of the box “check OAuth token is valid” feature (see documentation here) but we want to do more than just check the token is valid and hence we call a custom authorize cloud Function
  • The aurthorizer cloud function first checks that the OAuth 2.0 token is valid by calling the IDCS Introspection endpoint (http://<IDCS>/oauth2/v1/introspect) with the IDCS OAuth token which was passed in
    • This introspection endpoint validates the token is “active” and conveniently also returns the expiration date of the token
    • If the token is not valid then an authentication error is returned
    • Local vs Remote Validation
      • There are two ways we could check a token is valid, 1. Calling a IDCS Service (like the introspect service) or 2. local validation using a custom function and checking the OAuth token against a locally stored IDCS certificate. 
      • We have gone with the calling IDCS Service for each new call because
        • a) No need to put the IDCS Certificate in the middle tier (ie APIGW) – Good for security
        • b) Performance isn’t an issue because API GW caches the authenticated token, if it receives a new request within the TTL (Time To Live) then it is automatically authenticated without going to IDCS or Fusion, also leads to protecting against HTTP 429 (too many requests)
        • c) Less code= Less bugs = Less Security Vulns
        • d) Solution will secure against long lived tokens which have been revoked.
    • If you are interested in validating the token locally using a function then please see this sample https://github.com/oracle/oracle-functions-samples/tree/master/samples/oci-apigw-authorizer-idcs-java.
  • Part of the returned data from the IDCS introspect call is the principal (the username) which we will use later
  • Assuming the token is valid then
    • The authorizer cloud function queries Oracle Fusion and returns all the roles associated with that user  
    • The authorizer Cloud Function then, using an OCI vault stored OAuth Secret and client Id, obtains a valid token from ORDS using the ORDS token request URL (https://<ORDS>/ords/<module>/oauth/token)
  • The function then sets the “expiresAt” API Gateway auth context variable so that API Gateway knows that this IDCS OAuth token is valid for a specific amount of time (its set to the lower of expiry or ORDS token or IDCS token). By setting this parameter API Gateway won’t call the authentication function if the time is still valid and thus will cache the entire request (including the roles). This stops us from hitting IDCS and ORDS token endpoints too often and thus risking IDCS blocking us thinking its a DDOS attack
  • The cloud function then returns the ORDS token, Fusion Roles and the principal to API gateway for further processing
• If the payload returning to API Gateway :
  • A) contains “is_active = false” then API Gateway will return an authentication error to the client
  • B) contains “is_active = true”  then API Gateway uses its transformation functionality to replace the authorization header with the returned ORDS OAuth token and adds a new headers , one called “X-AUTH-USERNAME (with the username of the user ) and the other one is “X-AUTH-FUSION-ROLES” with the Fusion roles for the user
• API Gateway now calls the ORDS REST endpoint with the Authorization string set to the ORDS OAuth token, the roles and the username as header variables 
• ORDS receives the request, given the valid OAuth token it starts to process the PL/SQL function
  • The PLSQL Function can then query its data using the username/roles for data security.

Advantages

• Simple and Efficient
• Oracle Database ORDS is protected by OAuth 2.0 client credential, no access possible without going through API Gateway
• Oracle VBCS Client has no direct access to ORDS
• API Gateway can also implement API call throttling, CORS and many other “Gateway” features
• Everything deployed in OCI is cloud native and managed by Oracle (Oracle Database is not considered cloud native but Autonomous DB Is fully managed) 

Disadvantages

• Cloud Functions can take a couple of seconds to start up on the first invocation. Workarounds include using a Load Balancer to query the health every 15mins to keep them “warm” or using the new functionally called pre warmed functions

Conclusion

The above pattern using API gateway and cloud functions demonstrates how OCI Cloud Native Technologies can easily solve a question which many VBCS SaaS Extenders experience , which is how to call ORDS PL/SQL APIS and pass the identity, and roles, of the SaaS user. This means where the customer has data stored in Autonomous Database , they can choose to create APIS to that Data using ORDS and still benefit from custom Data Security.

This article is based on source code which will be published to Github here https://github.com/oracle-samples/sample.fusion-ords-identityprop