Preventing Users From Changing Their Password

The following article provides code that enables a DBA to prevent certain users to change their password. This can be useful in environments where many users log on to the database with a single account. In situations like this, it is very important to guarantee that no user can change the password, thus preventing other users of this common account to login.

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