Require old password when setting a new password to a specific user in Oracle

Question

Does Oracle 12 support having the old password required when changing a password to a specific user?

What I would like:

ALTER USER user_a IDENTIFIED BY secret123;
-- ERROR, missing old password

ALTER USER user_a IDENTIFIED BY secret456 REPLACE secret123;
-- OK

ALTER USER user_b IDENTIFIED BY secret789;
-- OK, since user_b does not require old password when changing it

Thanks!

Answer 1

Yes, this is supported since Oracle 9i, when a function was introduced that checks a new password for complexity and optionally for difference to the old password. As Oracle stores only hashes, not the passwords, it cannot compare old and new passwords unless the user supplies it during the change.

So, all users with a PROFILE where the PASSWORD_VERIFY_FUNCTION is set are required to have the old password, even if this function doesn't check any passwords:

CREATE OR REPLACE FUNCTION always_true (
  username     VARCHAR2, 
  password     VARCHAR2, 
  old_password VARCHAR2) RETURN boolean IS
BEGIN
  RETURN TRUE;
END always_true;
/

CREATE PROFILE always_true 
  LIMIT PASSWORD_VERIFY_FUNCTION always_true;

CREATE USER user_a IDENTIFIED BY secret123 PROFILE always_true;
GRANT CREATE SESSION to user_a;

Now user_a has to specify the old password:

ALTER USER user_a IDENTIFIED BY secret123;
ORA-28221: REPLACE not specified

ALTER USER user_a IDENTIFIED BY secret456 REPLACE secret123;
User altered.

A user with a profile without PASSWORD_VERIFY_FUNCTION or this parameter set to NULL doesn't have to specify the old password:

CREATE PROFILE without_function 
  LIMIT PASSWORD_VERIFY_FUNCTION NULL;

CREATE USER user_b IDENTIFIED BY secret123 PROFILE without_function;
GRANT CREATE SESSION to user_b;

Now user_b can change his/her password without having the old password:

ALTER USER user_b IDENTIFIED BY secret789;
User altered.

The second option is to have the privilege ALTER USER, but that is only for administrators, as they can change all the passwords of all account.