Create a Customized authentication Employee Login System in Oracle Apex 👩‍💻

In this tutorial, I will demonstrate how to create a customized login authentication system for different employees. We will cover the process of logging in, viewing employee details, switching between users like Lisa Maya and Felix Richter, and signing out securely. This system will be implemented using Oracle Apex and SQL commands.

Step-by-Step Guide

1. Adding Email Addresses to Employee Profiles

First, we need to define email addresses for the employee profiles. This is done by updating the database table. The email addresses will be generated from the employee names.

1.1. SQL Command to Create Email Addresses

We use an SQL query to create email addresses based on the employee names. Spaces are replaced with dots, and the names are converted to lowercase.

 

SQL

xxxxxxxxxx

 

1

-- SQL command to create email addresses based on employee names

2

SELECT LOWER(REPLACE(EMPL_NAME, ' ', '.') || '@apexnote.de') 

3

FROM APNO_EMPLOYEE;

1.2. SQL Command to Update the Table

After generating the email addresses, we update the table to store the new email addresses.

 

SQL

xxxxxxxxxx

 

1

-- SQL command to update the table with the new email addresses

2

UPDATE APNO_EMPLOYEE

3

SET EMPL_EMAIL = LOWER(REPLACE(EMPL_NAME, ' ', '.') || '@apexnote.de');

2. Setting Up the Login Mechanism with Oracle Apex

Next, we set up the login mechanism by creating a new authentication schema and designing a login page.

2.1. Creating the Authentication Schema

• Navigate to the Application: Go to the Oracle Apex application and select "Shared Components".
• Select Authentication Schemas: Choose "Authentication Schemas" and create a new schema.
• Name the Schema: Name the schema, for example, "employee custom login".
• Select Schema Type: Choose "Database Account" as the schema type.
• Activate the Schema: Set the new schema as the current schema.

2.2. Designing the Login Page

• Open the Login Page: Go to the login page and customize it to use the new email addresses.
• Create an Employee Report: Create a report with all employees and display it on the login page.
• Link the Emails: Link the employee emails to the new login page.

 

SQL

xxxxxxxxxx

 

1

-- Example SQL query to display employee data

2

SELECT EMPL_NAME, EMPL_EMAIL 

3

FROM APNO_EMPLOYEE;

3. Implementing the Login Process

Create a new page for login confirmation and implement the login process.

3.1. Creating the Confirmation Page

• Create a New Page: Create a new blank page with a hidden field for the email and a login button.
• Set Up Linking: Link this page to the main login page.

3.2. Implementing the Login Process

• Define the Login Process: Implement a process that performs the login based on the selected email.

 

SQL

xxxxxxxxxx

10

 

1

-- Example code for the login process

2

BEGIN

3

  -- Login process based on the email

4

  APEX_AUTHENTICATION.LOGIN(

5

    p_username => :P9998_EMAIL, -- Hidden email field

6

    p_password => NULL,         -- No password required

7

    p_session_id => APEX_UTIL.GET_SESSION_ID,

8

    p_app_page => '1'           -- Redirect to the home page

9

  );

10

END;

4. Testing and Verifying

• Test the Login Process: Test the login process with different employees.
• Verification: Verify that users can log in and log out correctly.

Detailed Steps and Explanations

Step 1: Generating Email Addresses

Initially, our employee profiles do not have email addresses. To change this, we use SQL commands to generate email addresses based on the employee names. The following SQL command creates email addresses by replacing spaces with dots and converting the names to lowercase:

 

SQL

xxxxxxxxxx

 

1

SELECT LOWER(REPLACE(EMPL_NAME, ' ', '.') || '@apexnote.de') 

2

FROM APNO_EMPLOYEE;

After verifying that the generated email addresses are correct, we update the APNO_EMPLOYEE table to store these email addresses:

 

SQL

xxxxxxxxxx

 

1

UPDATE APNO_EMPLOYEE

2

SET EMPL_EMAIL = LOWER(REPLACE(EMPL_NAME, ' ', '.') || '@apexnote.de');

Step 2: Setting Up the Authentication Schema and Login Page

2.1. Creating the Authentication Schema

In Oracle Apex, navigate to "Shared Components" and select "Authentication Schemas". Here, create a new schema named "employee custom login" and choose "Database Account" as the schema type. Finally, set this schema as the current schema.

2.2. Designing the Login Page

We customize the login page to use the new email addresses. This involves creating a report that displays all employees and linking the email addresses to the new login page. An example SQL query to display employee data is:

 

SQL

xxxxxxxxxx

 

1

SELECT EMPL_NAME, EMPL_EMAIL 

2

FROM APNO_EMPLOYEE;

Step 3: Implementing the Login Process

3.1. Creating the Confirmation Page

We create a new blank page with a hidden field for the email and a login button. This page is linked to the main login page.

3.2. Implementing the Login Process

The login process is implemented by creating a PL/SQL block that performs the login based on the selected email. Here is an example code:

 

SQL

xxxxxxxxxx

 

1

BEGIN

2

  -- Login process based on the email

3

  APEX_AUTHENTICATION.LOGIN(

4

    p_username => :P9998_EMAIL, -- Hidden email field

5

    p_password => NULL,         -- No password required

6

    p_session_id => APEX_UTIL.GET_SESSION_ID,

7

    p_app_page => '1'           -- Redirect to the home page

8

  );

9

END;

Step 4: Testing and Verifying

After completing all the steps, we test the login process with different employees and verify that users can log in and log out correctly.

Conclusion

This tutorial demonstrates how to create a customized login authentication system for employees. By using Oracle Apex and SQL commands, we can generate email addresses, set up an authentication schema, and design a user-friendly login page. This simplifies the process of logging in and managing employees within an application.

Good luck with your implementation!

 {fullWidth}