How to Generate JWT Token Using PL/SQL

 We have already seen how we  can have configuration in Fusion application to accept JWT token for API authentication in THIS post.

Now we will see how we can generate the JWT dynamically from a PL/SQL code which we can use in our OIC integrations to override and pass as authorization header in a webservice call (For eg using a Rest adapter). 

As the JWT token has 3 parts Header, Payload and Signature.

We will write a simple PL/SQL function to create JWT.

We can store the private key somewhere in OCI bucket or in OIC lookup or in any ATP table and get it at runtime to pass to this function.

Other values like x5t, typ, alg we can either hard code or pass dynamically .

For the payload we need to use like below:

                'iss'  - This will be token issuer with which we have created keys and registered as authentication

                           provider in Fusion
                'sub'  - This will be the user, which will be passed at runtime
                'prn'  -This will be the user, which will be passed at runtime,  use same as sub
                'iat'  - Unix EPOCH time for the issue time
                'exp'  -Unix EPOCH time for the expiration time

 Note:- if we are using OIC lookup to store private key, to be passed to this function then we need to split the key string into two columns as OIC lookup has limit of string length per column.

It will look like below:

 

 

 The PL/SQL function will look like below:

    FUNCTION cleanup_base64 (
        p_base IN RAW
    ) RETURN VARCHAR2 IS

        l_return  VARCHAR2(32767);
        l_crlf    VARCHAR2(2) := chr(13)
                              || chr(10);
        l_base    VARCHAR2(32767);
        l_charset VARCHAR2(8) := 'AL32UTF8';
    BEGIN
        l_base := utl_i18n.raw_to_char(p_base, l_charset);
        l_base := replace(replace(replace(replace(l_base, l_crlf), '='), '+', '-'), '/', '_');

        l_return := l_base;
        RETURN l_return;
    END;

    FUNCTION generate_jitsi_token (
        p_key IN VARCHAR2,
        p_iss IN VARCHAR2,
        p_x5t IN VARCHAR2,
        p_sub IN VARCHAR2
    ) RETURN VARCHAR2 IS

        l_token            VARCHAR2(30000);
        l_header           VARCHAR2(30000);
        l_payload          VARCHAR2(30000);
        l_signature        VARCHAR2(30000);
        l_secretkey        VARCHAR2(30000) := p_key;
        l_content          VARCHAR2(30000);
        l_pay_clean        VARCHAR2(30000);
        l_hdr_clean        VARCHAR2(30000);
        l_header_base64    RAW(30000);
        l_payload_base64   RAW(30000);
        l_signature_base64 RAW(30000);
        l_charset          VARCHAR2(8) := 'AL32UTF8';
        l_epoc_start       VARCHAR2(20);
        l_epoc_end         VARCHAR2(20);
    BEGIN
    -- Make Header in JSON Format
        SELECT
            JSON_OBJECT(
                'alg' VALUE 'RS256',
                'typ' VALUE 'JWT',
                'x5t' VALUE p_x5t
            )
        INTO l_header
        FROM
            dual;

    -- Get EPOC Start and End Time with 6 mins Gap
        SELECT
            TO_NUMBER((sysdate - TO_DATE('01-01-1970', 'dd-mm-yyyy')) * 24 * 60 * 60)           AS start_epoc,
            TO_NUMBER((sysdate + 1 / 240 - TO_DATE('01-01-1970', 'dd-mm-yyyy')) * 24 * 60 * 60) AS end_epoc
        INTO
            l_epoc_start,
            l_epoc_end
        FROM
            dual;

    --
    -- Make Payload in JSON Format
        SELECT
            JSON_OBJECT(
                'iss' VALUE p_iss,
                'sub' VALUE p_sub,
                'prn' VALUE p_sub,
                'iat' VALUE l_epoc_start,
                'exp' VALUE l_epoc_end
            )
        INTO l_payload
        FROM
            dual;

    --
        l_header_base64 := utl_encode.base64_encode(utl_i18n.string_to_raw(l_header, l_charset));
        l_payload_base64 := utl_encode.base64_encode(utl_i18n.string_to_raw(l_payload, l_charset));
        l_hdr_clean := cleanup_base64(l_header_base64);
        l_pay_clean := cleanup_base64(l_payload_base64);
        l_content := l_hdr_clean
                     || '.'
                     || l_pay_clean;
        l_signature_base64 := utl_encode.base64_encode(dbms_crypto.sign(utl_i18n.string_to_raw(l_content, l_charset), utl_i18n.string_to_raw
        (l_secretkey, l_charset), dbms_crypto.key_type_rsa, dbms_crypto.sign_sha256_rsa));

        l_signature := cleanup_base64(l_signature_base64);
        l_token := l_hdr_clean
                   || '.'
                   || l_pay_clean
                   || '.'
                   || l_signature;
        RETURN l_token;
    EXCEPTION
        WHEN OTHERS THEN
            l_token := NULL;
            RETURN l_token;
    END;

We can call this from OIC. In the next post we will see how we can call this function from OIC and dynamically generate JWT token for a user and pass it to rest adapter as authorization header.