revoke role

Description

Revokes a role from a group, login, login profile, or role:

Syntax

revoke role {role_name [, role_list ...]} from 
	{grantee [, grantee ...]}

Parameters

role: is the name of a system or user-defined role. Use revoke role to revoke granted roles from roles, logins, or login profiles.

role_name: is the name of a system or user-defined role. When revoking a role from a grantee you are revoking all permissions which that grantee has through role membership.

grantee: is the name of a system role, user-defined role, a login profile, or the login name of a user, from whom you are revoking a role.

role_list: is a list of the name of the system or user-defined roles you are revoking and cannot be a variable.

Examples

Example 1

Revokes “doctor_role” from “specialist_role”:

revoke role doctor_role from specialist_role

Example 2

Revokes “doctor_role” and “surgeon_role” from “specialist_role” and “intern_role”, and from users Mary and Tom:

revoke role doctor_role, surgeon_role from specialist_role, intern_role, mary, tom

Example 3

User Smith, with manage roles privileges, revokes the nurse_role from the doctor_role, which was originally granted by roleAdmin:

revoke role nurse_role from doctor_role
granted by roleAdmin

manage roles privileges is only available when granular permissions is enabled.

Example 4

Revokes the system role oper_role from the login profile assumed by system operators.

revoke role oper_role from lp_operator

Usage

You can revoke a role from a user while the user is logged in. Adaptive Server verifies a user’s activated roles before performing access checks.
If you revoke a role from a login profile, Adaptive Server revokes the role from all users assigned to that profile, including users currently logged in to Adaptive Server.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for revoke role differ based on your granular permissions settings.

Granular permissions enabled

When granular permissions is enabled, you must be a user with manage roles privilege.

Granular permissions disabled

With granular permissions enabled:

Roles – You can revoke roles only from the master database. Only a system security officer can revoke sso_role, oper_role, or a user-defined role from a user or a role. Only system administrators can revoke sa_role from a user or a role. Only a user who has both sa_role and sso_role can revoke a role that includes sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
85	role	create role, drop role, alter role, grant role, or revoke role	Roles – current active roles Keywords or options – Full command text of revoke role statement. Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect