Monday, August 23, 2010

How to setup Custom Authentication in Oracle APEX

Hi,
Recently on ODTUG mailing list one fellow asked about using custom authentication schemes in APEX. i thought it would be good idea for my blog post :)

In this video i will demonstrate how to create login page which authenticates username and passwords from database tables in Oracle APEX.

Note: The purpose of this post is just to show the setting up custom authentication therefore i have used a very simple db table with passwords stored as text.
In real world this password approach in not recommended you must store passwords in encrypted columns or use any procedure to convert passwords into encrypted form.

Database structure

 create table my_users
(user_name varchar2(10),
 user_pwd varchar2(10));

insert into my_users
values('Tom','Secret');

insert into my_users
values('Baig','oracle');

commit;

Authentication Function

create or replace function 
validate_user_from_db (p_username in varchar2, p_password in varchar2)
return boolean
as
    v_pw_check varchar2(1);
begin
    select 'x'
    into v_pw_check
    from my_users
    where upper(user_name) = upper(p_username)
    and user_pwd = p_password;
    
    apex_util.set_authentication_result(0);
    return true;
exception when no_data_found then
    apex_util.set_authentication_result(4);
    return false;
end validate_user_from_db;


Authentication messages

0: Normal, successful authentication
1: Unknown User Name
2: Account Locked
3: Account Expired
4: Incorrect Password
5: Password First Use
6: Maximum Login Attempts Exceeded
7: Unknown Internal Error

A little code to test our authentication function( This function for only test in SQL prompt)

declare
 vresult varchar2(10);
begin 
 if validate_user_from_db ('baig','oracle') then 
    dbms_output.put_line('OK');
 else
     dbms_output.put_line('ERROR');
 end if;
end;


And finally a video tutorial steps



Happy learning,
Zeeshan Baig

