Note:

Access Protected Resources in ORDS using a JWT Token Issued by OCI IAM Domains

Introduction

Oracle Cloud Infrastructure Identity and Access Management (OCI IAM) uses identity domains to provide identity and access management features such as authentication, Single Sign-On (SSO), and identity lifecycle management for OCI as well as for Oracle and non-Oracle applications, whether SaaS, cloud hosted, or on-premises.

Oracle REST Data Services (ORDS) bridges HTTPS and your Oracle Database. A mid-tier Java application, ORDS provides a Database Management REST API, SQL Developer Web, a PL/SQL Gateway, Simple Oracle Document Access (SODA) for REST, and the ability to publish RESTful Web Services for interacting with the data and stored procedures in your Oracle Database.

ORDS release 23.3 introduces support for JSON Web Token (JWT). These bearer tokens enable the ORDS developers to delegate authentication and authorization to any OAuth2-compliant Identity Provider (IdP) to issue a JWT access token that ORDS can validate to provide access to ORDS protected resources.

In this tutorial, we will demonstrate how to access protected resources in ORDS using a JWT token issued by OCI IAM domains. For this demonstration, we will use an Oracle Autonomous Database in OCI, which comes with a preconfigured and fully managed ORDS application.

The following image shows the high-level representation of the solution architecture.

Architecture Diagram

Objectives

Prerequisites

Note: The OCI IAM domain and the JWT token issued by it satisfies all the above requirements.

Task 1: Set Up ORDS for a Database Schema, Define API Endpoints, and Configure Access Control

  1. Log in to the OCI Console, navigate to Oracle Database and click Autonomous Database.

  2. Click Provisioned Autonomous Database Instance, Database Actions and then click SQL.

  3. Run the following query to ensure you have ORDS version 23.3 or higher, which supports JWTs.

    SELECT * FROM ORDS_METADATA.ORDS_VERSION;

    ORDS Version

  4. Run the following query to create a user (ordstest) and assign the necessary privileges.

    CREATE USER ordstest IDENTIFIED BY "<Password>";
GRANT CONNECT, RESOURCE TO ordstest;
ALTER USER ordstest DEFAULT ROLE CONNECT, RESOURCE;

  5. Run the following query to enable ORDS for the schema to allow REST API functionality.

    BEGIN
 ORDS_ADMIN.ENABLE_SCHEMA(
     p_enabled => TRUE,
     p_schema => 'ordstest',
     p_url_mapping_type => 'BASE_PATH',
     p_url_mapping_pattern => 'ordstest',
     p_auto_rest_auth=> FALSE
 );
commit;
END;
/

  6. Run the following query to create a table named emp in the ordstest schema and insert sample data.

    CREATE TABLE ordstest.emp (
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

    Insert into ordstest.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
Insert into ordstest.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
Insert into ordstest.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
Insert into ordstest.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
Insert into ordstest.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);

  7. Run the following query to define an ORDS module (demo) that will contain the API endpoints.

    BEGIN
  ORDS_ADMIN.DEFINE_MODULE(
     p_schema => 'ordstest',
     p_module_name => 'demo',
     p_base_path => '/demo/',
     p_items_per_page=> 1000,
     p_status => 'PUBLISHED',
     p_comments=> ''
 );
COMMIT;
END;
/

  8. Run the following query to define a template emp that maps to a specific REST resource.

    BEGIN
 ORDS_ADMIN.DEFINE_TEMPLATE(
     p_schema => 'ordstest',
     p_module_name => 'demo',
     p_pattern => 'emp',
     p_priority => 0,
     p_etag_type => 'HASH',
     p_comments => ''
 );
COMMIT;
END;
/

  9. Run the following query to create a GET request handler for emp, which runs a simple SQL query returning data from the table emp.

    BEGIN
 ORDS_ADMIN.DEFINE_HANDLER(
     p_schema => 'ordstest',
     p_module_name => 'demo',
     p_pattern => 'emp',
     p_method => 'GET',
     p_source_type => ords.source_type_collection_feed,
     p_source => 'select * from emp',
     p_items_per_page => 25,
     p_comments => ''
 );
COMMIT;
END;
/

    The emp table can be viewed without any authentication by appending /ordstest/demo/emp to the ORDS public access URL of the Oracle Autonomous Databases, which is available under Tool Configuration. Postman is used in this tutorial.

    ORDS URL

    Access with out Auth

  10. Run the following query to define a privilege (privilegetest) to protect the demo module.

    DECLARE
L_PRIV_ROLES owa.vc_arr;
L_PRIV_PATTERNS owa.vc_arr;
L_PRIV_MODULES owa.vc_arr;
BEGIN
L_PRIV_MODULES( 1 ) := 'demo';
ORDS_ADMIN.DEFINE_PRIVILEGE(
  P_Schema=> 'ordstest',
  P_PRIVILEGE_NAME => 'privilegetest',
  P_ROLES => L_PRIV_ROLES,
  P_PATTERNS =>  L_PRIV_PATTERNS,
  P_MODULES => L_PRIV_MODULES,
  P_LABEL => 'privilegetest',
  P_DESCRIPTION => 'Demonstrate controlling access with priviliges',
  P_COMMENTS=> ''
  );
