TechnoMind

Linkedin Instagram Youtube
Let's Connect
Linkedin Instagram Youtube
Icon-phone-handset
Whatsapp

Oracle APEX – Custom Authentication

Complete Guide to Setting Up Custom Authentication

Learn how to set up Custom authentication.

To create a custom authentication scheme:

  1. On the Workspace home page, click the App Builder icon.
  2. Select an application.
  3. On the Application home page, click Shared Components.
  4. The Shared Components page appears.

  1. Under Security, select Authentication Schemes.

  2. On the Authentication Schemes page, click Create.

  3. Select Based on a pre-configured scheme from the gallery and click Next.

  4. Under Name:

    1. Name – Enter the name used to reference the authentication scheme by other application developers.

    2. Scheme Type – Select Custom.

  1. Fill in the appropriate fields.

    To learn more about a specific field, see field-level Help.

  2. Click Create Authentication Scheme.

Creating a custom authentication scheme alone is not sufficient for building a fully tailored authentication system. There are many additional, actionable, and important considerations involved.

In this article, we’ll dive deep into this complex subject and provide you with practical, step-by-step guidance to help you implement a complete custom authentication solution in your Oracle APEX application.

Here are the key topics we’ll cover to help you gain a thorough understanding of the subject.

  1. Create a custom authentication scheme – covered above.

  2. Create Custom Logic – a PL/SQL code

    • Password Security

    • Register a User

    • Authenticate User

      • Using Username and Password

      • Using Username and OTP (optional)

    • Password Reset

  3. Role Base Access Control – RBAC

    Writing a separate article on RBAC.

Lets start writing our custom logic for our Oracle APEX Application’s custom authentication.

Login to your APEX instance as developer.

Navigation:

Home > SQL Workshop > Object Browser

On the left panel, click on + sign and choose Package to create

  • Give a name – LAMCX_AUTH

    Enter the name of your choice for the Package to be created. The name must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.

  • Click on Create Package

    Now refresh page and check in left panel, you will find your newly created package.

Navigate to Package Specification

Create function – HASH_PASSWORD

      FUNCTION hash_password (
        p_user_name IN VARCHAR2,
        p_password  IN VARCHAR2
    ) RETURN VARCHAR2;

Create procedure – SIGNUP

          PROCEDURE signup (
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_user_name  IN VARCHAR2,
        p_email      IN VARCHAR2,
        p_app_id     NUMBER,
        p_company_id NUMBER,
        p_referral   varchar2,
        p_password   IN VARCHAR2 DEFAULT NULL
    );

Create procedure – AUTHENTICATE_USER

          FUNCTION authenticate_user (
        p_username  IN VARCHAR2,
        p_password  IN VARCHAR2 DEFAULT NULL,
        p_app_id    IN NUMBER DEFAULT NULL,
        p_by_otp    IN VARCHAR2 DEFAULT 'N',
        p_otp        IN NUMBER DEFAULT NULL
    ) RETURN BOOLEAN;

Create procedure – RESET_PASSWORD

         PROCEDURE reset_password (
        p_user_name    IN VARCHAR2,
        p_password     IN VARCHAR2,
        p_password_new IN VARCHAR2
    );

Create function – IS_ACTIVE

      
    FUNCTION is_active(
        p_username varchar2 default null
        ) return boolean;

Click on button – Save and Compile

You must check and confirm, if package is compiled successfully

