Restrict Password changes for application users using profile in Oracle database

Article : Restrict Password changes for application users using profile
Applies to: All Oracle database version in any platform
------------------------------------------------------------------

Step:1 : Create function to verify the password.

CREATE OR REPLACE FUNCTION f_restrict_pwd_chg_verify(
    username      VARCHAR2
  , password      VARCHAR2
  , old_password  VARCHAR2) RETURN boolean IS
BEGIN
    raise_application_error(-20009, 'ERROR: Password cannot be changed');
END;
/

Step:2 : Create profile to user above function to restrict password

CREATE or replace PROFILE application_user_profile LIMIT
PASSWORD_VERIFY_FUNCTION f_restrict_pwd_chg_verify
  composite_limit unlimited
  connect_time unlimited
  cpu_per_call unlimited
  cpu_per_session unlimited
  failed_login_attempts unlimited
  idle_time unlimited
  logical_reads_per_call unlimited
  logical_reads_per_session unlimited
  password_grace_time unlimited
  password_life_time unlimited
  password_lock_time unlimited
  password_reuse_max unlimited
  password_reuse_time unlimited
  private_sga unlimited
  sessions_per_user unlimited;

Note: you can set parameters as per your application / user requirement.

Step: 3: Create user with above profile

create user HR
  identified by HR
  default tablespace users
  temporary tablespace TEMP
  profile DEFAULT;
grant CONNECT to HR;
grant resource to HR;
grant UNLIMITED TABLESPACE to HR;
alter user HR profile application_user_profile;

Scenario Testing:
~~~~~~~
Case: 1:
~~~~~~~
$ sqlplus /nolog
SQL> connect HR/HR
Connected.
SQL> alter user HR identified by HR2;
alter user HR identified by HR2
*
ERROR at line 1:
ORA-28221: REPLACE not specified

~~~~~~~
Case: 2:
~~~~~~~

SQL> alter user HR identified by HR2 replace HR;
alter user HR identified by HR2 replace HR
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: ERROR: Password cannot be changed

~~~~~~~
Case: 3:
~~~~~~~
SQL> password
Changing password for HR
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20009: ERROR: Password cannot be changed

Password unchanged

***************************************************************************************************                   
-- Demo-1 to Change password of a schema

sql> connect / as sysdba
sql>alter user HR profile default;
sql>alter user hr identified by hr$55#89ahk;
Password changed.
sql> alter user hr profile  application_user_profile;

-- Demo-2 to Change password of a schema

sql> alter profile application_user_profile limit password_verify_function NULL;
sql> alter user hr identified by hr$55#89ahk;
Password changed.
sql> alter profile application_user_profile limit password_verify_function f_restrict_pwd_chg_verify

Thank, Cheers !!!