Secure User Authentication with JWT in Oracle APEX

Secure User Authentication with JWT in Oracle APEX

This tutorial will walk you through the process of creating a JWT token using theapex_jwt package, as well as how to secure your REST services so that only legitimate token-associated requests are allowed access.

Why use JWT to secure your APIs?

JSON Web Tokens, commonly known as JWTs, have become a fundamental element in modern-day authentication and authorization processes. These small and self-contained tokens act as a secure method for transferring information between parties in a compact and verifiable format.

JWTs operate as digitally signed tokens and carry payload data encoded in JSON format. They have gained widespread adoption due to their versatility, offering a standardized method for securely transferring claims or information between entities.

In its compact form, JSON Web Tokens consist of three parts separated by dots (.), which are:

  • Header

  • Payload

  • Signature

Prerequisites

All you need to follow along with this example is an Oracle APEX account and the Postman client.

Setting Up

We will make a GET request to a REST API (given that the user or the application that is requesting the token is authenticated), which will generate and return a JWT token using the PL/SQL packageapex_jwt. The token will contain information about the user, such as username and user role; this information will be used later in another REST API when a user tries to get the employee's data. We will use Postman to send requests to the REST APIs, but you can use any other client or integrate the solution into your current application.

Task1: Create a user table

First, we will create a user table that will contain information about the users. For this demo, we will only create the table with two attributes username and user_role.

Go to your APEX workspace, navigate to the SQL workshop, then select the object browser and create the table with the attributes we mentioned above. Alternatively, you can run the following script in the SQL Commands:

create table app_users(
    username  varchar2(100)
  , user_role varchar2(100)
);
/
insert into app_users (username, user_role) values ('Mohamad', 'Admins');
/

Task2: Create a REST API that will generate the JWT token

After a successful login, the user (client) will use our RESTful API, which will return a token including the username and user role.

To create the REST API, navigate to SQL Workshop and select RESTful Services.

Create a new Module and give it the name 'Auth', then create a new template and name it 'getToken', navigate to the template, and create a get handler. In the get handler select 'PL/SQL' as a source type and add the following code:

DECLARE
    l_token           VARCHAR2 (4000);
    l_is_user_exists  NUMBER;
    l_username        VARCHAR2(200);
    l_user_role       VARCHAR2(200);
BEGIN
    --check if the user exists in our app_users where the username is 
    --equal to the name passed with the request.
    SELECT COUNT (*)
      INTO l_is_user_exists
      FROM app_users
     WHERE LOWER(username) = LOWER(:userName);
    --if the user does not exist in our table, return an error message
    --in a json format
    IF l_is_user_exists <= 0
    THEN
        APEX_JSON.open_object;
        APEX_JSON.write ('message', 'User doesnt exist');
        APEX_JSON.close_object;
        RETURN;
    END IF;
    --if the user exists, get the username and user role from the table.
    SELECT username, user_role
      INTO l_username, l_user_role
      FROM app_users
     WHERE LOWER(username) = LOWER(:userName);
    /***
    use the apex_jwt package to generate the jwt token
    @param p_iss the issuer of the token
    @param p_sub the "sub" (subject) claim identifies the principal that is the
           subject of the JWT
    @param p_aud the "aud" (audience) claim identifies the recipients 
           that the JWT is intended for
    @param p_iat_ts the "iat" (issued at) claim identifies the time at 
           which the JWT was issued
    @param p_exp_sec token expiration in seconds
    @param p_signature_key signature key used to encrypt the token
    ***/
    l_token :=
        apex_jwt.encode (
            p_iss       => 'ORDS',
            p_sub       => l_username,
            p_aud       => l_user_role,
            p_iat_ts    => SYSDATE,
            p_exp_sec   => 60 * 5,
            p_signature_key   =>
                sys.UTL_RAW.cast_to_raw (
                    'secretKey'));
    --return the token with success message in json format
    APEX_JSON.open_object;
    APEX_JSON.write ('data', l_token);
    APEX_JSON.write ('message', 'Success generating token');
    APEX_JSON.close_object;

    EXCEPTION
    WHEN OTHERS
    THEN
        APEX_JSON.open_object;
        APEX_JSON.write ('message', 'Error');
        APEX_JSON.close_object;
        :status := 401;
END;

In the handler, add an input parameter and name it 'userName'

Task3: Create a REST API to validate and retrieve employees data

After we have retrieved the token, we will use it to retrieve employee's data from another RESTful API. The client will send the token along with the request to the REST API, after a successful token validation, the API will return the employee's data in a JSON format.

Navigate to the sample Module in the RESTful API, which is created by default in APEX, and edit the get handler of the 'empinfo' template, change the source type to 'PL/SQL', and paste the following script:

