Oracle: “ORA-28221: REPLACE not specified” on password-change

If we apply a PASSWORD_VERIFY_FUNCTION to a user’s profile, we would need a little more syntax than usual to change the users’s password. A simple “ALTER USER username IDENTIFIED BY password;” for most users would not work:

SQL> conn test/test123@testdb
Connected.

SQL> alter user test identified by QwertzQwertz2014;
alter user test identified by QwertzQwertz2014
*
ERROR at line 1:
ORA-28221: REPLACE not specified

We have to add a “REPLACE old_password” to our command:

SQL> alter user test identified by QwertzQwertz2014 replace test123;

User altered.

SQL>

This “REPLACE” can only be omitted, if the executing user has the “ALTER USER” system privilege or if a PASSWORD_VERIFY_FUNCTION is not in use. In the presence of a PASSWORD_VERIFY_FUNCTION the REPLACE can only be omitted if the user changes his/her password for the first time.

This is important to keep in mind for cases when we want the password to be set via an application. Here the application’s logic must be able to handle the above.

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF53632

Edit 2014-08-27:
It took some time for me to grasp the logic behind this syntax – but now I think I got it:

As the users passwords are always stored encrypted / hashed in the database, there would be otherwise no chance for a PASSWORD_VERIFY_FUNCTION to check the new password for a minimum number of changed characters compared to the old password.

If the new password would differ in at least one single character, the hash-value of that password would be completely different to the hash of the old password – so no use for the aforementioned check. That way we could just check for “NEW_PWD != OLD_PWD”. Since there is no profile limit for a minimum number of changed password-characters, the only way to check this is in a PASSWORD_VERIFY_FUNCTION – and hence it makes sense to just request the old password ( or request the old password at all ) in case we use a PASSWORD_VERIFY_FUNCTION.

Just verified this on My Oracle Support (MOS) and found it confirmed under
“Password Verify Function Not Enforcing Difference Between Old and New Passwords” (Doc ID 816932.1)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: