How To Set Default Home Page Of Different User Privileges On Oracle APEX 5

In this article we are going to discuss about how to set default home page of different user privileges on Oracle APEX 5. Oracle APEX (Application Express) will create one login page as a default page on every application that we made. As a default it will use the Oracle APEX authentication schemes to check the username and password provided by user on login page. To manage that user validation credentials we have to go on workspace management and go to user management sections.

We can use another way to manage the login validation by using our own table. To do this process we must choose the Custom Authentication Schemes on application properties from the application that we create. With this custom authentication we may store any number of attributes to check the user login credentials or to change default behavior of Oracle APEX login flows.

This article will give you step by step explanations of how to create login page on Oracle APEX application that is not the default behavior of Oracle APEX login flows.

1.     Create table namely TUSER for holding the user data

CREATE TABLE tuser(
                Id NUMBER PRIMARY KEY,
                Uname VARCHAR2(24) UNIQUE,
                Pwd VARCHAR2(128),
                Status VARCHAR2(12)
);

2.       Create Desktop Application with 3 new Pages . These three pages will be used as default home page of three different users.

3.       Customize the AdminPage on page 2.

4.       Customize the ManagerPage on page 3.

5.       Customize the StaffPage on page 4.

6.       Create Application Item, namely GLOBAL_STATUS_ITEM with Unrestricted Session State Protection setting

a.       From your application on application builder go to Shared Components/Application Items

b.      Set the values

7.       Choose the Custom Authentication Schemes on current application

a.       Go to Shared Components/Authentication Schemes then Create

b.      Create New Custom Authentication Scheme

c.       Fill the PL/SQL Code area

FUNCTION my_user_auth

                (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN

                AS

                  Result NUMBER :=0;

                  Sts VARCHAR2(12);

                BEGIN

                  SELECT 1, status INTO Result, Sts

                  FROM tuser

                  WHERE UPPER(uname)=UPPER(p_username)

                  AND pwd = p_password;

                  IF Result =1 THEN

                    APEX_UTIL.SET_SESSION_STATE('GLOBAL_STATUS_ITEM', Sts);

                    RETURN TRUE;

                  ELSE

                    RETURN FALSE;

                  END IF;

                EXCEPTION

                  WHEN NO_DATA_FOUND THEN

                                RETURN FALSE;

END my_user_auth;

d.      Make sure your custom authentication scheme is active, indicated with word of “Current”

8.       Edit Page 1 (Home)

a.       Create Branches

b.      Set Brach Point and Branch Type as follows

c.       Fill Branch Action with PL/SQL Script

DECLARE

  Psts VARCHAR2(64);

  Result VARCHAR2(8);

BEGIN

  Psts := APEX_UTIL.GET_SESSION_STATE('GLOBAL_STATUS_ITEM');

  IF Psts='ADMIN' THEN Result:= '2';

  ELSIF Psts='MANAGER' THEN Result:= '3';

  ELSE Result:= '4';

  END IF;

  RETURN Result;

END;

9.       Now it’s time to check the application and its behavior. Run the application, try to login with three username stated on step 1.

a.       Login as USER1, we will see the home page for Admin.

b.      Login as USER2, we will see the home page for Manager.

c.       Login as USER3, we will see the home page for Staff.

The conclusion is we can create one application on Oracle APEX with one login page to redirect different user on their own home page by using Custom Authentication Schemes.

Full article can be downloaded here

Thank You - Bobsis