This content has been marked as final. Show 8 replies
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?
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
select passwd into v_passwd
if v_passwd!=p_pass then
update password_table set
when no_data_found then
Study this piece of code and see if this will do.
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.
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)
select password into p_check from passwords where upper(user_name) = p_username;
if p_check = p_password then
EXCEPTION WHEN OTHERS THEN RETURN false;
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
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.
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.