Application Express comes with three standard ways to authenticate users on applications. We can use the credentials of Database users, we can use the credentials of users defined within APEX itself, or we can use the credentials defined in the Database Access Descriptor. In this recipe, we will show how to add our own Authentication Scheme to this list.
An Authentication Scheme controls access to an entire application as opposed to an Authorization Scheme that controls access to individual components inside the application.
Simply put, an Authentication Scheme is what is called when a user clicks the Login button.
First, we need a table to store the data for our users. In our application, this table will be APP_USERS. It contains columns for username and password, so we can create a very basic authentication scheme. Make sure this table is ready before continuing in this recipe.
Enter at least one row of data into the table that we can use to login at the end of the recipe.
Also we need two functions in place. APP_HASH is a function that will use a hashing algorithm and a salt to mask the real password. To make it more secure, the current date can be used in the algorithm, but this is enough for our example.
In a production environment, it is probably a good idea to wrap this code, because it can help intruders gain access to the application.
create or replace function app_hash (p_username in varchar2, p_password in varchar2) return varchar2 is l_password varchar2(4000); l_salt varchar2(4000) := 'DFS2J3DF4S5HG666IO7S8DJGSDF8JH'; begin l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5 (input_string => p_password || substr(l_salt,10,13) || p_username || substr(l_salt, 4,10))); return l_password; end;
[1346_01_13.txt]
APP_AUTH is a function that will check if the user is valid and if the password is entered correctly.
create or replace function app_auth (p_username in VARCHAR2, p_password in VARCHAR2) return BOOLEAN is l_password varchar2(4000); l_stored_password varchar2(4000); l_expires_on date; l_count number; begin select count(*) into l_count from app_users where upper(username) = upper(p_username); if l_count > 0 then select password into l_stored_password from app_users where upper(username) = upper(p_username); l_password := app_hash(p_username, p_password); if l_password = l_stored_password then return true; else return false; end if; else return false; end if; end;
[1346_01_14.txt]
The first thing we have to do is add the new authentication scheme to the list of existing schemes.
- Click the Create button.
- Choose From Scratch and click Next.
- Name it Application Authentication and click Next.
- Click Next on the following two screens as well.
- Select Page in This Application and page 101 on the Invalid Session Target and click Next until you reach Credentials verification method.
- Select Use my custom function to authenticate and enter return app_auth in the Authentication Function.
- Click Next until you reach Logout URL.
- The Logout URL is
wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:1 - Click Next and Create Scheme.
The last step is to make the new Authentication Scheme the current for the application. To do this, navigate to the tab Change Current on the schemes overview screen. Select the new scheme from the list and click the Make Current button.
You can now log in to the application using a username and password from the APP_USERS table.