Create database table – LAMCX_USERS, to store user data.

         CREATE TABLE "LAMCX_USERS" 
     (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
      "FIRST_NAME" VARCHAR2(2000 CHAR), 
      "LAST_NAME" VARCHAR2(2000 CHAR), 
      "USER_NAME" VARCHAR2(100), 
      "START_DATE" DATE, 
      "END_DATE" DATE, 
      "STATUS" VARCHAR2(30 CHAR), 
      "REMARKS" VARCHAR2(4000 CHAR), 
      "ATTRIBUTE1" VARCHAR2(150 CHAR), 
      "ATTRIBUTE2" VARCHAR2(150 CHAR), 
      "ATTRIBUTE3" VARCHAR2(150 CHAR), 
      "ATTRIBUTE4" VARCHAR2(150 CHAR), 
      "ATTRIBUTE5" VARCHAR2(150 CHAR), 
      "PASSWORD_HASH" VARCHAR2(2000 CHAR), 
      "PASSWORD_RAW" VARCHAR2(30 CHAR), 
      "MOBILE" VARCHAR2(30 CHAR), 
      "EMAIL" VARCHAR2(100 CHAR), 
      "SOURCE" VARCHAR2(30 CHAR), 
      "USER_TYPE" VARCHAR2(30 CHAR), 
      "PWD_UPDATE_STATUS" VARCHAR2(10 CHAR), 
      "PWD_UPDATE_DATE" DATE, 
      "PWD_UPDATED_BY" NUMBER, 
      "PWD_OTP" VARCHAR2(10 CHAR), 
      "PWD_OTP_REQ_START" DATE, 
      "PWD_OTP_REQ_END" DATE, 
      "EMP_IMG" BLOB, 
      "COMPANY_ID" NUMBER, 
      "CREATED" DATE, 
      "CREATED_BY" VARCHAR2(255 CHAR), 
      "UPDATED" DATE, 
      "UPDATED_BY" VARCHAR2(255 CHAR), 
      "ACCESS_ROLES" VARCHAR2(500), 
      "FULL_NAME" VARCHAR2(150), 
      "APPLICATION_ID" NUMBER, 
      "BUYER" VARCHAR2(1) DEFAULT 'N', 
      "AGENT" VARCHAR2(1) DEFAULT 'N', 
      "LINKEDIN" VARCHAR2(200), 
      "LAST_LOGGED_METHOD" VARCHAR2(30), 
      "LAST_LOGGED" DATE, 
      "DISPLAY_IMAGE" VARCHAR2(4000), 
      "EMAIL_VERIFIED" VARCHAR2(10) DEFAULT 'N', 
      "COUNTRY" VARCHAR2(100), 
      "STATE" VARCHAR2(100), 
      "CITY" VARCHAR2(100), 
      "ADDRESS_1" VARCHAR2(100), 
      "ADDRESS_2" VARCHAR2(100), 
      "POSTAL_CODE" VARCHAR2(15), 
      "ORG_TAX_ID" VARCHAR2(20), 
      "ORG_INCOME_ID" VARCHAR2(20), 
      "CONNECT_REQ" VARCHAR2(10) DEFAULT 'N', 
      "REQUESTED_BY" NUMBER, 
      "REQUEST_DATE" DATE, 
      "ISD" NUMBER, 
      "REQUESTED_BY_COMP" NUMBER, 
      "ACCOUNT_TYPE" VARCHAR2(20), 
      "RELEASE_REQUEST" VARCHAR2(10) DEFAULT 'N', 
      "RELEASE_REQUEST_DATE" DATE, 
      "RELEASE_REQUEST_BY" NUMBER, 
      "RELEASE_REQUEST_REM" VARCHAR2(1000), 
      "WF_ID" NUMBER, 
      "ACCEPT_PRIVACY_POLICY" VARCHAR2(10) DEFAULT 'N', 
      "ACCEPT_PRIVACY_POLICY_DATE" DATE, 
      "REFERRAL_USER" NUMBER, 
      "OTP_LOGIN" NUMBER, 
      "OTP_LOGIN_DATE" DATE, 
      "PARSE_COUNT" NUMBER DEFAULT 0, 
      "PARSE_COUNT_MAX" NUMBER DEFAULT 3, 
       CONSTRAINT "LAMCX_USERS_ID_PK" PRIMARY KEY ("ID")
    USING INDEX  ENABLE
     ) ;

