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?

  • 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, 2013 at 16:27
  • 2
    According to security it is better to handle users using database users. May 26, 2013 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, 2013 at 6:21

3 Answers 3


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.


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

  • note: with 11g profiles, this REPLACE clause might be mandatory. i.e. nobody(having the profile) can change it's password without entering the old one
    – ibre5041
    Feb 17, 2015 at 20:11

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 ;)

  • How should i do this? May 28, 2013 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? May 28, 2013 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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