password_verify_function

The Password_Verify_function will verify the password has been used ever. If it is enabled then we can not reuse the same password for alerting a user. There may be some situations still we need to reset the password to same password i.e older password. Here we go as below.
Since Oracle 11g has introduced new Password policies like PASSWORD_LIFE_TIME=180 days,PASSWORD_VERIFY_FUNCTION= VERIFY_FUNCTION_11G which was not there in Oracle 10g.

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty.


1) Identify the PROFILE assigned to the user you want to change the password.

select username,password,account_status,profile from dba_users where USERNAME='&USERNAME';

2) select * from dba_profiles where PROFILE_NAME='Profile name retrieved from step 1'; Identify the values assigned to resource name PASSWORD_VERIFY_FUNCTION

3) alter the profile with Password_Verify_function as null;

alter profile PROFILE_NAME limit password_verify_function null;

4) Now change the password.

alter user username identified by password; //If we know the old password

alter user username identified by values '******'; //If we don't know the password please use the below query or use the values found from step 1

e.g.

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' Query
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: remote_dba
old   3: where d.username = upper('&&username')
new   3: where d.username = upper('remote_dba')

Query
--------------------------------------------------------------------------------
alter user "REMOTE_DBA" identified by values 'F894844C34402B67';

SQL> alter user "REMOTE_DBA" identified by values 'F894844C34402B67'


5) change password_verify_function to older value.

ALTER PROFILE profile_name LIMIT PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT} //Found in step 2.