Fun with password verification

Prompted by a question in the OTN forums I have been having a little play around with the Password Verification functionality. It's quite easy: we just create a function under SYS and add it to the users' profile(s). Oracle provide a sample function that applies some standard rules.

SQL> @C:\oracle\ora92\rdbms\admin\utlpwdmg.sql

Function created.


Profile altered.

SQL> alter user a identified by a
  2  /
alter user a identified by a
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user


SQL> alter user a identified by b
  2  /
alter user a identified by b
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4


SQL> alter user a identified by abcd
  2  /
alter user a identified by abcd
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password too simple


SQL> alter user a identified by a1b2
  2  /
alter user a identified by a1b2
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one character and one punctuation


SQL> alter user a identified by a1b_
  2  /

User altered.

SQL> alter user a identified by a1b_
  2  /
alter user a identified by a1b_
*
ERROR at line 1:
ORA-28007: the password cannot be reused


SQL> 

So that's quite nice. But wait a minute, what about this:

SQL> alter user a identified by b1b_
  2  /

User altered.

SQL> 

Isn't there supposed to be some similarity checking? It is definitely there in the utlpwdmg.sql script. So why isn't it working? Turns out it does work but only when the user changes their own password. By the way, notice the REPLACE syntax we need now that we have enabled the PASSWORD_VERIFY_FUNCTION.

SQL> conn a/b1b_
Connected.
SQL> alter user a identified by b2b_ replace b1b_
  2  /
alter user a identified by b2b_ replace b1b_
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20004: Password should differ by at least 3 characters


SQL> 

This part of the checking doesn't work for SYS or any other user with the ALTER USER privilege. There's some helpful in Metalink note #114930.1 which explains Oracle's password management policy. Amongst other things the note says that:

"Oracle recommends that you do not change passwords using the ALTER USER statement because it does not fully support the password verification function."

Apparently we should use the SQL*Plus PASSWORD command or make explicit calls to OCIPasswordChange() to change passwords instead. But apart from the fact that SYS can bypass some of the similarity checking I'm not sure what the differences are. For normal users I think ALTER USER with the REPLACE clause remains a valid option (although using SQL*Plus PASSWORD is probably friendlier).