8 Replies Latest reply: Nov 17, 2010 1:00 PM by William Robertson RSS

    PL/SQL Function to check user name and password

    701428
      Hi,
      I am new to PL/SQL. I have a table that stores user name and password. I want to write a PL/SQL function that checks if the entered user name and password is correct, by validation against the values in the table... Can someone help me with the code? Both user name and password is varchar2
        • 1. Re: PL/SQL Function to check user name and password
          JustinCave
          1) Is this a real system? Or a homework problem? You would never store the password in clear text in a real system-- you would store a password hash.
          2) What is the function supposed to return? A BOOLEAN? A string ('Y' for valid, 'N' for not valid)? A number (1 for valid, 0 for not valid)? Something else?

          Justin
          • 2. Re: PL/SQL Function to check user name and password
            701428
            Hi Justin,
            It is a real system.
            The function should return some error message, "Password Incorrect", when the password doesnt match with the password in the table.
            • 3. Re: PL/SQL Function to check user name and password
              BEDE
              First of all, I believe you mean a procedure that would check the application username and password. Fot, if you mean a PL/SQL procedure, that runs in the database, and that means it has to be executed after the client application is connected to the database.

              Maybe you need a procedure like this:

              procedure check_pass(p_user varchar2, p_pass varchar2) is
              pragma autonomous_transaction;
              v_passwd varchar2(200);
              begin
              select passwd into v_passwd
              from password_table
              where user_name=p_user;
              if v_passwd!=p_pass then
              update password_table set
              last_unsucc_logon=sysdate
              ,unsucc_logon_n=nvl(unsucc_logon_n,0)+1
              where user_name=p_user;
              commit;
              raise_application_error(-20101,'Invalid username/password!');
              end if;
              exception
              when no_data_found then
              raise_application_error(-20101,'Invalid username/password!');
              end;

              Study this piece of code and see if this will do.
              • 4. Re: PL/SQL Function to check user name and password
                BEDE
                Still, I believe you should not store the password in clear text, but rather something obtained using dbms_utility.get_hash_value for instance. RTM on this.
                • 5. Re: PL/SQL Function to check user name and password
                  JustinCave
                  OK. If it's a real system, then the password should not be stored in clear text. You would only want to store a password hash

                  There is a good thread on AskTom that discusses this issue. It also includes a couple different implementations of the function you're looking for to verify passwords.

                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:95412348059

                  Justin
                  • 6. Re: PL/SQL Function to check user name and password
                    701428
                    Hi BEDE and Justin,
                    Thanks for the help....
                    I want to use this piece of PL/SQL code in APEX application.

                    Is the following code correct?

                    create or replace function return my_custom_auth (p_username IN VARCHAR2, p_password IN VARCHAR2)
                    return boolean
                    as
                    p_check varchar2(10);
                    BEGIN
                    select password into p_check from passwords where upper(user_name) = p_username;
                    if p_check = p_password then
                    RETURN true;
                    else
                    RETURN false;
                    end if;
                    EXCEPTION WHEN OTHERS THEN RETURN false;
                    End;

                    My Table name is : passwords
                    Columns: user_name and password

                    The field name where the validation needs to be done is :p101_username and :p101_password
                    • 7. Re: PL/SQL Function to check user name and password
                      JustinCave
                      Storing passwords in clear text is a major security issue, so from that standpoint, no that is not correct. It would raise all sorts of red flags to store passwords in clear text even in a toy internal application because a reasonable fraction of users are going to use the same password for multiple systems. Even if your system has no interesting data to compromise, if the VP happens to use the same password he used for the general ledger system, you've just opened a giant security hole.

                      From a purely functional standpoint, however, yes, that probably does what you want. I would strongly advise you to change the requirements, but this does what you want.

                      Justin
                      • 8. Re: PL/SQL Function to check user name and password
                        William Robertson
                        You could simplify it a little by having the query search for the specified username/password combination, then trapping NO_DATA_FOUND exceptions. I agree with others though, if the passwords are going to be unencrypted and unhashed you might as well not use passwords at all.

                        btw I would keep the 'p_' prefix for parameter values, and use something else (e.g. 'v_') for variables.