GRANT CHANGE PASSWORD Statement

A user can be granted the ability to mange 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.

Syntax

GRANT CHANGE PASSWORD ( target_user_list | ANY | ANY WITH ROLES target_role_list  )
TO userID [,...] 
[ WITH ADMIN [ONLY] OPTION | WITH NO ADMIN OPTION]

Parameters

target_user_list – users the grantee has the potential to impersonate. The list must consist of existing users or user-extended roles with login passwords. Separate the userIDs in the list with commas.
ANY – all database users with login passwords become potential target users to manage passwords for each grantee.
ANY WITH ROLES target_role_list – list of target roles for each grantee. Any users who are granted any of the target roles become potential target users for each grantee. The target_role_list must consist of existing roles and the users who are granted said roles must consist of database users with login passwords. Use commas to separate multiple userIDs.
userID – must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.
WITH ADMIN OPTION – (valid with the ANY clause only) The user can both manage passwords and grant the CHANGE PASSWORD system privilege to another user.
WITH ADMIN ONLY OPTION – (valid with the ANY clause only) The user can grant the CHANGE PASSWORD system privilege to another user, but cannot manage passwords of other users.
WITH NO ADMIN OPTION – the user can manage passwords, but cannot grant the CHANGE PASSWORD system privilege to another user.

Examples

Example 1 – grants Sally and Laurel the ability to mange the password of Bob, Sam, and Peter.
```
GRANT CHANGE PASSWORD (Bob, Sam, Peter) TO (Sally, Laurel)
```
Example 2 – grants Mary the right to grant the CHANGE PASSWORD system privilege to any user in the database. However, since the system privilege is granted with the WITH ADMIN ONLY OPTION clause, Mary cannot manage the password of any other user.
```
GRANT CHANGE PASSWORD (ANY) TO Mary WITH ADMIN ONLY OPTION
```
Example 3 – grants Steve and Joe the ability to manage the password of any member of Role1 or Role2.
```
GRANT CHANGE PASSWORD (ANY WITH ROLES Role1, Role2) TO Steve, Joe
```

Usage

If no clause is specified, ANY is used by default.

If no administrative clause is specified in the grant statement, the WITH NO ADMIN OPTION clause is used.

By default, the CHANGE PASSWORD system privilege is granted to the SYS_AUTH_SA_ROLE compatibility role with the WITH NO ADMIN OPTION clause and to the SYS_AUTH_SSO_ROLE compatibility role with the ADMIN ONLY OPTION clause, if they exist.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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.