Create an Index on the table

      
  CREATE OR REPLACE EDITIONABLE TRIGGER "LAMCX_USERS_BIU" BEFORE
      INSERT OR UPDATE ON LAMCX_USERS
      FOR EACH ROW
  BEGIN
      IF inserting THEN 
        -- Make sure to save the full name
        -- Make sure to save the username in upper case
          :new.user_name := upper(:new.user_name); 
        -- Hash the password so we are not saving clear text
  --        :new.password_hash := lams_auth.hash_password(upper(:new.user_name), :new.user_name||'@123');
          :new.password_hash := lamcx_auth.hash_password(upper(:new.user_name), nvl(:new.password_raw,:new.user_name||'@123'));

          :new.created := sysdate;
          :new.created_by := e(coalesce(sys_context('APEX$SESSION', 'APP_USER'), user));
      END IF;

      :new.first_name := initcap(substr(:new.first_name,1,100)); 
      :new.last_name := initcap(substr(:new.last_name,1,100)); 
      :new.full_name := trim(:new.first_name||' '||:new.last_name); 
      :new.updated := sysdate;
      :new.updated_by := e(coalesce(sys_context('APEX$SESSION', 'APP_USER'), user));
  END LAMCX_USERS_biu;

    
        CREATE TABLE "LAMCX_APEX_WORKSPACE_SESSIONS" 
     (    "WORKSPACE_ID" NUMBER NOT NULL ENABLE, 
      "WORKSPACE_NAME" VARCHAR2(255) NOT NULL ENABLE, 
      "WORKSPACE_DISPLAY_NAME" VARCHAR2(4000), 
      "APEX_SESSION_ID" NUMBER, 
      "USER_NAME" VARCHAR2(255), 
      "REMOTE_ADDR" VARCHAR2(255), 
      "SESSION_CREATED" DATE NOT NULL ENABLE, 
      "SESSION_IDLE_TIMEOUT_ON" DATE NOT NULL ENABLE, 
      "SESSION_LIFE_TIMEOUT_ON" DATE NOT NULL ENABLE, 
      "SESSION_MAX_IDLE_SEC" NUMBER(5,0) NOT NULL ENABLE, 
      "SESSION_TIME_ZONE" VARCHAR2(255), 
      "SESSION_LANG" VARCHAR2(5), 
      "SESSION_TERRITORY" VARCHAR2(255), 
      "SESSION_DEBUG_LEVEL" NUMBER(1,0), 
      "SESSION_TRACE_MODE" VARCHAR2(5), 
      "SESSION_AUTHENTICATION_ID" NUMBER, 
      "SESSION_TENANT_ID" VARCHAR2(32), 
      "SESSION_AUTHENTICATION_NAME" VARCHAR2(255), 
      "APPLICATION_ID" NUMBER, 
      "SESSION_END" DATE
     ) ;

  • As now we have created our package specification, ready with our database table, now we proceed to create our package body.

  • Inside package, select Body tab and start writing your actual logic for the process.

  • Function – HASH_PASSWORD

            FUNCTION hash_password (
          p_user_name IN VARCHAR2,
          p_password  IN VARCHAR2
      ) RETURN VARCHAR2 IS
          l_user     lamcx_users.user_name%TYPE := upper(p_user_name);
          l_password VARCHAR2(1000);
      BEGIN
      --
      -- The following encrypts the provided password and returns the encrypted string.  
      -- This is a one-way encryption using SHA512
      -- 
          SELECT
              standard_hash(p_user_name || p_password, 'SHA512')
          INTO l_password
          FROM
              dual;

          RETURN l_password;
      END hash_password;

Procedure – SIGNUP

          PROCEDURE signup (
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_user_name  IN VARCHAR2,
        p_email      IN VARCHAR2,
        p_app_id     NUMBER,
        p_company_id NUMBER,
        p_referral   varchar2,
        p_password   IN VARCHAR2 DEFAULT NULL
    ) IS

        l_status  VARCHAR2(1000) := NULL;
        l_status2 VARCHAR2(1000) := NULL;
        l_id      NUMBER;
        x         NUMBER := 0;
        cnt       NUMBER := 0;
