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;
[9672_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;
[9672_01_14.txt]

The first thing we have to do is add the new authentication scheme to the list of existing schemes:

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 on the Make Current button.

You can now log in to the application using a username and password from the APP_USERS table.