This discussion is archived
3 Replies Latest reply: Jan 17, 2013 12:05 AM by 983316 RSS

built-in users table

983316 Newbie
Currently Being Moderated
Hello,

I want to build an authentication function based on the built-in users table, some thing like:
(select * from (appex-user) where user=&varialble and password=&varialbes)

How can I do that?

Edited by: Najla on Jan 16, 2013 8:34 AM
  • 1. Re: built-in users table
    Ahmed Alsaied Newbie
    Currently Being Moderated
    Hi,
    you can build table like
    CREATE TABLE  "USER_LOGIN" 
       (     "USER_NAME" VARCHAR2(50) NOT NULL ENABLE, 
         "USER_PASS" VARCHAR2(50))
    and use function like this
    create or replace FUNCTION  "AACU_F" (
    p_username IN VARCHAR2,
    p_password IN VARCHAR2)
    RETURN BOOLEAN IS
    BEGIN
    FOR c1 IN (SELECT 1
    FROM USER_LOGIN
    WHERE UPPER(USER_NAME) = UPPER(p_username)
    AND UPPER(USER_PASS) = UPPER(p_password))    
    LOOP
    RETURN TRUE;
    END LOOP;
    RETURN FALSE;
    END;‚Äč
    now change your Authentication Schemes
    Scheme Type : custom
    in Authentication Function Name : use the function name you created (AACU_F) .

    Abo Yahya
  • 2. Re: built-in users table
    Justin Cave Oracle ACE
    Currently Being Moderated
    Which "built-in users" table are you referring to?

    If you want to create your own table with your own usernames and passwords, Abo showed how to do that above (though you would really, really, really) want to store a password hash rather than storing the actual password and you would want that hash to include some randomly generated salt that would also be stored in the table.

    But if you are talking about leveraging either Oracle's built-in table or APEX's built in table of users, APEX provides built-in authentication methods for APEX accounts and for Oracle accounts that you can simply choose. There is no need to write any code to take advantage of these options.

    Justin
  • 3. Re: built-in users table
    983316 Newbie
    Currently Being Moderated
    Thank you Abo Yahya, Justin... your answers were very helpful.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points