--        e_oldpass EXCEPTION;
--        PRAGMA exception_init ( e_oldpass, -20001 );
    BEGIN
        BEGIN
            SELECT
                COUNT(1)
            INTO x
            FROM
                lamcx_users
            WHERE
                    user_name = p_user_name
                AND (company_id is null or company_id = p_company_id);

            IF x > 0 THEN
                cnt := cnt + 1;
                l_status := cnt || '. Invalid User Name !';
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;

        x := 0;
        BEGIN
            SELECT
                COUNT(1)
            INTO x
            FROM
                lamcx_users
            WHERE
                    d(email) = p_email
                AND company_id = nvl(p_company_id,company_id)
                and rownum=1;

            IF x > 0 THEN
                cnt := cnt + 1;
                l_status2 := cnt || '. Email Exists, Provide a unique email !';
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;

        IF cnt = 0 THEN
            INSERT INTO lamcx_users (
                first_name,
                last_name,
                user_name,
                email,
                source,
                status,
                start_date,
                application_id,
                company_id,
                password_raw,REFERRAL_USER
            ) VALUES (
                e(p_first_name),
                e(p_last_name),
                p_user_name,
                e(p_email),
                'SIGNUP',
                'Y',
                sysdate,
                p_app_id,
                p_company_id,
                nvl(p_password, p_user_name || '@xxxxx'),LAMCX_UTL.ENCODE(p_referral,'DECODE')
            ) RETURNING id INTO l_id;

        ELSE
            raise_application_error(-20001, l_status
                                            || chr(13)
                                            || chr(10)
                                            || l_status2);
        END IF;

    EXCEPTION
        WHEN OTHERS THEN
            RAISE;
    END signup;

Procedure – AUTHENTICATE_USER

          FUNCTION authenticate_user (
        p_username  IN VARCHAR2,
        p_password  IN VARCHAR2 DEFAULT NULL,
        p_app_id    IN NUMBER DEFAULT NULL,
        p_by_otp    IN VARCHAR2 DEFAULT 'N',
        p_otp        IN NUMBER DEFAULT NULL
    ) RETURN BOOLEAN IS

        l_user_name       lamcx_users.user_name%TYPE := upper(p_username);
        l_password        lamcx_users.password_hash%TYPE;
        l_hashed_password VARCHAR2(1000);
        l_roles           VARCHAR2(1000);
        l_name           VARCHAR2(1000);
        l_curr            VARCHAR2(10);
        l_count           NUMBER;
        l_otp             NUMBER;
        l_otp_date        DATE;
        l_id              lamcx_users.id%TYPE;
        l_comp_id         lamcx_users.company_id%TYPE;
    BEGIN 
