Preventing Users From Changing Their Password

by Jeff Hunter, Sr. Database Administrator Script connect sys/change_on_install as sysdba -----

The following function can be used to prevent a user from changing their password. This function requires use of a USER PROFILE to activate the function. This script MUST be run while connected as the SYS user.

set term on set echo on CREATE OR REPLACE FUNCTION verify_function_fix_pwd ( username VARCHAR2 , password VARCHAR2 , old_password VARCHAR2) RETURN boolean IS BEGIN raise_application_error(-20009, 'ERROR: Password cannot be changed'); END; / show errors -- The following profile will contain a limit PASSWORD_VERIFY_FUNCTION -- associated to the previously created function. -- This profile must be assigned to all users who are NOT allowed to -- change their password. DROP PROFILE fixpwd CASCADE; CREATE PROFILE fixpwd LIMIT PASSWORD_VERIFY_FUNCTION verify_function_fix_pwd;

Example Run DROP USER test_pwd; CREATE USER test_pwd IDENTIFIED BY test_pwd DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp ACCOUNT UNLOCK; GRANT connect TO test_pwd; ALTER USER test_pwd PROFILE fixpwd; SQL> connect test_pwd/test_pwd Connected. -----

Notice that in the following example, we need to provide the REPLACE keyword in 9.2.x to prevent getting ORA-28221. This is required when users to not have the ALTER USER system privilege.

SQL> ALTER USER test_pwd IDENTIFIED BY test REPLACE test_pwd; ALTER USER test_pwd IDENTIFIED BY test REPLACE test_pwd

* ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20009: ERROR: Password cannot be changed SQL> password Changing password for TEST_PWD 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