Granting the CHANGE PASSWORD System Privilege to a User

Allow a user to manage the password of other users.

Prerequisites

Requires the CHANGE PASSWORD system privilege granted with administrative rights.
Each target user specified (target_users_list) is an existing user or user-extended role with a login password.
Each target role specified (target_roles_list) must be an existing user-extended or user-defined role.

Task

A user can be granted the ability to change the password of any user in the database (ANY) or only specific users (target_users_list) or members of specific roles (ANY WITH ROLES target_roles_list). Administrative rights to the CHANGE PASSWORD system privilege can only be granted when using the ANY clause.

If no clause is specified, ANY, WITH NO ADMIN OPTION is used by default.

When regranting the CHANGE PASSWORD system privilege, the effect of the grant is cumulative. For example, if you grant user1 the privilege limited to user2 and user3, and then regrant the privilege limited to role1, user1 can manage the password of user2, user3, and any member of role1.

If the CHANGE PASSWORD system privilege is regranted to a user with lesser rights than currently granted, the higher rights are retained. For example, if the privilege is granted using the ANY clause and then regranted using the target_users_list clause, the user retains the rights of the ANY clause.

To grant the CHANGE PASSWORD system privilege, execute one of these statements:

Grant TypeUpdated contne	Statement
Any database user, with full administrative rights	GRANT CHANGE PASSWORD (ANY) TO `user_ID` WITH ADMIN OPTION
Any database user, with administrative rights only	GRANT CHANGE PASSWORD (ANY) TO `user_ID` WITH ADMIN ONLY OPTION
Any database user, with no administrative rights	GRANT CHANGE PASSWORD (ANY) TO `user_ID` WITH NO ADMIN OPTION
Specified users, with no administrative rights	GRANT CHANGE PASSWORD (`target_users_list`) TO `user_ID` WITH NO ADMIN OPTION
Any member of specified roles, with no administrative rights	GRANT CHANGE PASSWORD (ANY WITH ROLES `target_roles_list`) TO `user_ID` WITH NO ADMIN OPTION
Specified users, or any member of specified roles, with no administrative rights	GRANT CHANGE PASSWORD (`target_users_list`), (ANY WITH ROLES `target_roles_list`) TO `user_ID` WITH NO ADMIN OPTION

Example:

This statement grants Sam the ability to change the password of any database user:

GRANT CHANGE PASSWORD (ANY) TO Sam
or
GRANT CHANGE PASSWORD TO Sam

This statement grants Sally and Bob the ability to change the password for Jane, Joe, and Laurel only:

GRANT CHANGE PASSWORD (Jane, Joe, Laurel) TO Sally, Bob

This statement grants Mary the ability to change the password of any member of the Sales1 role:

GRANT CHANGE PASSWORD (ANY WITH ROLES Sales1) TO Mary

This statement grants Sarah the ability to change the password of Joe or Sue, or any member of the Sales2 role:

GRANT CHANGE PASSWORD (Joe, Sue), (ANY WITH ROLES Sales2) TO Sarah

This statement grants Joan the ability to change the password of any member of the Marketing1 or Marketing2 roles:

GRANT CHANGE PASSWORD (ANY WITH ROLES Marketing1, Marketing2) TO Joan