-- Codes I normally use for AUTHENTICATION_RESULTS
-- These get logged to the APEX Login Access Log.
--  
--    0    Normal, successful authentication 
--    1    Unknown User Name 
--    2    Account Locked 
--    3    Account Expired 
--    4    Incorrect Password 
--    5    Password First Use 
--    6    Maximum Login Attempts Exceeded 
--    7    Unknown Internal Error 
-- 
-- First, check to see if the user exists 
        BEGIN
            SELECT
                id,
                company_id, trim(d(FIRST_NAME)||' '||d(last_name)), OTP_LOGIN,OTP_LOGIN_DATE
            INTO
                l_id,
                l_comp_id,l_name,l_otp,l_otp_date
            FROM
                lamcx_users
            WHERE
                    upper(user_name) = l_user_name
                -- AND application_id = NVL(p_app_id,v('APP_ID'))
                AND status='Y'
                AND ROWNUM = 1;

        EXCEPTION
            WHEN OTHERS THEN
                l_id := 0;
        END;

        IF l_id > 0 THEN

        BEGIN
            SELECT
                currency_code
            INTO l_curr
            FROM
                lamcx_company_profiles
            WHERE
                company_id = l_comp_id;

        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
        -- update lamcx_users set ADDRESS_2=l_otp||'.'||p_otp||'-'||l_otp_date where id=l_id;

            IF p_by_otp='Y' THEN
                IF l_otp_date < sysdate THEN
                  -- OTP expired.  
                    apex_util.set_authentication_result(3);
                    RETURN FALSE;
                ELSIF l_otp = p_otp THEN
                  -- Good result.  
                    apex_util.set_authentication_result(0);

                    apex_util.set_session_state(p_name => 'USER_ID', p_value => l_id);
                    apex_util.set_session_state(p_name => 'USER_ROLE', p_value => l_roles);
                    apex_util.set_session_state(p_name => 'P_NAME', p_value => l_name);
                    apex_util.set_session_state(p_name => 'COMPANY_ID', p_value => l_comp_id);
                    apex_util.set_session_state(p_name => 'CURRENCY_CODE', p_value => l_curr);
                    apex_util.set_session_state(p_name => 'SYSTEM_DATE', p_value => to_char(sysdate, 'DD-MON-RRRR'));
                    apex_util.set_session_state(p_name => 'APP_YEAR', p_value => to_char(sysdate, 'RRRR'));

                    BEGIN
                        INSERT INTO LAMCX_APEX_WORKSPACE_SESSIONS  (WORKSPACE_ID,WORKSPACE_NAME,APEX_SESSION_ID,
                        USER_NAME,REMOTE_ADDR,SESSION_CREATED,SESSION_IDLE_TIMEOUT_ON,SESSION_LIFE_TIMEOUT_ON,
                        SESSION_MAX_IDLE_SEC,SESSION_TIME_ZONE,SESSION_AUTHENTICATION_ID,SESSION_AUTHENTICATION_NAME,APPLICATION_ID)
                        SELECT WORKSPACE_ID,WORKSPACE_NAME,APEX_SESSION_ID,
                        l_user_name,REMOTE_ADDR,SESSION_CREATED,SESSION_IDLE_TIMEOUT_ON,SESSION_LIFE_TIMEOUT_ON,
                        SESSION_MAX_IDLE_SEC,SESSION_TIME_ZONE,SESSION_AUTHENTICATION_ID,NVL(SESSION_AUTHENTICATION_NAME,'EMAIL'),v('APP_ID')
                        FROM APEX_WORKSPACE_SESSIONS 
                        WHERE APEX_SESSION_ID=APEX_CUSTOM_AUTH.GET_SESSION_ID; 
                    EXCEPTION
                    WHEN OTHERS THEN NULL;
                    END;

                    RETURN TRUE;
                ELSE
                  -- Good Invalid OTP.  
                    apex_util.set_authentication_result(4);
                    RETURN FALSE;


                END IF;

            ELSE-- if login by password

              -- Hash the password provided 
                l_hashed_password := hash_password(l_user_name, p_password); 

              -- Get the stored password 
                SELECT
                    password_hash
                INTO l_password
                FROM
                    lamcx_users
                WHERE
    --                user_name = l_user_name;
                id=l_id;

              -- Compare the two, and if there is a match, return TRUE 
                IF l_hashed_password = l_password THEN 
                  -- Good result.  
                    apex_util.set_authentication_result(0);

                    apex_util.set_session_state(p_name => 'USER_ID', p_value => l_id);
                    apex_util.set_session_state(p_name => 'USER_ROLE', p_value => l_roles);
                    apex_util.set_session_state(p_name => 'P_NAME', p_value => l_name);
                    apex_util.set_session_state(p_name => 'COMPANY_ID', p_value => l_comp_id);
                    apex_util.set_session_state(p_name => 'CURRENCY_CODE', p_value => l_curr);
                    apex_util.set_session_state(p_name => 'SYSTEM_DATE', p_value => to_char(sysdate, 'DD-MON-RRRR'));
                    apex_util.set_session_state(p_name => 'APP_YEAR', p_value => to_char(sysdate, 'RRRR'));

                    BEGIN
                        INSERT INTO LAMCX_APEX_WORKSPACE_SESSIONS  (WORKSPACE_ID,WORKSPACE_NAME,APEX_SESSION_ID,
                        USER_NAME,REMOTE_ADDR,SESSION_CREATED,SESSION_IDLE_TIMEOUT_ON,SESSION_LIFE_TIMEOUT_ON,
                        SESSION_MAX_IDLE_SEC,SESSION_TIME_ZONE,SESSION_AUTHENTICATION_ID,SESSION_AUTHENTICATION_NAME,APPLICATION_ID)
                        SELECT WORKSPACE_ID,WORKSPACE_NAME,APEX_SESSION_ID,
                        l_user_name,REMOTE_ADDR,SESSION_CREATED,SESSION_IDLE_TIMEOUT_ON,SESSION_LIFE_TIMEOUT_ON,
                        SESSION_MAX_IDLE_SEC,SESSION_TIME_ZONE,SESSION_AUTHENTICATION_ID,NVL(SESSION_AUTHENTICATION_NAME,'EMAIL'),v('APP_ID')
                        FROM APEX_WORKSPACE_SESSIONS 
                        WHERE APEX_SESSION_ID=APEX_CUSTOM_AUTH.GET_SESSION_ID; 
                    EXCEPTION
                    WHEN OTHERS THEN NULL;
                    END;

                    RETURN TRUE;
                ELSE  
                  -- Incorrect Password
    --                raise_application_error(-20004, 'Error while LOGIN!' || p_username||', '||p_password);
                    apex_util.set_authentication_result(4);
                    RETURN FALSE;
                END IF;
            END IF;--END p_by_otp

        ELSE 
          -- The username does not exist 
            apex_util.set_authentication_result(1);
            RETURN FALSE;
        END IF; 
    -- 
        RETURN TRUE;
    EXCEPTION
        WHEN OTHERS THEN  
            raise_application_error(-20003, 'Error while LOGIN!' || sqlerrm);
        -- We don't know what happened so log an unknown internal error 
            apex_util.set_authentication_result(7);
            apex_util.set_custom_auth_status(sqlerrm);
            RETURN FALSE;
    END authenticate_user;

