Custom Authentication in Apex

Problem

I want to be able to sign in with a custom user and password. Each user is of a specific type, I'll also show how to hide certain pages based on the user type.

Before anybody jumps down my throat, let me say that although I've made reasonable attempts to hash the passwords and usertypes, you will want to adapt this (make more robust) if you have a sensitive application.

Solution

Create Tables to Hold you User Information

create table admin.xxx_ccs_users

(user_id    number,

username varchar2(200),

usertype varchar2(100),

pwd         varchar2(100));

You can create a simple administration screen to populate this information.

Create A Security Package

This contains useful functions for creating and retrieving user and security information.

I've edited it a bit, note that the package has line that can be adjusted according to your DB version and privileges.

CREATE OR REPLACE PACKAGE BODY admin.xxx_ccs_security AS
  FUNCTION get_hash (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
    RETURN VARCHAR2 AS
    l_hash VARCHAR2(30) := 'QPWOEIR1';
  BEGIN
    -- Pre Oracle 10g
    RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(
      input_string => UPPER(p_username) || l_hash || UPPER(p_password));
    -- Oracle 10g+ : Requires EXECUTE on DBMS_CRYPTO
    --RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(UPPER(p_username) || l_hasg || UPPER(p_password)),DBMS_CRYPTO.HASH_SH1);
  END;
  PROCEDURE add_user (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2,
                      p_usertype in varchar2) AS
  BEGIN
    INSERT INTO admin.xxx_ccs_users
       (user_id,
        username,
        usertype,
        pwd)
    VALUES (
      admin.xxx_ccs_users_seq.NEXTVAL,
      UPPER(p_username),
      get_hash(p_username,p_usertype),
      get_hash(p_username, p_password)
    );
    COMMIT;
  END;
  PROCEDURE change_password (p_username      IN  VARCHAR2,
                             p_old_password  IN  VARCHAR2,
                             p_new_password  IN  VARCHAR2) AS
    v_rowid  ROWID;
  BEGIN
    SELECT rowid
    INTO   v_rowid
    FROM   admin.xxx_ccs_users
    WHERE  username = UPPER(p_username)
    AND    pwd = get_hash(p_username, p_old_password)
    FOR UPDATE;
    UPDATE admin.xxx_ccs_users
    SET    pwd = get_hash(p_username, p_new_password)
    WHERE  rowid    = v_rowid;
    COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;
  PROCEDURE valid_user (p_username  IN  VARCHAR2,
                        p_password  IN  VARCHAR2) AS
    v_dummy  VARCHAR2(1);
  BEGIN
    SELECT '1'
    INTO   v_dummy
    FROM   admin.xxx_ccs_users
    WHERE  username = UPPER(p_username)
    AND    pwd = get_hash(p_username, p_password);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
  END;
  FUNCTION valid_user (p_username  IN  VARCHAR2,
                       p_password  IN  VARCHAR2)
    RETURN BOOLEAN AS
  BEGIN
    valid_user(p_username, p_password);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END;
  function get_user_type (p_username in varchar2) return varchar2 as
     v_usertype varchar2(100);
     begin
        select usertype
        into v_usertype
        from admin.xxx_ccs_users
        where username = upper(p_username);
        for v_check in (select 'A' utype from dual union all
                        select 'R' from dual union all
                        select 'D' from dual union all
                        select 'T' from dual union all
                        select 'S' from dual union all
                        select 'O' from dual union all
                        select 'F' from dual union all
                        select 'B' from dual union all
                        select 'Z' from dual union all
                        select 'P' from dual union all
                        select 'E' from dual) loop
           if get_hash(p_username,v_check.utype) = v_usertype then
              return v_check.utype;
           end if;
        end loop;
        return '?';

     exception
        when others then
           Raise_application_error(-20000,'get_user_type.xxx_ccs_security: '||sqlerrm);
     end;


END;

I'll leave you to create the spec (it's a copy/paste job). I would leave out get_hash function from the spec to add a small layer of security.

for my sample application, I created a set group of user types. You might find a more elegant way of doing this, but it worked for me.

APEX Authentication

This is your checkpoint when the user signs in.

Go to the the shared components section of your application. Click on "Authentication Schemes" then "Create".

I left everything else to default. Once saved, the custom schema is automatically set for the application.

As I was messing around with this before I created a form for maintaining users, I found the following script useful for creating a user called barry, password "apply1".

begin
   admin.xxx_ccs_security.add_user ('barry',
                      'apply1',
                      'A');
end;

APEX Authorization

This is where you can modify certain behavior (for example hiding a page), based on the user type.

My application has a desktop navigation menu. I wanted only Administrators and Executives to Access it. Like this:

I did this by changing the condition against the navigation menu entry. Shared components > Navigation Menu. I selected the Administration menu entry then made the change shown below.

As an extra security measure (say you have a redirect to this page from somewhere else), you can add an authorization screen to the page too.

To do this, I created authorization schema. Shared components > Authorization Schemas > Create.

See Below:

I then went to the page that I wanted to restrict, and changed the "Authorization Scheme" from the LOV to "Administrator or Executive".  

Anybody trying to access the page accidentally will then get the message that I defined above "Insufficient Privileges, user is not an Administrator or Executive"

Acknowledgements

Thanks to Tim of Oracle_base for the inspiration.

Version: Apex 5.0