Granting a role (SQL)

Grant a role to a user or another role.

Prérequis

You must have administrative rights on the role you are granting.

Contexte et remarques

You cannot grant administrative rights on a system role; only users with the MANAGE ROLES system privilege can administer system roles.

Execute a GRANT ROLE statement. For example:

Option

Statement

Grant a role with no administrative rights.

GRANT ROLE role-name TO userid;

Grant a role with administrative rights.

GRANT ROLE role-name TO userid WITH ADMIN OPTION;

Grant administrative rights only on a role.

GRANT ROLE role-name TO userid WITH ADMIN ONLY OPTION;

Résultat

The role is granted to the user or role.

Exemple

To grant RoleB along with its administrative rights to the user Jane, execute the following statement:

GRANT ROLE RoleB TO Jane WITH ADMIN OPTION

To grant the user John the administrative rights to RoleB, but the inability to use RoleB, execute the following statement:

GRANT ROLE RoleB TO John WITH ADMIN ONLY OPTION