Power Of Oracle Apex

Custom Authentication in APEX

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:










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’, ‘lewis.hamilton@gmail.com’, ‘UNLOCKED’, 0);

INSERT INTO user_repository

VALUES(2, ‘USER_02’, ‘user02_pass’, ‘Fernando’, ‘Alonso’, ‘fernando.alonso@gmail.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.



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.

Figure 1

Figure 1

On the Next screen, select Based on a pre-configured scheme from the gallery, and click Next, as shown below.

Figure 2

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.

Figure 3

Figure 3

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.

Figure 4

Figure 4

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.

Figure 5

Figure 5

Double click the Login process, as highlighted in blue in the image above.

You will arrive at the following screen.

Figure 6

Figure 6

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.

Figure 7

Figure 7

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.

Figure 8

Figure 8

Next, click Application Items, as shown below.

Figure 9

Figure 9

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’.

Figure 10

Figure 10

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.

Figure 5

Figure 11

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.

Figure 12

Figure 12


We wish to create an HTML region to display the login messages, so click HTML, as shown below.

Figure 13

Figure 13

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.

Figure 14

Figure 14

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.

Figure 15

Figure 15

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.

Figure 16

Figure 16

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.

ScreenHunter_35 Sep. 24 14.35

Figure 17

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.

ScreenHunter_23 Sep. 23 16.17

Figure 18

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.

Figure 19

Figure 19

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.

Figure 20

Figure 20


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.

Tagged on:

20 thoughts on “Custom Authentication in APEX

  1. Na'eem Hashim

    Hi there

    I followed every step but i can’t seem to login after i click the ‘login’ button.
    ERR-1002 Unable to find item ID for item “USERNAME” in application “63982”. I get this error all the time

    1. Anonymus

      Be sure that you are using :P_101_USERNAME and :P101_PASSWORD when you can you call your authentication function:

      result := AUTHENTICATION_PKG.authenticate_user(p_user_name_in=>:P101_USERNAME, p_password_in => :P101_PASSWORD);

  2. Tannu Jiwnani

    i followed the steps till step 5 figure 10 so now my application should let me login if I enter the right user id and password. But after the login page it takes me again to login page never goes to home page

  3. TJ

    After spending half a day trying to work this out, this was the only blog that adequately explained how to do this. Thank you guru!

  4. ka

    Great post. I got the part on we need to change the shared_component but I didn’t get the part we have to change the login page process. However, I stil cant get it to work….

    ORA-06550: line 8, column 3: PLS-00201: identifier 'WWW_FLOW_CUSTOM_AUTH_STD.POST_LOGIN' must be declared ORA-06550: line 8, column 3: PL/SQL: Statement ignored

  5. Riky

    I got this error
    ORA-06550: line 4, column 42: PLS-00306: wrong number or types of arguments in call to ‘AUTHENTICATE_USER’ ORA-06550: line 4, column 1: PL/SQL: Statement ignored
    But can’t figure where I made mistake. Can somebody help me?

  6. serge

    Thank you for the post. There are some modification when creating a region for the LOGIN_MESSAGE that needs to be done, when using latest Apex version. Also you may remove creating USERNAME Application Item and replacing apex_util.set_session_state(‘USERNAME’, UPPER(l_username)); with apex_util.set_authentication_result(0); You may add a new procedure reset_invalid_login_count when successful authentication occurs.

  7. Ivan

    Hi, getting the error
    ‘ORA-06550: line 7, column 1: PLS-00306: wrong number or types of arguments in call to ‘POST_LOGIN’ ORA-06550: line 7, column 1: PL/SQL: Statement ignored’

    Any suggestions ?

  8. Adam Pearson

    i can’t quite figure out what happens if there’s no matching user record – there doesn’t appear to be any error capture for this. Or am I missing something?

  9. pallll

    Thanks a lot. It worked at the very first time. Is there a way for the user to update his password, once entered in the application ? Or a specific APEX page must be developed for this goal ?

  10. s_b

    Thanks for the article, it’s very clear! But I can’t get it working, I’m getting this error:

    ORA-20001: An error has occured in function authenticate_user – ORA-20001: An error occurred in procedure get_num_invalid_logins – ORA-01403: no data found

  11. Lutfi Nurrohman

    ORA-04067: not executed, package body “HR.AUTHENTICATION_PKG” does not exist ORA-06508: PL/SQL: could not find program unit being called: “HR.AUTHENTICATION_PKG”

  12. FirstMuhammad

    I have noticed you don’t monetize your site, don’t waste your traffic, you can earn extra cash every month because you’ve got high quality content.
    If you want to know how to make extra money, search for: Boorfe’s tips best
    adsense alternative

Leave a Reply

Your email address will not be published. Required fields are marked *