Revoking the CHANGE PASSWORD System Privilege from a User

Remove the ability of a user to manage passwords and administer the system privilege.

Prerequisites

Requires the CHANGE PASSWORD system privilege granted with administrative rights.

Task

The CHANGE PASSWORD system privilege can be granted to a user multiple times, using different clauses. For example, UserA is granted the CHANGE PASSWORD system privilege once using the ANY clause and again with the target_users_list clause. In cases of multiple grants, the form of the clause used for the GRANT must be used to revoke it. Continuing with the example, if the system privilege is revoked from UserA using the ANY clause, the grant with the target_users_list clause remains in effect. The net effect is that UserA is now limited to managing the passwords of users on the target_users_list. Alternately, if the system privilege is revoked from UserA using the target_users_list clause, the grant with the ANY clause remains in effect. The net effect in this scenario is that UserA can continue to manage the passwords of any user in the database.

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

Revoke Type	Description
Administrative rights to system privilege only	REVOKE ADMIN OPTION FOR CHANGE PASSWORD ( ANY ) FROM `user_ID [,...]`
System privilege to manage password of any database user, including administrative rights	REVOKE CHANGE PASSWORD FROM `user_ID [,...]`
System privilege to manage password of specified users	REVOKE CHANGE PASSWORD ( `target_users_list` ) FROM `user_ID [,...]`
System privilege to manage password of specified roles	REVOKE CHANGE PASSWORD ( ANY WITH ROLES `target_roles_list` ) FROM `user_ID [,...]`

Example:

Both these statements remove the ability of Sam to change the password of any database user:

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

Assuming that Frank was granted the CHANGE PASSWORD system privilege with the ANY and WITH ADMIN OPTION clauses, this statement removes only the ability to administer the system privilege from Frank. He can continue to change the password of any user in the database.

REVOKE ADMIN OPTION FOR CHANGE PASSWORD (ANY) FROM Frank

This statement removes the ability of Sally and Bob to change the password ofJane, Joe, and Laurel only:

REVOKE CHANGE PASSWORD (Jane, Joe, Laurel) FROM Sally, Bob

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

REVOKE CHANGE PASSWORD (ANY WITH ROLES Sales1) FROM Mary

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

REVOKE CHANGE PASSWORD (Joe, Sue), (ANY WITH ROLES Sales2) FROM Sarah

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

REVOKE CHANGE PASSWORD (ANY WITH ROLES Marketing1, Markeing2) FROM Joan