Authentication methods in Oracle Apex.

Authentication methods

APEX provides us with a couple of authentication schemes by default.
Some of which are:

• Application Express – Every user must exist as an APEX user
• Database Account – Every user must have a database account
• Open Door Credentials

The custom authentication is a good option so you can keep full control over how you want this to work. When you have created your application, or at least the start pages, you go to the shared components page where you go select the Authentication Schemes from the Security section.

Custom authentication scheme

When you want to create your own authentication scheme you must create a (packaged) function that must obey a few rules. The function must accept two parameters: the first is the username, and the second is the password. Both these parameters are varchar2 type. Remember, the username and password parameter are sent as clear text. If you want your application to be more secure, you may want to obfuscate the values before sending them to the authentication function. The simplest form of the authentication function is like this:

 FUNCTION authenticate(username_in IN VARCHAR2
                      ,password_in IN VARCHAR2)
 RETURN BOOLEAN
 IS
 BEGIN
   RETURN TRUE;
 END authenticate;

This function is pretty much the same as the open door version, but it’s the start of a real authentication. You create a new authentication scheme by selecting the create button on the screen with the list of defined schemes for this application.

When you create a new scheme you can choose to create a scheme based on an existing scheme, but in this case we want to create a new scheme based on one of the pre-configured schemes.

In this screen you can create the code for the Authentication Function. You can write the code for the function in this screen, but you can also create the function as a stored (package) function so you can use your IDE to create the code.

To create a real authentication scheme you need to do more than just return true for whatever parameters are sent in. You can write code to check the usernames and their passwords, but that would mean you would have to alter the code every time you want to add or remove a user. You are better off using a table which stores the users for the application. The table can look like this:

Table USERS

ID	NUMBER(15,0)	Primary key
USERNAME	VARCHAR2(50)	Unique constraint
PASSWORD	VARCHAR2(50)

You can this table to save more information on the user, such as the email address but for this example that is not necessary.

The function could be updated to something like this:

 FUNCTION authenticate(username_in IN VARCHAR2
                                        ,password_in IN VARCHAR2) RETURN BOOLEAN IS
   l_value       NUMBER;
   l_returnvalue BOOLEAN;
 BEGIN
   BEGIN
     SELECT 1
       INTO l_value
       FROM users
      WHERE 1 = 1
        AND upper(users.username) = upper(username_in)
        AND upper(users.password) = upper(password_in);
   EXCEPTION
     WHEN no_data_found
          OR too_many_rows THEN
       l_value := 0;
     WHEN OTHERS THEN
       l_value := 0;
   END;
   l_returnvalue := l_value = 1;
   RETURN l_returnvalue;
 END;

In this example the username and password are validated against the data in the table. The username and password are stored in plain text in the database, which is not a good idea. You may want to obfuscate the password before storing it.

This is where creating a package for the authentication functions comes in handy. You want to use the same function for obfuscating the password when you store the data as when you check the credentials. You can do this by creating a private function in the package that does the obfuscating and use the outcome of this function for both storing the data and checking the entered credentials. A package like this could look like this:

PACKAGE redgate_authentication IS
  PROCEDURE adduser(username_in IN VARCHAR2
                   ,password_in IN VARCHAR2);
  FUNCTION authenticate(username_in IN VARCHAR2
                       ,password_in IN VARCHAR2) RETURN BOOLEAN;
END redgate_authentication;

PACKAGE BODY redgate_authentication IS
  -- private functions 

  /******************************************************************************\ || function : obfuscate || parameters : text_in -=> text to be obfuscated || || return value: obfuscated value || || purpose : Hash the value of text_in || || author : PBA || (C) 2013 : Patrick Barel \******************************************************************************/
  FUNCTION obfuscate(text_in IN VARCHAR2) RETURN RAW IS
    l_returnvalue RAW(16);
  BEGIN
    dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(text_in), checksum => l_returnvalue);
    RETURN l_returnvalue;
  END obfuscate;
  -- public functions 

  /******************************************************************************\ || procedure : adduser || parameters : username_in -=> Username of the user to be authenticated || password_in -=> Password of the user to be authenticated || || purpose : Add a user to the users table || || author : PBA || (C) 2013 : Patrick Barel \******************************************************************************/
  PROCEDURE adduser(username_in IN VARCHAR2
                   ,password_in IN VARCHAR2) IS
    l_obfuscated_password users.password%TYPE;
  BEGIN
    l_obfuscated_password := obfuscate(text_in => password_in);
    INSERT INTO users
      (id
      ,username
      ,password)
    VALUES
      (users_seq.nextval
      ,username_in
      ,l_obfuscated_password);
    NULL;
  END adduser;
  /******************************************************************************\ || function : authenticate || parameters : username_in -=> Username of the user to be authenticated || password_in -=> Password of the user to be authenticated || || return value: TRUE -=> User is authenticated || FALSE -=> User is not authenticated || || purpose : Check if a user is authenticated based on the username and || password supplied || || author : PBA || (C) 2013 : Patrick Barel \******************************************************************************/
  FUNCTION authenticate(username_in IN VARCHAR2
                       ,password_in IN VARCHAR2) RETURN BOOLEAN IS
    l_obfuscated_password users.password%TYPE;
    l_value               NUMBER;
    l_returnvalue         BOOLEAN;
  BEGIN
    l_obfuscated_password := obfuscate(text_in => password_in);
    BEGIN
      SELECT 1
        INTO l_value
        FROM users
       WHERE 1 = 1
         AND upper(users.username) = upper(username_in)
         AND users.password = l_obfuscated_password;
    EXCEPTION
      WHEN no_data_found
           OR too_many_rows THEN
        l_value := 0;
      WHEN OTHERS THEN
        l_value := 0;
    END;
    l_returnvalue := l_value = 1;
    RETURN l_returnvalue;
  END authenticate;
END redgate_authentication;

Now all you have to do is tell your application that it needs to reference the custom authentication schema. When you created the new authentication schema your application was automatically told to use the new schema, but if you created an authentication scheme in one application and you created a new application where you copied the authentication scheme from an existing application you have to do this yourself. It can also happen that during development you want to use a different authentication scheme than you might in production.