Show TOC

REVOKE ROLE StatementLocate this document in the navigation structure

Removes a users membership in a role or his or her ability to administer the role.

Quick Links:

Go to Parameters

Go to Examples

Go to Standards

Go to Permissions

Syntax
REVOKE [ ADMIN OPTION FOR ] ROLE role_name [,...] 
   FROM <grantee> [,...]

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 ADMIN OPTION FOR clause is not supported for system roles.

Parameters

(back to top)

  • role_name must already exist in the database. Separate multiple role names with commas.
  • userID must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.
  • ADMIN OPTION FOR each <userID> must have been granted administrative privilege over the specified <role_name>.
    Note This clause revokes administrative privileges of the role only, not membership in the role, unless the role was originally granted with the WITH ADMIN ONLY OPTION clause. For roles granted with the WITH ADMIN ONLY OPTION clause, the ADMIN OPTION FOR clause is optional as it is semantically equivalent to revoking membership in a role in its entirety.
Examples

(back to top)

  • Example 1 revokes the user-defined (standalone) role Role1 from User1:
    REVOKE ROLE Role1 FROM User1

    After you execute this command, User1 no longer has the rights to perform any authorized tasks using any system privileges granted to Role1.
  • Example 2 revokes the ability for User1 to administer the compatibility role SYS_AUTH_WRITEFILE_ROLE:
    REVOKE ADMIN OPTION FOR ROLE SYS_AUTH_WRITEFILE_ROLE FROM User1

    User1 retains the ability to perform any authorized tasks granted by SYS_AUTH_WRITEFILE_ROLE.
Standards

(back to top)

  • SQ Other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
  • SAP Database products–Syntax is supported in SAP ASE.
Permissions

(back to top)

Requires the MANAGE ROLES system privilege to revoke these roles:
  • diagnostics
  • dbo
  • PUBLIC
  • rs_systabgroup
  • SA_DEBUG
  • SYS
  • SYS_RUN_REPLICATE_ROLE
  • SYS_SPATIAL_ADMIN_ROLE
Requires administrative privilege over the role to revoke 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>
Related Information