Controlling who can and cannot access your application is an extremely important consideration when designing your APEX applications. Oracle APEX provides several different ways of authenticating users of your application. Some of the most common are:
- APEX Authentication – This is the default authentication scheme used by APEX. This scheme uses the built-in users and groups created by a workspace administrator within the workspace where the application is installed. This method is often referred to as cookie user accounts. It offers a quick way to manage and maintain a simple user repository without having to create your own user-management routines.
- Open door credentials: This scheme allows users to successfully authenticate using any username, without having to provide a password. The username is not checked against any sort of repository, so this scheme is really useful only for testing purposes or where you don’t need to enforce any form of account uniqueness.
- Database account authentication: This scheme allows you to use Oracle accounts to authenticate your users against. Users need to specify a valid database username and password in order to successfully authenticate to your application. This scheme is ideal if you have already created a database user for each of your end users.
The purpose of this post is to discuss another authentication method available to APEX users – custom authentication. This form of authentication works by storing the users of your application in a user table, and whenever someone attempts to login to your application, a function verifies if the submitted username and password are found in the user table you created. Only if a match is found will a user be permitted to authenticate.
Let us now look at the steps involved in utilizing this form of authentication.
Please note: You can download the setup script for this article by clicking this link: setup_script. This script will create the user table, insert sample data, and create the authentication package discussed in this article. Simply copy and paste the script into SQL Developer, Toad, or SQL Plus to execute it.
Step 1 – Creating a user table
In order to use custom authentication, you must have a table where you store the users of your application. Let us use the following user table as an example:
CREATE TABLE user_repository(USER_ID NUMBER NOT NULL,
USERNAME VARCHAR2(8) NOT NULL,
USER_PASSWORD VARCHAR2 (20) NOT NULL,
FIRST_NAME VARCHAR2 (30) NOT NULL,
LAST_NAME VARCHAR2 (30) NOT NULL,
EMAIL VARCHAR2 (50) NOT NULL,
ACCOUNT_STATUS VARCHAR2(10) NOT NULL,
NUM_INVALID_LOGINS NUMBER NOT NULL,
Note the two fields ACCOUNT_STATUS and NUM_INVALID_LOGINS. These fields will enable the locking of accounts when a certain number of invalid logins have occurred.
Step 2 – Insert a few sample users that can be used to test your custom authentication scheme.
For this example let us create two users; we’ll call them USER_01 and USER_02.
INSERT INTO user_repository
VALUES(1, ‘USER_01’, ‘user01_pass’, ‘Lewis’, ‘Hamilton’, ‘firstname.lastname@example.org’, ‘UNLOCKED’, 0);
INSERT INTO user_repository
VALUES(2, ‘USER_02’, ‘user02_pass’, ‘Fernando’, ‘Alonso’, ‘email@example.com’, ‘UNLOCKED’, 0);
Note: For the purposes of this article, passwords are stored in an unencrypted form. Obviously, in a real application, you would want to encrypt your passwords using the DBMS_CRYPTO package. The use of encryption will be covered in a subsequent article.
Step 3 – Create your packaged authentication function
As a general best practice, it is recommended to place as much of your PL/SQL code utilized by your APEX applications in packages. This way, whenever changes are required, modifications are made in one place, as opposed to hunting down every occurrence of the code that requires changing.
Let us now create our packaged authentication function that our custom authentication scheme will use.
First, we create the package specification containing our authentication function. Let’s simply call the function authenticate_user. Please note that this package is meant as an example only; also note that the structure of procedure update_invalid_login_count, and the structure of function get_num_invalid_logins is such that the username field must be unique, or else these functions could return more than one row. Thus, a unique constraint must exist on the username column to ensure that there cannot be two identical usernames.
CREATE OR REPLACE PACKAGE AUTHENTICATION_PKG
FUNCTION authenticate_user(p_user_name_in USER_REPOSITORY.username%TYPE,
p_password_in USER_REPOSITORY.user_password%TYPE) RETURN BOOLEAN;
FUNCTION get_num_invalid_logins(p_username_in IN USER_REPOSITORY.username%TYPE) RETURN NUMBER;
PROCEDURE update_invalid_login_count(p_username_in IN USER_REPOSITORY.username%TYPE);
PROCEDURE lock_user_account(p_username_in IN USER_REPOSITORY.username%TYPE);
Note: As the package body is too large for the purposes of this post, please use the script located earlier in this article. Make sure to compile this package before proceeding on to the next steps; it will be required.
Step 4 – Creating your custom authentication scheme
Now that our authentication package is compiled in the schema where our APEX application is located, Login to the apex application in which you wish to utilize custom authentication. Click Shared Components, and the under Security, click Authentication Schemes, as shown in Figure 1 below.
On the Next screen, select Based on a pre-configured scheme from the gallery, and click Next, as shown below.
You will arrive at this screen. Enter a name for your authentication scheme (Ex. My_Custom). For Scheme type, select Custom from the drop down list. In the field Authentication Function Name, enter the name of the authentication function you created in your authentication package, as demonstrated in step 3. Leave the other fields blank.
Click Create Authentication Scheme and your custom scheme is now created, as shown below. Your new authentication scheme will be shown as the current one.
Step 5) Modifying the Login page to make use of the custom authentication function
Now that our custom authentication scheme has been created, we must now modify the Login page of our application to make use of it.
First, go into Application Builder and click on your Login page (by Default, it’s Page 101) so that we can modify it. Here is what Page 101 looks like.
Double click the Login process, as highlighted in blue in the image above.
You will arrive at the following screen.
We must now replace the PL/SQL code in the Source section shown above by the following. Note: you should comment out the original code shown above; that way, if you ever wish to revert back to APEX authentication, you can just uncomment out the original code and return to using that form of authentication.
Here is the modified source, shown in Figure 7 below.
Next, click Apply Changes to complete this step. You will be returned to your Login page, as shown in Figure 5 shown previously.
You have now completed the setup of your custom authentication scheme.
We have a few last steps to complete in order to enhance the user-friendliness of this customized login page. First, we must create a few APEX application items that will be used to display information on the login screen (Ex- if a login is unsuccessful, the user receives a message that their username and password was not found). This message functionality will be handled by the LOGIN_MESSAGE application item; the contents of this item, and the other application item we will create, USERNAME, are populated by our authentication package that we created earlier.
Go to your Application Builder for your application and click Shared Components, as shown below.
Next, click Application Items, as shown below.
Click Create on the following screen.
We now have to create two application items, namely ‘LOGIN_MESSAGE’, and ‘USERNAME’. Ensure that you enter the names all in caps, or it will not work. This is due to the fact that our authentication package is referring to these application items to set their values in session state.
The image below shows the creation of the LOGIN_MESSAGE item. Simply enter the name of the application item and click Create Application Item. Simply repeat this process for the other application item called ‘USERNAME’.
Now that our application items are created, we must create a region on the login page that will be used to display the contents of the LOGIN_MESSAGE item.
Click Page 101 in the Application builder and you will return to this page.
We will now create an HTML region which will display messages to the user if an unsuccessful login occurs.
First, in your application builder shown above, right click Body and then select Create, as shown below.
We wish to create an HTML region to display the login messages, so click HTML, as shown below.
On the following screen, click HTML again to proceed. You will arrive at the following screen, where you must enter a name for the Region (Ex- Login Message), and enter 10 for the Sequence field.
Click Next to proceed. We will now define the source for our HTML region. In our case, the source will be the application item we created earlier, namely LOGIN_MESSAGE. The trick here is to enter LOGIN_MESSAGE as ‘&LOGIN_MESSAGE.’ The leading ampersand and trailing period are required in order to reference the item’s session state value.
Click Next to proceed.
We must now add a condition to control when this new HTML region displays. We only want this region to display after a user attempts to login, and that this attempt was unsuccessful.
Enter the condition as shown below. The reason behind using this condition is as follows: When an unsuccessful login occurs, the LOGIN_MESSAGE application item is populated by our authentication package; it thus makes sense to control the conditional display of this region to whether or not this item contains a value.
Click Create Region to finish creating your region. You will now be returned to your login page, page 101.
It is now simply a matter of running your Login page by clicking the Run button on Page 101 and entering the credentials of one of the two sample users you have created and clicking Login. You will be logged in by your custom authentication function and taken to your application’s home page.
Here is the login screen. I enter the username of a sample user, in this case user_01, and the password for this user (in this case, user01_pass ). I then click Login.
I am successfully logged in and the home page of my application appears. Note that this application’s homepage is blank as it was only created for the purposes of this article.
As a result of the Login Message region we created, and the use of the underlying application item LOGIN_MESSAGE, if a login attempt is unsuccessful, here is the message that will appear.
Furthermore, if the maximum number of invalid logins is reached, the following message will appear, alerting the user that his/her account has been locked.
I hope this article has been helpful in demonstrating how you can setup custom authentication in your APEX applications. Feel free to ask questions and provide comments.