GRANT ROLE Statement
Grants roles to users or other roles, with or without administrative rights.
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
- must already exist in the database. Separate multiple role names with commas.
- must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.
- each <grantee> is granted the underlying system privileges of each <role_name>, but cannot grant <role_name> to another user.
- each <userID> is granted administrative privileges over each <role_name>, but not the underlying system privileges of <role_name>.
- each userID is granted the underlying system privileges of each <role_name>, along with the ability to grant <role_name> to another user.
- 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
- 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
- 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
- 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
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 |
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 |
Standards
- SQL–Other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
- SAP Database products–Syntax is supported in SAP ASE.