GRANT <roles>

The GRANT <roles> statement lets you add a role or user as a member to a role.

Note:
GRANT <roles> is an enterprise-only feature.

Synopsis

GRANTrole_name,TOuser_name,WITHADMINOPTION

Required privileges

The user granting role membership must be a role admin (i.e., members with the ADMIN OPTION) or a superuser (i.e., a member of the admin role).

Considerations

  • Users and roles can be members of roles.
  • The root user is automatically created as an admin role and assigned the ALL privilege for new databases.
  • All privileges of a role are inherited by all its members.
  • Membership loops are not allowed (direct: A is a member of B is a member of A or indirect: A is a member of B is a member of C … is a member of A).

Parameters

ParameterDescription
role_nameThe name of the role to which you want to add members. To add members to multiple roles, use a comma-separated list of role names.
user_nameThe name of the user or role to whom you want to grant membership. To add multiple members, use a comma-separated list of user and/or role names.
WITH ADMIN OPTIONDesignate the user as an role admin. Role admins can grant or revoke membership for the specified role.

Examples

Grant role membership

  1. > GRANT design TO ernie;
  1. > SHOW GRANTS ON ROLE design;
  1. +--------+---------+---------+
  2. |  role  | member  | isAdmin |
  3. +--------+---------+---------+
  4. | design | barkley | false   |
  5. | design | ernie   | false   |
  6. | design | lola    | false   |
  7. | design | lucky   | false   |
  8. +--------+---------+---------+

Grant the admin option

  1. > GRANT design TO ERNIE WITH ADMIN OPTION;
  1. > SHOW GRANTS ON ROLE design;
  1. +--------+---------+---------+
  2. |  role  | member  | isAdmin |
  3. +--------+---------+---------+
  4. | design | barkley | false   |
  5. | design | ernie   | true    |
  6. | design | lola    | false   |
  7. | design | lucky   | false   |
  8. +--------+---------+---------+

See also

Was this page helpful?
YesNo