Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command:

alter user user_name identified by new_password;

My problem is that I don't know how to check whether the current password which the user has been entered in the form is correct or not ( as the passwords are encrypted in oracle)

How should I do this?

share|improve this question
    
I'd change your application logic. Create the users not on db level but in a table. And handle permissions yourself using again a table. Instead of granting them on db level. –  Munchi May 26 '13 at 16:27
1  
According to security it is better to handle users using database users. –  rahim asgari May 26 '13 at 17:11
    
Weird though :) Wouldn't know an environment where users would need the ability to do that. The database being the backend and all. –  Munchi May 27 '13 at 6:21

3 Answers 3

up vote 1 down vote accepted

I would give the Database Link primitive a shot. Though it would involve some DDL. For example, you could create a private fixed user link as follows:

create database link check_subm_credentials
  connect to scott
  identified by password using 'service_name';

In your PL/SQL code it might look something along the lines of:

EXECUTE IMMEDIATE 'create database link check_subm_credentials'
                  || ' connect to '    || submitted_user_name
                  || ' identified by ' || submitted_password
                  || ' using ''orcl''';

Since ALTER DATABASE LINK has some limitations:

You cannot use this statement to change the connection or authentication user associated with the database link.

You must re-create the database link to use different connection user. You would just issue DROP DATABASE LINK every time before you re-create the database link. You might also need to close the database link with CLOSE DATABASE LINK clause before you can drop it:

alter session close database link check_subm_credentials;

Once the database link is created, you could query some USER_* view or DUAL table to check if the user submitted correct credentials:

select * from dual@check_subm_credentials;

If the query returned some result then the connection was established successfully and the credentials provided by the user are valid. Otherwise Oracle will raise an exception.

Remember that the statements to create and drop the database links is DDL, and DDL statements implicitly commit current transaction, therefore all the changes you made before DDL will become permanent, and you will be unable to ROLLBACK them after DDL is executed.

share|improve this answer

How about applying your own salt and hash to the password that the use supplies when they login successfully, and storing it in a variable in case they change their password later? Don't forget to update it to the new one if they change it ;)

share|improve this answer
    
How should i do this? –  rahim asgari May 28 '13 at 7:07
    
Using the same technology stack as that which presents the user with the option to change the password, whatever that is. Java? Rails? –  David Aldridge May 28 '13 at 7:13

11g and higher accept this syntax: ALTER USER username IDENTIFIED BY new_password REPLACE old_password;

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.