how to change a user password while checking the current password in oracle?

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 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.

StackExchange Database Administrators Q#43086, answer score: 2