Custom Authentication on Oracle APEX with User Roles Selection

In this article we are going to discuss about custom authentication on Oracle APEX with user roles selection. On every application built with Oracle APEX there will be found one login page. The login process will done by comparing username & password typed by user against data stored in authentication table. There will be 2 text input required which are username and password. In this article we are going to add 1 additional text input that will check the user roles that stored in the authentication table. Checking in addition to comparing the validity of whether the username and password also compare the role or permissions owned by the user. Suppose that the existing roles in the application is ADMIN, DOSEN and MAHASISWA (the roles names is freely defined).

Here are the steps of making custom authentication by implementing the user roles checking process:

1. Create one table as the authentication table that will be used for storing the authorized user informations.

a. The table name is CustomLogin {#uname, pwd, status}

b. Insert three records as the user roles. You might add more than three records but the roles must be ADMIN or DOSEN or MAHASISWA.

2. On Application Builder, create new Desktop Application

3. Edit page 101 (Login), add one Select List item namely P101_STATUS

a. Choose Static on Select List Type

The List of items should be like this:

b. Make an arrangement (Grid Layout) so the result should be like this:

4. Back to main Application design page, Go to Shared Components

a. Create new authentication schemes

b. Choose Based on a pre-configured scheme from the gallery

c. Give it a name of cek_login and the Schema Type is Custom 

d. On Authentication Function Name fill cek_login

e. On the Source enter these codes:

FUNCTION cek_login

(p_username IN VARCHAR2, p_password IN VARCHAR2)

RETURN BOOLEAN

AS

hasil NUMBER := 0;

BEGIN

SELECT 1 INTO hasil FROM customlogin

WHERE UPPER(uname) = UPPER(p_username)

AND pwd = p_password

AND status = :P101_STATUS;

RETURN TRUE;

EXCEPTION WHEN NO_DATA_FOUND THEN

RETURN FALSE;

END cek_login;

f. Complete the process of authentication schemes creation, make sure the active scheme is cek_login authentication (Current)

5. Run the application and login as admin (use username and password stored on authentication table)

a. Enter the username administrator, password admin, status ADMIN then click on login button

Home page should be shown, on the right top of the page will be found user information about current user

b. Logout then login with the username administrator, password admin, status DOSEN

Error message should be found because the login information is not valid. Try to empty the password and try to login, the error message should be appear.

6. Try to login as dosen roles

a. Enter the username dosennya, password dosen, status DOSEN

Home page will be shown, look at the right top of the page, it should be found the information about current user which is DOSENNYA.

7. Try to login as mahasiswa

a. Enter the username mahasiswanya, password mahasiswa, status MAHASISWA

Home page will be shown with MAHASISWANYA as the current user logged in.

Okay that’s the discussion and an example about creating custom authentication with user roles selection on Oracle APEX. You are freely to modify the source code based on your needed.

Thank You - Bobsis