DECLARE
    employees_cursor   SYS_REFCURSOR;
    l_token            APEX_JWT.T_TOKEN;
    l_user_object      APEX_JSON.T_VALUES;
    l_header_value     VARCHAR2(4000);
    l_bearer_token     VARCHAR2(4000);
    l_scope            VARCHAR2(200);
BEGIN

    --get the authorization header value from the request
    l_header_value := OWA_UTIL.get_cgi_env('Authorization');

    -- Check if the Authorization header exists and starts with 'Bearer '
    IF l_header_value IS NOT NULL AND INSTR(l_header_value, 'Bearer ') = 1 THEN
        -- Extract the token part after 'Bearer '
        l_bearer_token := SUBSTR(l_header_value, 8);
    ELSE
        --return error message in json format if the token is not founded
        APEX_JSON.open_object;
        APEX_JSON.write ('message', 'Error no token found');
        APEX_JSON.close_object;
        :status := 401;
        RETURN;
    END IF;
    --decode the token using apex_jwt, note that will should use the same
    --signature key
    l_token :=
        apex_jwt.DECODE (
            p_value   => l_bearer_token,
            p_signature_key   =>
                sys.UTL_RAW.cast_to_raw (
                    'secretKey'));
    /***
    validate the token using the apex_jwt package
    @param p_token the JWT
    @param p_iss if not null, verify that the "iss" claim equals p_iss
    @param p_aud if not null, verify that the single "aud" value equals 
    p_aud. If "aud" is an array, verify that the "azp" (Authorized Party) claim equals p_aud. This is an OpenID extension
    ***/
    apex_jwt.VALIDATE (p_token            => l_token,
                       p_iss              => 'ORDS',
                       p_aud              => 'Admins');

    --extract the payload from the token
    apex_json.parse (l_user_object, l_token.payload);

    --check if the subject and audince claims exists
    IF NOT apex_json.does_exist (p_path => 'sub', p_values => l_user_object)
    THEN
        APEX_JSON.open_object;
        APEX_JSON.write ('message', 'Error no user found');
        APEX_JSON.close_object;
        :status := 401;
        RETURN;
    ELSIF NOT apex_json.does_exist (p_path => 'aud', p_values => l_user_object)
    THEN
        APEX_JSON.open_object;
        APEX_JSON.write ('message', 'Error no scope found');
        APEX_JSON.close_object;
        :status := 401;
        RETURN;
    END IF;
    --extract the aud value from the json
    l_scope := apex_json.get_varchar2 (p_path => 'aud', p_values => l_user_object);

    --check if the user has the 'Admins' role to view the data and return
    --the data in json format
    IF l_scope = 'Admins' 
    THEN
        OPEN employees_cursor FOR
        SELECT ename, job
          FROM emp;

        APEX_JSON.open_object;
        APEX_JSON.write ('data', employees_cursor);
        APEX_JSON.write ('message', 'Success');
        APEX_JSON.close_object;
    ELSE
        --return error message if the user does not have the admin role
        APEX_JSON.open_object;
        APEX_JSON.write ('message', 'Not Authorised');
        APEX_JSON.close_object;
    END IF;

EXCEPTION
    WHEN OTHERS
    THEN
        APEX_JSON.open_object;
        APEX_JSON.write ('message', 'Error');
        APEX_JSON.close_object;
        :status := 401;
END;

Task4: Using the REST APIs in our client App

After we have created the APIs, we can use them in our client app to authorize a user and retrieve the data. This example assumes that the user has successfully logged in and is already authenticated. For the sake of this example, we will use Postman as a client.

First of all, we need to get the token from the 'Auth' API, for that, we will call the API and pass the username:

After you click send, you should get the response in JSON format, copy the token from the response because we will use it later.

Now, we will call the second API, which will validate the token and send the employee's data back. Create a new get request in Postman and in the Authorization tab select 'Bearer Token' as a type and paste the token then send the request.

You should get the employees' data back if you have the 'Admins' role and your token is still valid.

That's it. We hope you enjoyed it.

Conclusion

In conclusion, JSON Web Tokens (JWTs) serve as a crucial tool in modern API security, providing a strong framework for authentication and authorization. Their stateless nature, coupled with their ability to securely transmit information, makes them a preferred choice for many developers and organizations.

JWTs simplify the authentication process by encapsulating user data within a compact and self-contained token. This portability and versatility allow for seamless communication between different services and systems, promoting interoperability and enabling efficient development practices.

Finally, please keep in mind that the demo we have created is only to introduce the apex_jwt package and does not represent best practices... You might use the package in another scenario.

References