Procedure – RESET_PASSWORD

         PROCEDURE reset_password (
        p_user_name    IN VARCHAR2,
        p_password     IN VARCHAR2,
        p_password_new IN VARCHAR2
    ) IS

        l_user_name       lamcx_users.user_name%TYPE := upper(p_user_name);
        l_password        VARCHAR2(255);
        l_hashed_password VARCHAR2(1000);
        l_count           NUMBER;
        l_id              lamcx_users.id%TYPE;
        l_comp_id         lamcx_users.company_id%TYPE;
    BEGIN

-- First, check to see if the user exists 
        BEGIN
            SELECT
                id,
                company_id
            INTO
                l_id,
                l_comp_id
            FROM
                lamcx_users
            WHERE
                    user_name = l_user_name
                AND password_hash = hash_password(l_user_name, p_password)
                -- AND application_id = v('APP_ID')
                AND ROWNUM = 1;

        EXCEPTION
            WHEN OTHERS THEN
                l_id := 0;
        END;

        IF l_id > 0 THEN

          -- Hash the password provided 
            l_hashed_password := hash_password(l_user_name, p_password_new);

          -- Set the hashed password 
            UPDATE lamcx_users
            SET
                password_hash = l_hashed_password,
                password_raw = p_password_new
            WHERE
                user_name = l_user_name;

        ELSE
            raise_application_error(-20002, 'Invalid user/password - ' || p_user_name);
            RETURN;
        END IF;

    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error(-20003, 'Error while updating password!' || sqlerrm);
        -- We don't know what happened so log an unknown internal error 
    END reset_password;
Function – IS_ACTIVE 
          FUNCTION is_active(p_username varchar2 default null) 
    return boolean
    is
    x number := 0;
    begin

        select COUNT(1) INTO X from APEX_WORKSPACE_SESSIONS 
        where 1=1
        and UPPER(user_name)=nvl(p_username,v('APP_USER'))
        and sysdate between SESSION_CREATED and SESSION_LIFE_TIMEOUT_ON;

        IF x=0 then
            return false;
        else
            return true;
        end if;
    EXCEPTION
    WHEN OTHERS THEN
        RETURN false;
   end is_active;

Save and Compile

Check and confirm, package must be compiled successfully.

Now we are ready with our package ‘LAMCX_AUTH’, lets configure it in our Authenticate Scheme. Navigate back to ‘create a custom authentication scheme’ section in this blog post, and see how we have mapped this package in point no. 7 sub-section 2, image 2.

Now we are done with our custom authentication scheme creation with appropriate custom logic.

You can create Registration and Password reset page and use these procedures, accordingly.

Your existing Login page will work with this custom auth.

4
0

Get in Touch

This will close in 0 seconds

Scroll to Top