COMMIT;
END;
/

    Since the demo module is protected by the privilegetest privilege, it cannot be accessed without proper authorization. To enable access, you need to create a JWT profile for the ordstest schema. This allows ORDS to validate JWT bearer tokens and grant access to protected resources, such as the demo module.

Task 2: Create an ORDS JWT Profile

A JWT profile can be created within a REST-enabled schema using the OAUTH.CREATE_JWT_PROFILE procedure. However, only one JWT profile can be defined per schema. To update an existing JWT profile, it must first be deleted before creating a new one.

BEGIN
  OAUTH_ADMIN.DELETE_JWT_PROFILE(p_schema=>'ordstest');
  OAUTH_ADMIN.CREATE_JWT_PROFILE(
  p_schema => 'ordstest',
  p_issuer => 'https://idcs-123456789abcdefghijklmnopqrstuvw.identity.oraclecloud.com',
  p_audience => 'ords/ordstest/',
  p_jwk_url =>'https://idcs-123456789abcdefghijklmnopqrstuvw.identity.oraclecloud.com:443/admin/v1/SigningCert/jwk'
 );
COMMIT;
END;
/

This JWT profile specifies the issuer, audience, and the JWK URL.

The Issuer and JWK URI for an OCI IAM domain can be obtained by sending a GET request to the OCI IAM domain URL, appending /.well-known/openid-configuration, as shown in the following image.

IAM Domain Metadata

To verify the JWT profile configuration, log in as the ordstest user and run the following SQL query.

SELECT * FROM ORDS_METADATA.USER_ORDS_JWT_PROFILE;

This query retrieves the JWT profile details, ensuring that the issuer, audience, and JWK URL are correctly configured.

ORDS JWT Profile

Once the JWT profile is configured, end users can access ORDS protected resources by presenting JWT tokens issued by an OAuth 2.0-compliant IdP such as the OCI IAM domain specified in the JWT profile. When a JWT bearer token is successfully validated, then ORDS accepts the following:

Task 3: Enable ORDS Access to the OCI IAM Domain Signing Certificate without Authentication

  1. Go to the OCI Console, navigate to Identity & Security, Identity and click Domains.

  2. Click the name of the identity domain that you want to work in. You might need to change the compartment to find the domain that you want. Click Settings and Domain settings.

  3. In the Access signing certificate section, select Configure client access and click Save Changes. This lets ORDS to access the signing certification for the identity domain without authentication.

    Signing Certificate

Task 4: Configure Resource Server and Client, JWT Scopes and ORDS Privileges

You must configure the IdP to issue the JWT with a scope that matches the required ORDS privilege. If a resource in ORDS is protected by a privilege, that privilege name must be defined as a scope. This scope allows the application to request access on behalf of the user. The issued JWT must then include the scope as a claim. Since we will be using the OCI IAM OAuth2 services to enforce authorization, we need to set up some resource servers and clients.

Task 4.1: Create a Confidential Application of Type Resource Server with the Desired Audience and Scopes

  1. Go to the identity domain you are working in and click Integrated Applications.

  2. Select Add Application, Confidential Application and click Launch workflow.

  3. Enter a Name for the application (for example, ORDS-SERVER) and click Next.

  4. In the Resource server configuration section, select Configure this application as a resource server now.

  5. Configure the resource server with primary audience matching the JWT profile and scope matching the ORDS privilege.

    Resource Server Configuration

  6. Click Next and Finish.

  7. In the application overview page, select Activate and confirm that you want to activate the application. The confidential application is activated.

Task 4.2: Create a Confidential Application of Type Client which is Assigned the Desired Scopes

  1. Go to the identity domain you are working in and click Integrated Applications.

  2. Select Add Application, Confidential Application and click Launch workflow.

  3. Enter a Name for the application (for example, ORDS-CLIENT) and click Next.

  4. In the Client configuration section, select Configure this application as a client now.

  5. In the Authorization section, select Client credentials.

    Note: In this tutorial, we are using Client Credentials as Grant Types. You may select a different grant type based on your requirements, such as Authorization Code Flow for a user-facing client.

  6. In Client type, select Confidential.

  7. In the Token issuance policy section, select Specific as Authorized resources.

  8. Click Add Resources and under Resources, select Add Scope. Select the desired scopes for the resource server created in Task 4.1. For example, privilegetest.

    Client Configuration

  9. Click Next and Finish.

  10. In the Application Overview page, select Activate and confirm that you want to activate the application. The confidential application is activated.

  11. Copy the Client ID and Client secret of this client app. These credentials will be required for authentication.

Task 5: Send Requests to ORDS Using a JWT Bearer Token

Send a post request (using Postman) to the OCI IAM domain token endpoint with Grant Type as client_credentials and Scope as privilegetest as shown in the following image.

Token End point

Note: The audience and scope must be included in the scope field when requesting the bearer token from the OCI IAM domain.

Client Credentials

Validate the JWT token for scopes, issuer and audience.

JWT Token Details

Now the protected ORDS resource (demo module) in the ordstest schema can be accessed by providing a valid JWT bearer token with the get request as shown in the following image.

Access with JWT

ORDS validates the token by retrieving the public key from the JWK URL and verifying the token’s signature. If the signature is valid, ORDS checks whether the iss (issuer) and aud (audience) claims match those defined in the JWT profile. It also verifies that the scope claim corresponds to the privilege protecting the resource. If all conditions are met, the user is granted access to the protected resource.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.