Thursday, March 29, 2018

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

3 comments

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

If You Enjoyed This, Take 5 Seconds To Share It

3 comments:

  1. Tried, i got stuck and does not load the login page properly

    ReplyDelete
  2. Hi Meisur,

    I supposed you already create GLOBAL_STATUS_ITEM variable based on step#6.
    Could you please share the screen shoot from step#7 (Choose the Custom Authentication Schemes on current application)?
    Thank You

    ReplyDelete
  3. Hi, I successfully apply the code given and change the attribute to adapt my table. I am wondering is that any way to lock user account after several failed attempt? I got two extra columns "is_locked" and "login_failed" which record whether the account is locked by 'Y' and not locked by 'N', and the login_failed time will be the time attempted fail login base on username. Wondering is that anyway to update these two column with codes? Thank you.

    ReplyDelete