GRANT ROLE Statement

Quick Links:

Syntax

GRANT ROLE role_name [, …] 
   TO grantee [, …]
   [ {WITH NO ADMIN | WITH ADMIN [ ONLY ] } OPTION ]
   [ WITH NO SYSTEM PRIVILEGE INHERITANCE ]

role_name
   dbo^†††
   | diagnostics^†††
   | PUBLIC^†††
   | rs_systabgroup^†††
   | SA_DEBUG^†††
   | SYS^†††
   | SYS_AUTH_SA_ROLE
   | SYS_AUTH_SSO_ROLE
   | SYS_AUTH_DBA_ROLE^††
   | SYS_AUTH_RESOURCE_ROLE^†
   | SYS_AUTH_BACKUP_ROLE^†
   | SYS_AUTH_VALIDATE_ROLE^†
   | SYS_AUTH_WRITEFILE_ROLE 
   | SYS_AUTH_WRITEFILECLIENT_ROLE
   | SYS_AUTH_READFILE_ROLE 
   | SYS_AUTH_READFILECLIENT_ROLE 
   | SYS_AUTH_PROFILE_ROLE 
   | SYS_AUTH_USER_ADMIN_ROLE 
   | SYS_AUTH_SPACE_ADMIN_ROLE 
   | SYS_AUTH_MULTIPLEX_ADMIN_ROLE 
   | SYS_AUTH_OPERATOR_ROLE 
   | SYS_AUTH_PERMS_ADMIN_ROLE
   | SYS_REPLICATE_ADMIN_ROLE^†††
   | SYS_RUN_REPLICATE_ROLE^†††
   | SYS_SPATIAL_ADMIN_ROLE^†††
   | user-defined role name

The WITH NO SYSTEM PRIVILEGE INHERITANCE clause can be used when granting select compatibility roles to other roles. It prevents automatic inheritance of the compatibility role's underlying system privileges by members of the role. When granted to user-extended roles, the WITH NO SYSTEM PRIVILEGE INHERITANCE clause applies to members of the role only. The user acting as a role automatically inherits the underlying system privileges regardless of the clause.
The WITH NO ADMIN OPTION WITH NO SYSTEM PRIVILEGE INHERITANCE and WITH NO SYSTEM PRIVILEGE INHERITANCE clauses are semantically equivalent.
^†The WITH ADMIN OPTION or WITH ADMIN ONLY clauses can not be specified in combination with the WITH NO SYSTEM PRIVILEGE INHERITANCE clause when granting the SYS_AUTH_BACKUP_ROLE, SYS_AUTH_RESOURCE_ROLE, or SYS_AUTH_VALIDATE_ROLE roles.
^††The WITH ADMIN OPTION clause can only be specified in combination with the WITH NO SYSTEM PRIVILEGE INHERITANCE clause when granting the SYS_AUTH_DBA_ROLE or SYS_RUN_REPLICATION_ROLE roles.
^†††The WITH ADMIN OPTION and WITH ADMIN ONLY OPTION clauses are not supported for system roles.

Parameters

(back to top)

role_name – must already exist in the database. Separate multiple role names with commas.

grantee – must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.

WITH NO ADMIN OPTION – each grantee is granted the underlying system privileges of each role_name, but cannot grant role_name to another user.

WITH ADMIN ONLY OPTION – each userID is granted administrative privileges over each role_name, but not the underlying system privileges of role_name.

WITH ADMIN OPTION – each userID is granted the underlying system privileges of each role_name, along with the ability to grant role_name to another user.

WITH NO SYSTEM PRIVILEGE INHERITANCE – the underlying system privileges of the granting role are not inherited by the members of the receiving role. However, if the receiving role is a user-extended role, the underlying system privileges are granted to the extended user.

Examples

(back to top)

Example 1 – grants Sales_Role to Sally, with administrative privileges, which means she can grant or revoke Sales_Role to other users as well as perform any authorized tasks granted by the role:
```
GRANT ROLE Sales_Role TO Sally WITH ADMIN OPTION
```
Example 2 – grants the compatibility role SYS_AUTH_PROFILE_ROLE to the role Sales_Admin with no administrative rights. Sales_Admin is a standalone role and Mary and Peter have been granted Sales_Admin. Since SYS_AUTH_PROFILE_ROLE is an inheritable compatibility role, Mary and Peter are granted the underlying system privileges of Sales_Role. Since the role is granted with no administrative rights, they cannot grant or revoke the role.
```
GRANT ROLE SYS_AUTH_PROFILE_ROLE TO Sales_Role WITH NO ADMIN OPTION
```
Example 3 – grants the compatibility role SYS_AUTH_BACKUP_ROLE to Tom with no administrative rights. Tom is a user-extended role to which Betty and Laurel have been granted. Since SYS_AUTH_BACKUP_ROLE is a non-inheritable compatibility role, the underlying system privileges of the role are not granted to Betty and Laurel. However, since Tom is an extended user, the underlying system privileges are granted directly to Tom.
```
GRANT ROLE SYS_AUTH_BACKUP_ROLE TO Tom 
WITH NO SYSTEM PRIVILEGE INHERITANCE
```

Usage

(back to top)

Use of the WITH ADMIN OPTION or WITH ADMIN ONLY OPTION clause allows the grantee to grant or revoke the role, but does not allow the grantee to drop the role.