26 comments:

  1. You have a SERIOUS flaw in your functionality. YOU NEVER store a password in plain text, this is a BIG security no-no..... Also, if this is to be used in APEX, DBMS_OUTPUT is useless, you need to use a htp.p function call...

    How much development HAVE you done in APEX again??

    ReplyDelete
  2. Hi APEX developer,

    Thanks for comments. i have posted a update note on this blog post.

    "Note: The purpose of this post is just to show the setting up custom authentication therefore i have used a very simple db table with passwords stored as text.

    In real world this password approach in not recommended you must store passwords in encrypted columns or use any procedure to convert passwords into encrypted form."

    ReplyDelete
  3. I have followed this tutorial all the way through with a couple of changes for password encryption. I keep getting the error "PLS-00201: identifier 'APEX_UTIL.SET_AUTHENTICATION_RESULT' must be declared"

    Can you help?

    ReplyDelete
  4. Great! Many thanks, it was that I need.

    ReplyDelete
  5. and how to do that with encripted text?
    thanks

    ReplyDelete
    Replies
    1. Hi,

      There are many ways check the following links

      http://www.oracle-base.com/articles/9i/storing-passwords-in-the-database-9i.php

      http://www.oracle-base.com/articles/10g/transparent-data-encryption-10gr2.php

      http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_crypto.htm

      Hope it helps,
      ZB

      Delete
  6. ORA-06550: line 4, column 5:
    PLS-00905: object GB_7133_S11_PLSQL.VALIDATE_USER_FROM_DB is invalid
    ORA-06550: line 4, column 2:
    PL/SQL: Statement ignored
    2. vresult varchar2(10);
    3. begin
    4. if validate_user_from_db ('baig','oracle') then
    5. dbms_output.put_line('OK');
    6. else

    i got this error

    ReplyDelete
    Replies
    1. Please check that procedure is proper compiled.

      Z

      Delete
  7. this is the function i am using
    create or replace function
    validate_user_from_db(p_username in varchar2,p_password in varchar2)
    return boolean
    is
    v_pw_check varchar2(1);
    begin
    select name
    into v_pw_check
    from customers
    where upper(NAME)=upper(p_username)
    and PASSWORD=p_password;

    apex_util.set_authentication_result(0);
    return true;
    exception when no_data_found then
    apex_util.set_authentication_result(4);
    return false;
    end validate_user_from_db;
    --
    when I executed that function it shows function processed ..
    --
    when i run below code ..
    declare vresult varchar2(10);
    begin
    if validate_user_from_db ('Robin','vbc')
    then dbms_output.put_line('ok');
    else
    dbms_output.put_line('ERROR');
    end if;
    end;
    --
    comes up with this error

    ORA-06550: line 4, column 5:
    PLS-00905: object GB_7133_S11_PLSQL.VALIDATE_USER_FROM_DB is invalid
    ORA-06550: line 4, column 2:
    PL/SQL: Statement ignored
    2. vresult varchar2(10);
    3. begin
    4. if validate_user_from_db ('baig','oracle') then
    5. dbms_output.put_line('OK');
    6. else
    --
    here object is workspace name how to rectify this..

    ReplyDelete
    Replies
    1. Hi,

      Your earlier issue was PLS error now you said it says Function processed...So what is actual issue ? What is displays when you execute it? Have to tried to execute in SQL developer ?

      Zeeshan

      Delete
    2. HI
      i faced the same problem......but fi u run ur function creation code in SQL PLUS , u will not face this problem

      Delete
  8. i tried to execute below code in sql developer
    declare vresult varchar2(10);
    begin
    if validate_user_from_db ('Robin','vbc')
    then dbms_output.put_line('ok');
    else
    dbms_output.put_line('ERROR');
    end if;
    end;
    but raises that error object invalid..

    ReplyDelete
    Replies
    1. Make sure you have execution rights on the function. try creating the same function in a test schema and execute from same schema.

      Z

      Delete
  9. Hi
    actually I am working on sql server oracle is new to me
    how to create test schema ..

    ReplyDelete
    Replies
    1. This code is related to Oracle APEX. Please follow the documentation. I have provided enough details and a video how to do it.

      Z

      Delete
  10. yes I followed each and every step of yours .. when I login with that user id 'robin' and pw 'vbc'
    is showing
    PLS-00905: object GB_7133_S11_PLSQL.VALIDATE_USER_FROM_DB is invalid

    above error
    what it means
    thanks

    ReplyDelete
    Replies
    1. Do you have permission to access APEX_UTIL user ? are you working in Oracle APEX application or something else ?

      if you don't have permission then grant permissions on set_authentication_result package to your user.



      Delete
  11. thanks for this blog, its usefull for me but;

    when i try to login get this error:

    "ORA-44004: invalid qualified SQL name"

    ReplyDelete
    Replies
    1. Hi check this thread for solution..

      https://forums.oracle.com/forums/thread.jspa?threadID=2284388&start=15&tstart=0

      "Give your packagename.function name in your custom authentication function like the below and check,

      return final_users_security.valid_user

      "

      Hope it helps,
      Z

      Delete
  12. It is very useful and simple explanation:) Thanks!

    ReplyDelete
  13. I tried several times, and it gave me the following two errors:

    1. ORA-06550: line 4, column 23: PLS-00306: wrong number or types of arguments in call to 'IS_AUTHENTIC_USER2' ORA-06550: line 4, column 1: PL/SQL: Statement ignored.
    2. Invalid Login Credentials

    Finally I got it worked and noted some interesting things:

    1. The program unit must be a function returning a BOOLEAN value. It can be a packaged function as well.

    2. You can name the function anything, however the names of the parameters should be: "p_username", and "p_password" and nothing else.
    It should NOT be "i_username" "i_user_name" or anything in those lines as well.

    3. More intersting thing is the value of the parameter "p_username" that was sent from the UI is converted to UPPER before it is passed the procedure.

    4. However the password remains how it is typed in the UI. Automatic case conversion did not happen with the password.

    5. It worked when I create a packaged function owned by the schema associated with the work space.

    6. It also works if the package is owned by a different user (schema) than the schema associated with the work space. I did not create any private synonym.

    In my case say
    sch1 schema is associated with the work space ws1.

    I have created the package in sch2 and granted execute permission on sch2.sec_pkg to sch1

    While creating the Authentication schema (custom), in the "Authentication function name", I have provided "sch2.sec_pkg.is_authentic_user", and that worked.

    I hope this will help few others who have this issue.

    ReplyDelete
    Replies
    1. Thanks..if you followed the blog example and oracle docs it should be good.

      Zeeshan

      Delete
    2. That was a good one. I was browsing for this login credential. Thanks

      Delete
  14. Hi, Working on Apex 5.0.

    Created username and password .but i am not able to login.

    This is the query i used
    Function mp_apex1(p_username IN VARCHAR2,p_passwordIN VARCHAR2)
    RETURN BOOLEAN
    AS
    APEX1 NEMBER:=0;
    BEGIN
    SELECT 1 INTO APEX1 FROM LOGIN
    WHERE UPPER(username)=UPPER(p_username)
    AND password=p_password
    AND status=:P101_Status
    AND mp='APEX1';
    RETURN TRUE;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
    END MP_APEX1;

    ReplyDelete
    Replies
    1. Hi...did you test the function in SQL Developer ? make sure the data exists what you are trying to authenticate.

      Zeeshan

      Delete