Custom Authentication / Authorisation Schemes (Part 1)
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
Comments
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
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.
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
Any ideas?
Thanks,
Art
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
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.
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
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?
Any idea when Part 2 will be published?
Thanks,
Justin
Still waiting for part 2.
Thanks,
once again appreciating your work...!!! :)
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
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.
http://www.afaqayub.com/oracle-2/apex-authentication-scheme/
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.
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.