GRANT ROLE statement

Grant roles to users and roles.

Syntax

Grant system roles

GRANT ROLE system-role
TO grantee [ ,... ]

system-role : 
dbo
| DIAGNOSTICS
| PUBLIC
| rs_systabgroup
| SA_DEBUG
| SYS
| SYS_REPLICATION_ADMIN_ROLE
| SYS_RUN_REPLICATION_ROLE
| SYS_SAMONITOR_ADMIN_ROLE
| SYS_SPATIAL_ADMIN_ROLE

Grant user-defined roles

GRANT ROLE user-defined-role [,...] 
TO grantee [ ,... ]
[ { WITH NO ADMIN | WITH ADMIN [ ONLY ] } OPTION ]

Grant compatibility roles

GRANT ROLE compatibility-role-name [,...]
TO grantee [ ,... ]
[ { WITH NO ADMIN | WITH ADMIN [ ONLY ] } OPTION ]
[ WITH NO SYSTEM PRIVILEGE INHERITANCE ]

compatibility-role-name : 
SYS_AUTH_BACKUP_ROLE
| SYS_AUTH_DBA_ROLE
| SYS_AUTH_PROFILE_ROLE
| SYS_AUTH_READCLIENTFILE_ROLE
| SYS_AUTH_READFILE_ROLE
| SYS_AUTH_RESOURCE_ROLE
| SYS_AUTH_SA_ROLE
| SYS_AUTH_SSO_ROLE
| SYS_AUTH_VALIDATE_ROLE
| SYS_AUTH_WRITECLIENTFILE_ROLE
| SYS_AUTH_WRITEFILE_ROLE

Parameters

role-name
The name of a system role, compatibility role, user-extended role, or user-defined role.
grantee
The user ID of a user, or the name of a role. You cannot grant roles to compatibility roles. You can grant roles to any system role; however, only the following system roles support logins: PUBLIC, dbo, diagnostics, rs_systabgroup, and SA_DEBUG
WITH [NO] ADMIN OPTION clause
This clause is only applicable when granting non-system roles. You cannot grant administrative rights on a system role; only users with the MANAGE ROLES system privilege can administer (grant and revoke) system roles.

The default is WITH NO ADMIN OPTION, meaning that the grantee is given the role, but not the ability to administer it.

If WITH ADMIN OPTION is specified, each grantee is given administrative rights over each role-granted.

If WITH ADMIN ONLY OPTION is specified, then the grantee is given only administrative rights over the role, but is not given the role itself. You can never use the WITH NO SYSTEM PRIVILEGE INHERITANCE clause with the WITH ADMIN ONLY OPTION.

You can only use the WITH ADMIN OPTION clause with the WITH NO SYSTEM PRIVILEGE INHERITANCE clause when granting SYS_AUTH_DBA_ROLE.
WITH NO SYSTEM PRIVILEGE INHERITANCE
This clause prevents the grantees of a role from inheriting the role's system privileges. Normally, when you grant a compatibility role to a user or role, the compatibility role's system privileges are available to both the role and its grantees. When you disable the inheritance of a compatibility role's system privileges, the system privileges are available only to the role, not to its grantees.

The WITH NO SYSTEM PRIVILEGE INHERITANCE clause is provided for backwards compatibility. Disabling system privilege inheritance for a compatibility role mimics the behavior of the non-inheritable authority in version 12 and earlier databases. Enabling system privilege inheritance for a compatibility role mimics the behavior of all system roles and user-defined roles.

You can disable the inheritance of the system privileges when granting one of the following roles to users, user-extended roles, or system roles:
- SYS_AUTH_DBA_ROLE role
- SYS_AUTH_RESOURCE_ROLE
- SYS_AUTH_BACKUP_ROLE
- SYS_AUTH_VALIDATE_ROLE
- SYS_RUN_REPLICATION_ROLE
Also, you can only use the WITH ADMIN OPTION clause with the WITH NO SYSTEM PRIVILEGE INHERITANCE clause when granting SYS_AUTH_DBA_ROLE. You can never use the WITH NO SYSTEM PRIVILEGE INHERITANCE clause with the WITH ADMIN ONLY OPTION.

Disabling the system privilege inheritance for a user is only useful if you intend to convert the user to a user-extended role.

Remarks

With the exception of the SYS role, you can grant/revoke additional roles to/from a system role, provided you have administrative rights on the roles you are granting/revoking.

When granting a role to the MANAGE ROLES system privilege, you must use the special internal representation SYS_MANAGE_ROLES_ROLE. For example, GRANT ROLE role-name TO SYS_MANAGE_ROLES_ROLE;.

The GRANT syntax related to authorities, permissions, and groups used in pre-16.0 versions of the software is still supported but deprecated.

Privileges

You must have administrative rights for each role you grant.

To grant the SYS_REPLICATION_ADMIN_ROLE system role, you must have the MANAGE ROLES system privilege.

To grant the SYS_REPLICATION_RUN_ROLE system role, you must have the SYS_REPLICATION_ADMIN_ROLE system role.

Side effects

None

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

Granting roles to users
To grant user Bob the role called SecurityRole without administrative rights, execute the following statement:
```
GRANT ROLE SecurityRole TO Bob;
```
To grant the role RoleB all the privileges associated with RoleA but no administrative rights for RoleA, execute the following statement:
```
GRANT ROLE RoleA TO RoleB;
```
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;
```
Granting the SYS_RUN_REPLICATION_ROLE system role
The following example grants the SYS_RUN_REPLICATION_ROLE system role to grantee Sam_Singer:
```
GRANT ROLE SYS_RUN_REPLICATION_ROLE
TO Sam_Singer;
```
Granting the SYS_REPLICATION_ADMIN_ROLE system role
The following example grants the SYS_REPLICATION_ADMIN_ROLE role to grantee Sam_Singer:
```
GRANT ROLE SYS_REPLICATION_ADMIN_ROLE
TO Sam_Singer;
```