Sunday, 1 July 2007

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

25 comments:

Peter Lorenzen said...

Nice! I have created a link to your How-to from the Security section of the APEX Wiki: http://wiki.shellprompt.net/bin/view/Apex/Authentication

Anonymous said...

A good and no-nonsense explanation of a topic I'm sure many (myself included) have wondered about how to implement.

runcsmeduncs said...

Keep checking back as I have Part 2 written and hopefully will get it published some time this week (Work load permitting)

It will deal with the second aspect on how to allow authorized users to see pages, components on pages and prevent users from altering the url to page jump.

Will also post a link on the forum once its published.

Regards

Duncan

Anonymous said...

Eaglerly awaiting the second part, any plans to publish it soon?

By the way, the package doesn't compile because the string concatenation characters (||) seem to have fallen out of your code sample in the login and get_hash routines.

Mike Barnett said...

Hi Duncan,
Hope you're well! I'm using this again on another site, so was pleased to find this packaged description of how to implement, rather than having to go back and strip bits out of Westminster!

Cheers,

Mike

Irina said...

I tried to create the APP_USERS with a primary key (ID) and an access level column...of course i adapted the package add_user routine. And in XE i get the strangest behavior - with a PK i get "invalid credentials" but if i repeat the process without a PK it all works. Curious...i've tried it 4 times around and it's the same.

Any ideas?

Anonymous said...

Hi Duncan, could you provide us with an example on how to decrypt a password encrypted with this package? This would be helpful when a user wants to retrieve a password through the application.

Thanks,
Art

Anonymous said...

Duncan,
Great example. This explains the custom authentication clearly.
How do I implement auto-login based on certain condition and display login screen otherwise?

Thanks,
Jay

Duncan Mein said...

Art

There is no way to decrypt the password as it was encrypted using an Oracle Packaged procedure. They do not offer the decryption as it would invalidate every security policy as you could find out user passwords.

The way I build reset password policies is to simply update the existing password with an encrypted new one. No point in sending out the password to the user as the net effect is still the same.

Duncan Mein said...

Jay

I would need to know a little more about your exact requirements before offering any advice on implementing auto-logon.

Happy to chat through the options once I know the requirements

Duncan

Duncan Mein said...

Hey Mike

Glad you found my blog od some use rather than just laughing at the picture on the front page.

I am about to publish the second part of this article so keep checking back. Hows life in London btw. Missing Land?

Justin said...

Duncan,

Any idea when Part 2 will be published?

Thanks,
Justin

Anonymous said...

Hi Duncan,

Still waiting for part 2.

Thanks,

Anonymous said...

Excellent work Duncan, so when are coming with Part 2, and for this part I have one query, I am able to login if I enter username & password in caps or not, I mean I wan't to make it case-sentive(both the user name and password), how to do that????

once again appreciating your work...!!! :)

Machan Sama said...

Hi Dunca,

First of thanks for your efforts. I was really looking for something like this.

I followed your instruction by to the letter, when I login I get the below error

ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to 'VALID_USER' ORA-06550: line 2, column 1: PL/SQL: Statement ignored

ERR-10460 Unable to run authentication credential check function.
Return to application.

Location: f?p=4155:1000:4370652870849899¬ification_msg=Invalid%20Login%20Credentials/A010C15EE33F84EDE9B293C0A7440059

You thoughts please...

Thanks much,
Machan Sama

Rajesh said...

Duncan,

Thanks for such a useful post. How do I implement account locking through custom authentication if the user enters incorrect password 'x' number of times?

I tried to execute the statement 'ALTER USER xxx ACCOUNT UNLOCK' from the package procedure, but it gives insufficient privileges message.

Thanks in advance.

gabs said...

Nice guide! One question: I can see that it's a few years old. Is it obsolete now or is there any hope for Part II ?

Ali butt said...

Thanks bro for such a usefull post

Afaq Ayub said...

Thank very much for a wonderful post. I used your post to describe Custom Authentication in APEX 4.0 with screenshots.

http://www.afaqayub.com/oracle-2/apex-authentication-scheme/

Sam Woods said...
This comment has been removed by the author.
Sam Woods said...
This comment has been removed by the author.
Anonymous said...

Great tutorial, thanks. I have followed all of the instructions and when I run the application I get the login screen. I enter 'testuser' and 'password' and I get Invalid Login Credentials error. Any ideas why this is happening?

Marcel said...

Hi Duncan,

I got the same error that Machan said.

Could you help?

thanks.


I followed your instruction by to the letter, when I login I get the below error

ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to 'VALID_USER' ORA-06550: line 2, column 1: PL/SQL: Statement ignored

ERR-10460 Unable to run authentication credential check function.
Return to application.

Anonymous said...

Thanks! I was able to follow the instructions and created my new Custom authentication w/o any problem.

Anonymous said...

I also experienced
ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to 'MY_LOGIN_FUNCTION' ORA-06550: line 2, column 1: PL/SQL: Statement ignored

The solution was to use the right function signature not only with respect to parameter TYPE but also with respect to parameter NAME.

Wrong:
CREATE OR REPLACE FUNCTION MY_LOGIN_FUNCTION
(
p_user IN VARCHAR2
, p_pwd IN VARCHAR2
) RETURN BOOLEAN AS [...]

Right:
CREATE OR REPLACE FUNCTION MY_LOGIN_FUNCTION
(
p_username IN VARCHAR2
, p_password IN VARCHAR2
) RETURN BOOLEAN AS [...]


Best regards,
Holger.