By default, if no administrative clause is specified in the grant statement, each compatibility role is granted with these default administrative rights:

WITH ADMIN OPTION	WITH ADMIN ONLY OPTION	WITH NO ADMIN OPTION
SYS_AUTH_SA_ROLE SYS_AUTH_SSO_ROLE	SYS_AUTH_DBA_ROLE	SYS_AUTH_RESOURCE_ROLE SYS_AUTH_BACKUP_ROLE SYS_AUTH_VALIDATE_ROLE SYS_AUTH_WRITEFILE_ROLE SYS_AUTH_WRITEFILECLIENT_ROLE SYS_AUTH_READFILE_ROLE SYS_AUTH_READFILECLIENT_ROLE SYS_AUTH_PROFILE_ROLE SYS_AUTH_USER_ADMIN_ROLE SYS_AUTH_SPACE_ADMIN_ROLE SYS_AUTH_MULTIPLEX_ADMIN_ROLE SYS_AUTH_OPERATOR_ROLE SA_DEBUG SYS_RUN_REPLICATION_ROLE

WITH ADMIN OPTION

WITH ADMIN ONLY OPTION

WITH NO ADMIN OPTION

SYS_AUTH_SA_ROLE SYS_AUTH_SSO_ROLE

SYS_AUTH_DBA_ROLE

SYS_AUTH_RESOURCE_ROLE

SYS_AUTH_BACKUP_ROLE

SYS_AUTH_VALIDATE_ROLE

SYS_AUTH_WRITEFILE_ROLE

SYS_AUTH_WRITEFILECLIENT_ROLE

SYS_AUTH_READFILE_ROLE

SYS_AUTH_READFILECLIENT_ROLE

SYS_AUTH_PROFILE_ROLE

SYS_AUTH_USER_ADMIN_ROLE

SYS_AUTH_SPACE_ADMIN_ROLE

SYS_AUTH_MULTIPLEX_ADMIN_ROLE

SYS_AUTH_OPERATOR_ROLE

SA_DEBUG

SYS_RUN_REPLICATION_ROLE

The SYS_AUTH_PERMS_ADMIN_ROLE role grants these underlying roles with these default administrative rights:

WITH ADMIN OPTION	WITH NO ADMIN OPTION
SYS_AUTH_BACKUP_ROLE SYS_AUTH_OPERATOR_ROLE SYS_AUTH_USER_ADMIN_ROLE SYS_AUTH_SPACE_ADMIN_ROLE SYS_AUTH_MULTIPLEX_ADMIN_ROLE SYS_AUTH_RESOURCE_ROLE SYS_AUTH_VALIDATE_ROLE SYS_AUTH_PROFILE_ROLE SYS_AUTH_WRITEFILE_ROLE SYS_AUTH_WRITEFILECLIENT_ROLE SYS_AUTH_READFILE_ROLE SYS_AUTH_READFILECLIENT_ROLE	MANAGE ROLES MANAGE ANY OBJECT PRIVILEGE CHANGE PASSWORD

WITH ADMIN OPTION

WITH NO ADMIN OPTION

SYS_AUTH_BACKUP_ROLE

SYS_AUTH_OPERATOR_ROLE

SYS_AUTH_USER_ADMIN_ROLE

SYS_AUTH_SPACE_ADMIN_ROLE

SYS_AUTH_MULTIPLEX_ADMIN_ROLE

SYS_AUTH_RESOURCE_ROLE

SYS_AUTH_VALIDATE_ROLE

SYS_AUTH_PROFILE_ROLE

SYS_AUTH_WRITEFILE_ROLE

SYS_AUTH_WRITEFILECLIENT_ROLE

SYS_AUTH_READFILE_ROLE

SYS_AUTH_READFILECLIENT_ROLE

MANAGE ROLES

MANAGE ANY OBJECT PRIVILEGE

CHANGE PASSWORD

Standards

(back to top)

SQL–Other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
SAP Sybase Database product–Syntax is supported in Adaptive Server.

Permissions

(back to top)

Requires MANAGE ROLES system privilege to grant these system roles:
- dbo
- diagnostics
- PUBLIC
- rs_systabgroup
- SA_DEBUG SYS
- SYS
- SYS_REPLICATION_ADMIN_ROLE
- SYS_RUN_REPLICATION_ROLE
- SYS_SPATIAL_ADMIN_ROLE
Requires administrative privilege over the role to grant these roles:
- SYS_AUTH_SA_ROLE
- SYS_AUTH_SSO_ROLE
- SYS_AUTH_DBA_ROLE
- SYS_AUTH_RESOURCE_ROLE
- SYS_AUTH_BACKUP_ROLE
- SYS_AUTH_VALIDATE_ROLE
- SYS_AUTH_WRITEFILE_ROLE
- SYS_AUTH_WRITEFILECLIENT_ROLE
- SYS_AUTH_READFILE_ROLE
- SYS_AUTH_READFILECLIENT_ROLE
- SYS_AUTH_PROFILE_ROLE
- SYS_AUTH_USER_ADMIN_ROLE
- SYS_AUTH_SPACE_ADMIN_ROLE
- SYS_AUTH_MULTIPLEX_ADMIN_ROLE
- SYS_AUTH_OPERATOR_ROLE
- SYS_AUTH_PERMS_ADMIN_ROLE
- <user-defined role name>