Custom Authentication / Authorisation Schemes (Part 1)

I often see posts on the APEX forum asking how to implement custom Authentication / Authorisation schemes within their applications. The following is something I have used in several apps over the last couple of years and provides a great base for securing your application.

The code base is largley based on the rather excellent article "Storing Passwords in the Database" found here with a few tweaks and changes.

Firstly, just to clarify, Authentication Schemes control access to the application and Authorisation Schemes control access to page items / regions and even pages themselves.

Implementing your own Authentication

I tend to set up an APP_USERS table that stores Username and encrypted passwords that I Authenticate against when page 101 is submitted. All this will be explained in detail as we go.

Create the APP_USERS table

CREATE TABLE APP_USERS 
(
USERNAME VARCHAR2(10),
PASSWORD VARCHAR2 (255)
);

Create the Application Security Package

CREATE OR REPLACE PACKAGE app_security_pkg
AS
PROCEDURE add_user 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
);

PROCEDURE login
(
 p_uname IN VARCHAR2
,p_password IN VARCHAR2
,p_session_id IN VARCHAR2
,p_flow_page IN VARCHAR2
);

FUNCTION get_hash 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
)
 RETURN VARCHAR2;

PROCEDURE valid_user2 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
);

FUNCTION valid_user 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
)
 RETURN BOOLEAN;

END app_security_pkg;
/

CREATE OR REPLACE PACKAGE BODY app_security_pkg
AS
PROCEDURE login 
          (
           p_uname IN VARCHAR2
          ,p_password IN VARCHAR2
          ,p_session_id IN VARCHAR2
          ,p_flow_page IN VARCHAR2
          )
IS
 lv_goto_page NUMBER DEFAULT 1;
BEGIN
 
 -- This logic is a demonstration of how to redirect 
 -- to different pages depending on who successfully 
 -- authenticates. In my example, it simply demonstrates 
 -- the ADMIN user going to page 1 and all other users going
 -- to page 100. Add you own logic here to detrmin which page 
 -- a user should be directed to post authentication.
 IF UPPER(p_uname) = 'ADMIN'
 THEN
  lv_goto_page := 1;
 ELSE
  lv_goto_page := 100;
 END IF;

 wwv_flow_custom_auth_std.login 
 (
  p_uname => p_uname,
  p_password => p_password,
  p_session_id => p_session_id,
  p_flow_page => p_flow_page || ':' || lv_goto_page
  );

EXCEPTION
WHEN OTHERS
THEN 
 RAISE;
END login;

PROCEDURE add_user 
(
 p_username IN VARCHAR2
,p_password IN VARCHAR2
)
AS
BEGIN
INSERT INTO app_users (username, PASSWORD)
VALUES (UPPER (p_username),
get_hash (TRIM (p_username), p_password));

COMMIT;

EXCEPTION
WHEN OTHERS
THEN 
 ROLLBACK; 
 RAISE;
END add_user;

-- Function to Perform a oneway hash of the users 
-- passwords. This cannot be reversed. This exmaple 
-- is a very week hash and if been used on a production 
-- system, you may want to use a stronger hash algorithm.
-- Read the Documentation for more info on DBMS_CRYPTO as 
-- this is the supported package from Oracle and 
-- DBMS_OBFUSCATION_TOOLKIT is now depricated.
FUNCTION get_hash (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
input_string => UPPER (p_username) 
                || '/' 
                || UPPER (p_password));
END get_hash;

PROCEDURE valid_user2 (p_username IN VARCHAR2, p_password IN VARCHAR2)
AS
v_dummy VARCHAR2 (1);
BEGIN
SELECT '1'
INTO v_dummy
FROM app_users
WHERE UPPER (username) = UPPER (p_username)
AND PASSWORD = get_hash (p_username, p_password);
EXCEPTION
WHEN NO_DATA_FOUND
THEN raise_application_error (-20000, 'Invalid username / password.');
END valid_user2;

FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
BEGIN
valid_user2 (UPPER (p_username), p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN RETURN FALSE;
END valid_user;

END app_security_pkg;
/

Create a Test User

• Log into the APEX Workspace and navigate to the SQL Workshop. If access to the workspace is restricted, this can be done in SQL*Plus providing you log in as the schema owner
• Execute the following command:
  

EXECUTE app_security_pkg.add_user (‘testuser’, ‘password’)
/

Create your own Authentication Scheme

• Navigate to: Shared Components > Authentication Schemes
• Click [Create >]
• Select “From Scratch” and click [Next >]
• Enter “My Auth Scheme” and click [Create Scheme]
• Click on the “My Auth Scheme” icon
• Enter this into the Authentication Function Section: RETURN APP_SECURITY_PKG.valid_user
• Click [Apply Changes]

Switch on your Custom Scheme

• Click on the “Change Current” link. This can be found under the “Tasks” area
• Select “My Auth Scheme” from the drop down list and click [Next>]
• Click [Make Current]

Alter the Login Process on Page 101

Page 101 is created by default when you create your application definition. Assuming that the Username and Password text boxes have remained unaltered, their names will be P101_USERNAME and P101_PASSWORD respectfully. If these item names have remained unchanged you will need to do very little. If you have changed them, update the item names in the following procedure call.

• Edit Page 101
• Navigate to the “Login” process
• Replace the procedure call (if one exists) with the following:

app_security_pkg.login (P_UNAME => :P101_USERNAME, P_PASSWORD => :P101_PASSWORD, P_SESSION_ID => v('APP_SESSION'), P_FLOW_PAGE => :APP_ID );

• Click [Apply Changes]

Test Access to the Application

Access to the Application should be only be gained by the Test User account set up in Step 3. I tend to build a screen that allows Users to be created using the add_user procedure executed in step 3.

This is just the bare bones of a more complete security layer. In previous applications, I have built in password expiration, session timeout and force password change on first login to name but a few. Check back for part 2 which will explain how to implement your own Authorisation Scheme