Remove the ability of a user to manage passwords and administer the system privilege.
Continuing with the example, if the system privilege is revoked from User1 using the ANY clause, the grant with the target_users_list clause remains in effect. The net effect is that User1 is now limited to managing the passwords of users on the target_users_list. Alternately, if the system privilege is revoked from User1 using the target_users_list clause, the grant with the ANY clause remains in effect. The net effect in this scenario is that User1 can continue to manage the passwords of any user in the database.
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 [,...] |
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 of Jane, Joe, and Laurel only:
REVOKE CHANGE PASSWORD (Jane, Joe, Laurel) FROM Sally, Bob
This statement removes the ability of Mary 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