Sunday, March 22, 2009

Custom Authentication Status

When writing custom authentication scheme I noticed that invalid logins weren't showing up properly in the apex logs (apex_workspace_access_log).

Here's how to re-create the problem along with the fix.

First let's create a custom authentication function:

CREATE OR REPLACE FUNCTION blog_cust_auth (
p_username IN VARCHAR2,
p_password IN VARCHAR2
)
RETURN BOOLEAN
AS
BEGIN
IF LOWER (p_username) = 'pass'
AND LOWER (p_password) = '123' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;

Let's set the APEX authentication scheme to use this custom function:

Shared Components / Authentication Schemes:
- Create
- From Scratch
- Name: BLOG_TEST
- Keep hitting "Next" until:
- Credentials Verification Method:
- return blog_cust_auth
- Keep hitting "Next" until the end

- Change the current authentication scheme to BLOG_TEST
- On the Authentication Schemes page, on the right hand side select "Change Current" and select "BLOG_TEST'

Let's create a region to always display the authentication statuses:
- Create Page
- Page Zero
- On Page 0 Create a region
- Report

SELECT user_name,
authentication_method,
access_date,
authentication_result,
custom_status_text
FROM apex_workspace_access_log
WHERE application_id = :APP_ID
ORDER BY access_date DESC

Now let's try to log in using a valid username and password:
- username: pass, password: 123

If things worked well you're now on P1, and the "AUTHENITCATION_RESULT" is flagged as "AUTH_SUCCESS"



Now logout and try to login with invalid credentials
- username: pass, password: fail

You'll notice that the login failed, however it still logged it as a successful login attempt:



Let's update our authentication function to use the 2 authentication result customizations:
apex_util.set_authentication_result and apex_util.set_custom_auth_status

CREATE OR REPLACE FUNCTION blog_cust_auth (
p_username IN VARCHAR2,
p_password IN VARCHAR2
)
RETURN BOOLEAN
AS
BEGIN
IF LOWER (p_username) = 'pass'
AND LOWER (p_password) = '123' THEN
RETURN TRUE;
ELSE
-- Set APEX authentication Codes
apex_util.set_authentication_result (p_code => 3);
-- Set our own APEX custom text
apex_util.set_custom_auth_status (p_status => 'Bad Username');
RETURN FALSE;
END IF;
END;

Let's now see what happens when we now login with invalid credentials:



Notice that we now have proper error information in the logs? Here's a list of some codes you can use for apex_util.set_authentication_result, taken from the view apex_workspace_access_log:

0, 'AUTH_SUCCESS',
1, 'AUTH_UNKNOWN_USER',
2, 'AUTH_ACCOUNT_LOCKED',
3, 'AUTH_ACCOUNT_EXPIRED',
4, 'AUTH_PASSWORD_INCORRECT',
5, 'AUTH_PASSWORD_FIRST_USE',
6, 'AUTH_ATTEMPTS_EXCEEDED',
7, 'AUTH_INTERNAL_ERROR',

Of course you can use your own codes but I'd suggest sticking with these codes to utilize the APEX views. You can supplement the codes with the custom authentication status text (apex_util.set_custom_auth_status)

10 comments:

  1. Martin - Good to see someone using these features. Thanks.

    ReplyDelete
  2. Hi Scott,

    The logging features are extremely useful when debugging user's login issues. For security reasons we don't like to display to the user that an account doesn't exist etc, so storing it authentication logs helps out a lot.

    Martin

    ReplyDelete
  3. wat if i have made a table for login and apssowrds and i want it to use it for the sake of loginw at i shud ...i m new to this apex envionmetn help me

    ReplyDelete
  4. wat if i have made a table for login and apssowrds and i want it to use it for the sake of loginw at i shud ...i m new to this apex envionmetn help me

    ReplyDelete
  5. Hi!

    Thanks a lot for this! Even two years later it's still valid for Apex 4.x.

    I landed on your page looking for a solution for my problem. I'd like to offer my users a facility to change their password. That actually works fine so far, however, I'd like them to be logged out with a message after the change. I managed to direct them to the login page and invalidate the session, but the thing I don't get is how to send the success message to the login screen. Have you ever tried this? Any hint greatly appreciated :-)

    Cheers!

    André

    ReplyDelete
  6. Hi Andre,

    I think this is possible. Can you please send me an email (my address is at the top right of blog)? Before answering this I have some questions.

    Thanks,

    Martin

    ReplyDelete
  7. Hello Martin,
    I follow your example but I'm using Apex 4.1 and I do not see "Credentials Verification Method" this option.
    I think Apex 4.1 is changing. Please show an example in 4.1

    thanks a lot
    /DJ

    ReplyDelete
  8. Hello,
    I've tried several samples including yours but keep getting errors. e.g
    APEX.AUTHENTICATION.SESSION_VERIFY_FUNCTION ORA-06550: line 4, column 23: PLS-00306: wrong number or types of arguments in call to 'BLOG_CUST_AUTH' ORA-06550: line 4, column 1: PL/SQL: Statement ignored

    ReplyDelete
    Replies
    1. BLOG_CUST_AUTH is a custom function that I used in this blog as an example (see the first code snippet). Did you compile it?

      Delete