Posted in

How to Enable JWT User Assertion Authentication for ORDS REST APIs from OIC

Oracle REST Data Services (ORDS) supports Oauth2.0 authentication using JWT User Assertion grant type. This allows an external application, such as Oracle APEX, OIC, Postman or a java application to authenticate with ORDS and invoke secured REST APIs without passing the database username and password in every API request.

In this guide, we will configure JWT User Assertion authentication from scratch and test it using POST REST API call.

Pre-Requisite:

We need following before we start configuration:

Component

Example

Oracle Database

19c/21C/23ai/26ai

ORDS

23.3 or above

JWT Signing algorithm

RS256

JWT private key

Client-side private key

JWT public key

Registered with ORDS

Follow below steps to enable JWT user assetion for ORDS REST API:

Step1: Generate the RSA public/private key pair

a. Generate the self-Signed key pair:

Open command prompt and run below command to generate self-signed key pair

Command:

    keytool -genkey -keyalg RSA -alias demo_ordsjwtkey -keystore sampleKeystore.jks -storepass Oracle@123 -validity 365 -keysize 2048

    *** change the password as per your requirement.

    This will generate sampleKeystore.jks file as shown below:

    b. Export the public key for signing the JWT assertion

    Command:

    keytool -exportcert -alias <your_alias> -file <filename> -keystore <keystore_file> -storepass <password>
    ##example
    keytool -exportcert -alias demo_ordsjwtkey -file demo_ordsjwtkey.cer -keystore sampleKeystore.jks -storepass Oracle@123
    
      ## This should show a success message e.g. Certificate stored in file <assert.cer>

    c. Convert the keystore to P12 format:

    Command:

    keytool -importkeystore -srckeystore <filename> -srcstorepass <password> -srckeypass <password> -srcalias <your_alias> -destalias <your_alias> -destkeystore <destFileName> -deststoretype PKCS12 -deststorepass <password> -destkeypass <password> 
    
      ##example
    
      keytool -importkeystore -srckeystore sampleKeystore.jks -srcstorepass Oracle@123 -srckeypass Oracle@123 -srcalias demo_ordsjwtkey -destalias demo_ordsjwtkey -destkeystore demo_ordsjwtkey.p12 -deststoretype PKCS12 -deststorepass Oracle@123 -destkeypass Oracle@123 
    
      ## This should show a success message e.g. Importing keystore sampleKeystore.jks to assert.p12...
    

    d. Export the private key from the P12 key store:

    Command:

    openssl pkcs12 -in <destFileName> -nodes -nocerts -out <pem_file>
    
      ##example
    
      openssl pkcs12 -in demo_ordsjwtkey.p12 -nodes -nocerts -out private_key.pem
    
      ## This should show a success message: MAC verified OK

    *******Install open ssl before running this command.

    Step2: Create and REST-enable your database schema

    Connect to your database using an account with the required ORDS administration priviledges.

    For this example, we will use APEX_WS_DB with ADMIN user.

    a. Create New Database Schema and enable REST:

    Login to OCI.

    Navigate to Oracle AI Databases —>Autonomous AI Database.

    Select the database which is provisioned.

    Click on “View all database actions”.

    Click on Administration —>Database Users.

    Click on Create User.

    We are creating new database schema to test ORDS JWT User Assertion.

    Provide DB_DEVELOPER_ROLE to this new schema.

    Click on Create User.

    Once User/Schema is created, click on Enable REST to enable ORDS for this schema.

    Provide alias name and click on “REST Enable User”.

    This will enable ORDS for this newly created schema.

    Open SQL developer for this user. Click on below highlighted icon.

    Provide UserName and Password given during user creation.

    Click on “Sign in”.

    Click on SQL.

    b. Create Role and Privilege:

    Use below PLSQL code to create role and privilege:

    BEGIN
        ORDS.CREATE_ROLE(
            p_role_name => 'API_ACCESS_ROLE'
        );
        ORDS.CREATE_PRIVILEGE(
            p_name        => 'employees.write',
            p_role_name   => 'API_ACCESS_ROLE',
            p_label       => 'Employee API Access',
            p_description => 'Allows access to Employee REST APIs'
        );
        COMMIT;
    END;
    /

    c. Map the privilege to REST API URL

    Run below PLSQL code to map privilege to REST API URL

    BEGIN
        ORDS.CREATE_PRIVILEGE_MAPPING(
            p_privilege_name => 'employees.write',
            p_pattern        => '/demo/employees/*'
        );
        COMMIT;
    END;
    /

    Make note of ’employees.write’ as this will be needed while creating confidential application in next steps.

    d. Create a sample database table in schema:

    Run below SQL statement to create ewn database schema:

    CREATE TABLE employees_demo (
        employee_id   NUMBER GENERATED ALWAYS AS IDENTITY,
        employee_name VARCHAR2(100),
        department    VARCHAR2(100),
        salary        NUMBER,
        created_by    VARCHAR2(100),
        created_date  DATE DEFAULT SYSDATE,
        CONSTRAINT employees_demo_pk PRIMARY KEY (employee_id)
    );

    e. Create a POST REST API in ORDS

    Run below PLSQL code to define the REST module and endpoint:

    BEGIN
        ORDS.DEFINE_MODULE(
            p_module_name    => 'demo',
            p_base_path      => '/demo/',
            p_items_per_page => 25,
            p_status         => 'PUBLISHED'
        );
        ORDS.DEFINE_TEMPLATE(
            p_module_name => 'demo',
            p_pattern     => 'employees/'
        );
        ORDS.DEFINE_HANDLER(
            p_module_name => 'demo',
            p_pattern     => 'employees/',
            p_method      => 'POST',
            p_source_type => ORDS.SOURCE_TYPE_PLSQL,
            p_source      => q'~
    DECLARE
        l_json          JSON_OBJECT_T;
        l_employee_name VARCHAR2(100);
        l_department    VARCHAR2(100);
        l_salary        NUMBER;
        l_employee_id   NUMBER;
        l_created_by    VARCHAR2(255);
    BEGIN
        l_json := JSON_OBJECT_T.PARSE(:body_text);
        l_employee_name := l_json.get_string('employee_name');
        l_department    := l_json.get_string('department');
        l_salary        := l_json.get_number('salary');
        -- Capture authenticated user from JWT
        l_created_by := :current_user;
        INSERT INTO employees_demo (
            employee_name,
            department,
            salary,
            created_by
        )
        VALUES (
            l_employee_name,
            l_department,
            l_salary,
            l_created_by
        )
        RETURNING employee_id INTO l_employee_id;
        COMMIT;
        :status_code := 201;
        OWA_UTIL.MIME_HEADER('application/json', FALSE);
        OWA_UTIL.HTTP_HEADER_CLOSE;
        HTP.P(
            JSON_OBJECT(
                'status'      VALUE 'SUCCESS',
                'employee_id' VALUE l_employee_id,
                'created_by'  VALUE l_created_by,
                'message'     VALUE 'Employee created successfully'
            )
        );
    END;
    ~'
        );
        COMMIT;
    END;
    /

    Step3: Crate Resource Confidential Application in IDCS

    Confidential application needs to create in OCI domain.

    Login to OCI console.

    Navigate to Identity & Security —>Domains

    Copy domain URL which we need in further steps.

    Navigate to Integrated applications —> Add application

    Select Confidential Application

    Click on Launch Workflow.

    Provide any valid Name and description and Click on Submit.

    This will create new confidential application. Now we need to edit this application.

    Navigate to Oauth configuration —>Edit Oauth configuration.

    Select “Configure this application as a resource server now”.

    Enable “Allow token refresh”.

    *For Primary Audience, provide any value but we have to use same value while creating JWT profile for ORDS in database in further steps.

    Enable “Add Scope” and Click on Add.

    *Provide value of scope exactly matching with privilege name given in earlier step.

    Click on Add.

    Click on Submit.

    This will create new resource confidential application.

    Click on Activate.

    Step4: Crate Client Confidential Application in IDCS

    We need create another client confidential application with below details:

    Provide any valid name and description and Click on Submit.

    Edit this newly created client confidential application.

    Navigate to Oauth configuration —->Edit Oauth configuration.

    Select Allowed grant types as “JWT assertion” and “Refresh Token”.

    Select client type as “Trusted” and click on Import Certificate.

    For Alias, provide same alias name which is given during key generation in earlier step.

    For Certificate file, drop security certificate file which is generated in earlier step.

    Click on Import.

    Enable “Add resources” and click on “Add Scope”

    Select resource confidential application which we have created in previous step

    Click on Add.

    Click on Submit.

    This will create new client confidential application.

    Click on Activate.

    Step5: Upload Certificate as Trusted Partner Certificate.

    Navigate to same domain —>Security —>Trusted partner certificates —>Import certificate.

    Import same certificate and alias which is uploaded in earlier step.

    Click on Import.

    Step6: Create JWT Profile in Oracle Database.

    We need to create JWT profile in Oracle database.

    Login to database with ADMIN user.

    BEGIN
        ORDS_SECURITY_ADMIN.CREATE_JWT_PROFILE(
            p_schema   => 'ORDS_JWT_DEMO_SCHEMA',
            p_issuer   => ' https://identity.oraclecloud.com/' ,
            p_audience => 'ords/ords_jwt_demo_schema/api',
            p_jwk_url  => 'https://idcs-XXXXXXXXXXXXXX/admin/v1/SigningCert/jwk'
        );
        COMMIT;
    END;
    /
    

    Provide parameter details as shown below:

    p_schema

    The schema for which we have to enable JWT User assertion

    p_issuer

    Provide fixed value as https://identity.oraclecloud.com/

    p_audience

    This value must match with scope provided in resource confidential application.

    p_jwk_url

    <domain URL>/ admin/v1/SigningCert/jwk

    Step6: Test ORDS REST API using JWT User Assertion from Postman/Insomnia.

    Let us test ORDS REST API from Postman/Insomnia using JWT User assertion.

    a. Get ORDS REST API endpoint.

    ORDS Rest API is having below format:

    https://<host>:<port>/ords/<schema-alias>/<module-base-path>/<template-uri>

    Component

    Value

    Description

    Schema alias

    ords_jwt_demo_schema

    REST-enabled database schema

    Module

    demo

    API grouping

    Base Path

    demo/

    Module base path

    Template

    employees/

    URI pattern

    Handler

    POST

    HTTP operation

    https://xxxxxxxx-apexdbai.adb.ap-xxxxxx-1.oraclecloudapps.com/ords/ords_jwt_demo_schema/demo/employees

    Sample Payload:

    {
        "employee_name": "John Smith",
        "department": "IT",
        "salary": 75000
    }

    b. Generate Assertion:

    Run below python code to generate JWT assertion.

    import jwt
    import time
    import uuid
    # Read private key
    with open("private_key.pem", "r") as f:
        private_key = f.read()
    # Generate current timestamps
    now = int(time.time())
    # JWT payload
    payload = {
        "sub": "XXTEST_DEMO_JWT_USER_1",
        "jti": str(uuid.uuid4()),
        "iat": now,
        "exp": now + 300,
        "iss": "88e7baXXXXXXXXXXXXXXXXXXXXXXXXXX",
        "aud": "https://identity.oraclecloud.com/"
    }
    # JWT header
    headers = {
        "kid": "demo_ordsjwtkey",
        "typ": "JWT"
    }
    # Generate signed JWT assertion
    assertion = jwt.encode(
        payload,
        private_key,
        algorithm="RS256",
        headers=headers
    )
    # Save assertion
    with open("assertionJWT.txt", "w") as f:
        f.write(assertion)
    print("JWT Assertion generated successfully!")
    print(assertion)

    Also provide private_key.pem file to this python script which is generated in step1.

    c. Generate Access Token:

    Run below access Token URL:

    <Domain URL>/oauth2/v1/token

    Under Auth, select authorization type as Basic and provide details as shown below:

    This will generate access token.

    d. Call ORDS REST API endpoint with this access token.

    Under Auth, select authorization type as Bearer Token.

    With these details, once we click on send, it will execute REST API call successfully with 201 status.

    Step7: Test ORDS REST API using JWT User Assertion from OIC.

    Let us test the ORDS REST API with JWT user assertion from OIC, we have to perform below steps:

    a. Upload Private key to OIC:

    Login to OIC.

    Navigate to Settings —>Certificates

    Click on Upload.

    b. Create JWT header and JWT payload:

    Create new Json file with below content and save this file as demo_ordsjwtkeyheader.json

    Create another Json file with below content and save this as demo_ordsjwtkeypayload.json

    c. Create new REST Connection:

    Create new REST Connection for ORDS as shown below:

    Click on Test.

    Click on Save.

    d. Create new simple Integration:

    Let us create simple Integration to insert data into table using ORDS connection.

    Sample Payload:

    {
        "employee_name": "John Smith",
        "department": "IT",
        "salary": 75000
    }

    Sample Response:

    {
        "status": "SUCCESS",
        "employee_id": 1,
        "created_by": "XX_TEST1",
        "message": "Employee created successfully"
    }

    Save the Integration.

    Run the Integration now:

    Check result from database:

    Leave a Reply

    Your email address will not be published. Required fields are marked *