revoke

Description

Revokes permissions or roles from users, groups, or roles.

Syntax

To revoke permission to access database objects:

revoke [grant option for] 
	{all [privileges] | permission_list} 
	on {table_name [(column_list)] 
		| view_name [(column_list)] 
		| stored_procedure_name}
		| keyname}
	from {public | name_list | role_list}
	[cascade]

To revoke permission to select built-in functions:

revoke select 
	on [builtin] builtin 
	to {name_list | role_list}

To revoke permission to create database objects, execute set proxy, or execute set session authorization:

revoke {all [privileges] | command_list} 
	from {public | name_list | role_list}

To revoke permission to run set proxy or set tracing:

[revoke set {proxy | tracing} 
	from {public | name_list | role_list

To revoke a role from a user or another role:

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

To revoke access on some dbcc commands:

revoke dbcc {dbcc_command [on {all | database}]
		[, dbcc_command [on {all | database}], ...]} 
	from {user_list | role_list}

To revoke permission from other users, groups, and roles to create encryption keys.

revoke create encryption key from user | role | group

To revoke decrypt permission on a table or a list of columns in a table:

revoke decrypt on [owner.] tablename[(columnname [{,columname}])] 
	from user | group | role

To revoke the default permissions from public:

revoke default permissions on system tables

Parameters

all

when used to revoke permission to access database objects (the first syntax format), all revokes all permissions applicable to the specified object. All object owners can use revoke all with an object name to revoke permissions on their own objects.

Only the System Administrator or the Database Owner can revoke permission to revoke create command permissions (the third syntax format). When used by the System Administrator, revoke all revokes all create permissions (create database, create default, create procedure, create rule, create table, and create view). When the Database Owner uses revoke all, Adaptive Server revokes all create permissions except create database, and prints an informational message.

all does not apply to set proxy, set session authorization, create encryption key, and create trigger.

permission_list

is a list of permissions to revoke. If more than one permission is listed, separate them with commas. The following table illustrates the access permissions that can be granted and revoked on each type of object:

Object

permission_list can include

Table

select, insert, delete, update references, update statistics, delete statistics, and truncate table, decrypt

View

select, insert, delete, update, decrypt

Column

select, update, references, decrypt

Column names can be specified in either permission_list or column_list.

Stored procedure

execute

Encryption key

select

Permissions can be revoked only by the user who granted them.

builtin

is a built-in function. Specifying built-in functions allows you to differentiate between a table and a revocable built-in function with the same name. The functions are set_appcontext, get_appcontext, list_appcontext, and rm_appcontext.

command_list

is a list of commands. If more than one command is listed, separate them with commas. The command list can include create database, create default, create procedure, create rule, create table, create view, create encryption key, set proxy, or set session authorization. create database permission can be revoked only by a System Administrator and only from within the master database.

set proxy and set session authorization are identical; the only difference is that set session authorization follows the SQL standard, and set proxy is a Transact-SQL extension. Revoking permission to execute set proxy or set session authorization revokes permission to become another user in the server. Permissions for set proxy or set session authorization can be revoked only by a System Security Officer, and only from within the master database.

table_name

is the name of the table on which you are revoking permissions. The table must be in your current database. Only one object can be listed for each revoke statement.

column_list

is a list of columns, separated by commas, to which the privileges apply. If columns are specified, only select and update permissions can be revoked. References permissions also can be revoked on columns.

view_name

is the name of the view on which you are revoking permissions. The view must be in your current database. Only one object can be listed for each revoke statement.

stored _procedure_name

is the name of the stored procedure on which you are revoking permissions. The stored procedure must be in your current database. Only one object can be listed for each revoke statement.

keyname

is the name of the key from which you are revoking permission. The encryption key must be in your current database. Only one object can be listed for each revoke statement. You can revoke only select permission from a key.

public

is all users. For object access permissions, public excludes the object owner. For object creation permissions or set proxy authorizations, public excludes the Database Owner. You cannot grant permissions with grant option to “public” or to other groups or roles.

name_list

is a list of user and group names, separated by commas.

proxy

Revokes permission from a user to impersonate another user. Only the system security officer can revoke set proxy.

tracing

Revokes permission from a user to enable or disable tracing for the set option, set plan, and dbcc traceon or traceoff. Only the system administrator can revoke set tracing permission, and only from the master database.

role

is the name of a system or user-defined role. Use revoke role to revoke revoked roles from roles or users.

role_name

is the name of a system or user-defined role. This allows you to revoke permissions from all users who have been revoked a specific role. The role name can be either a system role or a user-defined role created by a System Security Officer with create role. Either type of role can be revoked to a user with the revoke role command. In addition, you can use sp_role to revoke system roles.

grantee

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

grant option for

revokes with grant option permissions, so that the users specified in name_list can no longer grant the specified permissions to other users. If those users have granted permissions to other users, you must use the cascade option to revoke permissions from those users. The user specified in name_list retains permission to access the object, but can no longer grant access to other users. grant option for applies only to object access permissions, not to object creation permissions.

cascade

revokes the specified object access permissions from all users to whom the revokee granted permissions. Applies only to object access permissions, not to object creation permissions. When you use revoke without grant option for, permissions granted to other users by the revokee are also revoked: the cascade occurs automatically.

dbcc_command

is the name of the dbcc command you are revoking. It cannot be a variable. Table 1-33 lists the valid revoke dbcc commands.

database

is the name of the database on which you are revoking permissions. It is used with database-specific dbcc commands to revoke permission only on the target database. The revokee must be a valid user in the target database. database conforms to the rules for identifiers and cannot be a variable.

If there are multiple revoked actions in the same command, database must be unique.

See “on all | database parameter and server-level commands” for more information.

user_list

is a list of users from whom you are revoking the permission, and cannot be a variable.

role_list

is a list of the name of system or user-defined roles from whom you are revoking the permission, and cannot be a variable.

NoteYou cannot grant or revoke dbcc commands to public or groups.

default permissions on system tables

specifies that you revoke the default permissions for the system tables listed in “revoking default permissions on system tables”.

Examples

Example 1

Revokes insert and delete permissions on the titles table from Mary and the “sales” group:

revoke insert, delete 
on titles 
from mary, sales

Example 2

Revokes select permission on the get_appcontext function from “public” (which includes all users):

revoke select on builtin get_appcontext from public 

Compare this to the following, which revokes select permission on a table called get_appcontext, if a table with that name exists:

revoke select on get_appcontext from public

Example 3

Two ways to revoke update permission on the price and advance columns of the titles table from “public:”

revoke update
on titles (price, advance)
from public

or:

revoke update (price, advance)
on titles
from public

Example 4

Revokes permission from Mary and John to use the create database and create table commands. Because create database permission is being revoked, this command must be executed by a System Administrator from within the master database. Mary’s and John’s create table permission is revoked only within the master database:

revoke create database, create table from mary, john

Example 5

Revokes permission from Harry and Billy to execute either set proxy or set session authorization to impersonate another user in the server:

revoke set proxy from harry, billy

Example 6

Revokes permission from users with sso_role to execute either set proxy or set session authorization:

revoke set session authorization from sso_role

Example 7

Revokes permission from users with vip_role to impersonate another user in the server. vip_role must be a role defined by a System Security Officer with the create role command:

revoke set proxy from vip_role

Example 8

Revokes all object creation permissions from Mary in the current database (except create encryption key):

revoke all from mary

Example 9

Revokes all object access permissions on the titles table from Mary (except decrypt permission):

revoke all on titles from mary

Example 10

Two ways to revoke Tom’s permission to create a referential integrity constraint on another table that refers to the price and advance columns in the titles table:

revoke references
on titles (price, advance)
from tom

or:

revoke references (price, advance)
on titles
from tom

Example 11

Revokes permission to execute new_sproc from all users who have been granted the “operator” role:

revoke execute on new_sproc from oper_role

Example 12

Revokes John’s permission to grant insert, update, and delete permissions on the authors table to other users. Also revokes from other users any such permissions that John has granted:

revoke grant option for
insert, update, delete
on authors
from john
cascade

Example 13

Revokes “doctor_role” from “specialist_role”:

revoke role doctor_role from specialist_role

Example 14

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 15

Revokes dbcc privileges from Frank:

1> use pubs2
2> go
1> revoke dbcc checkdb on pubs2 from checkdb_role
2> go
1> use master
2> go
1> revoke dbcc checkdb on all from frank
2> go
...

Example 16

Revokes truncate table and update statistics privileges from Harry on the authors table:

revoke truncate table on authors from harry
revoke update statistics on authors from harry

Example 17

Revokes the delete statistics privileges from user Billy on the authors table:

revoke delete statistics on authors from billy

Example 18

Revokes truncate table and update and delete statistics privileges from all users with the oper_role:

revoke truncate table on authors from oper_role
revoke update statistics on authors from oper_role
revoke delete statistics on authors from oper_role

Users Billy and Harry can no longer run these commands on authors.

Example 19

Revokes decrypt permissions from public:

revoke decrypt on customer from public

Example 20

Revokes create encryption key permissions from user joe:

revoke create encryption key from joe

Example 21

Revokes select on permission for the ssn_key from the database owner.

grant select on ssn_key to dbo

Usage


Using the cascade option


Revoking set proxy and set session authorization


Revoking from roles, users and groups


revoke dbcc command options

Table 1-33 lists the valid revoke dbcc commands.

Table 1-33: dbcc command options

Command name

Description

checkalloc

Checks the specified database to make sure all of its pages are correctly allocated, and that there are no unused allocated pages.

checkcatalog

Checks for consistency in and between system tables.

checkdb

Runs the same checks as checktable, but on each table in the specified database, including syslogs.

checkindex

Checks the specified index to make sure that:

  • Index and data pages are correctly linked.

  • Indexes are correctly sorted.

  • All pointers are consistent.

  • Data information on each page is reasonable.

  • Page offsets are reasonable.

checkstorage

Checks the specified database for:

  • Allocation

  • OAM page entries

  • Page consistency

  • Text-valued columns

  • Allocation of text-valued columns

  • Text-column chains

checktable

Checks the specified table to make sure that:

  • Index and data pages are correctly linked.

  • Indexes are correctly sorted.

  • All pointers are consistent.

  • Data information on each page is reasonable.

  • Page offsets are reasonable.

checkverify

Verifies the results of the most recent run of dbcc checkstorage for the specified database.

fix_text

Upgrades text values after any Adaptive Server character set is converted to a new multibyte character set.

indexalloc

Checks the specified index to make sure all pages are correctly allocated, and that there are no unused allocated pages.

reindex

Checks the integrity of indexes on user tables by running a fast version of dbcc checktable.

tablealloc

Checks the specified table to make sure that all pages are correctly allocated, and that there are no unused allocated pages.

textalloc

Checks for a violation of the format of the root page of a text or image index.

tune

Enables or disables tuning flags for special performance situations.

All of the options in Table 1-33 are database-level commands except for tune, which is a server-level command.

See Chapter 25, “Checking Database Consistency” in the System Administration Guide for more information on these dbcc commands.


on all | database parameter and server-level commands

The on database parameter specifies the database on which to invoke the database-level revoke dbcc command. Because on master revokes the ability to use dbcc commands on all databases, on master is the same as on all. You must be in the master database to use either the on all and on master parameters.

Neither the on database nor on all parameters work when invoking a server-level revoke dbcc command such as dbcc tune, because by doing so, you are forcing a server-level command to restrict itself to individual databases. For this reason, using the server-level revoke dbcc tune on master command raises an error.


revoking default permissions on system tables

default permissions on system tables revokes sysobjects (audflags) permissions from “public.”

Table 1-34 lists the system tables you can revoke the default permissions for when you issue the command from any database.

Table 1-34: System tables with revocable permissions issued from any database

sysalternates

sysjars

sysprotects

systabstats

sysattributes

syskeys

sysqueryplans

systhresholds

syscolumns

syslogs

sysreferences

systypes

syscomments

sysobjects

sysroles

sysusermessages

sysconstraints

syspartitions

syssegments

sysusers

sysdepends

sysprocedures

sysstatistics

sysxtypes

sysindexes

Table 1-35 lists the system tables you revoke the default permissions for when you issue this command from the master database.

Table 1-35: System tables with revocable permissions issued from master

sysdatabases

sysprocesses

syscurconfigs

systimeranges

sysdevices

systransactions

syslanguages

sysresourcelimits

syslocks

sysusages

syscharsets

syslogins

sysmessages

sysconfigures

sysservers

sysremotelogins


Revoking permissions for update statistics, delete statistics, and truncate table

Adaptive Server allows you to revoke permissions for users, roles, and groups for the update statistics, delete statistics, and truncate table commands. Table owners can also provide permissions through an implicit grant by adding update statistics, delete statistics, and truncate table to a stored procedure and then granting execute permissions on that procedure to a user or role.

You cannot revoke permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other than sysroles, syssrvroles, and sysloginroles, and can transfer this privilege to other users.

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

NoteOnce you revoke permission to execute update statistics from a user, they also lose permission to execute variations of this command, such as update all statistics, update partition statistics, update index statistics, update statistics table, and so on. For example, the following revokes Billy permission from running all variations of update statistics on the authors table:

revoke update statistics on authors to billy

If you revoke a user’s permission to execute update statistics, you also revoke their ability to execute the variations of this command.

You cannot revoke variants of update statistics (for example, update index statistics) separately. That is, you cannot issue:

revoke update all statistics from harry

You cannot grant and revoke delete statistics permissions at the column level. For more information, see the “Usage” section of grant.

The command fails and generates an error if a user issues update statistics, delete statistics, or truncate table and they:


revoke in a clustered environment

revoke fails if you attempt to revoke permissions from user-defined roles in a local temporary database.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

47

revoke

revoke

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

85

roles

create role, drop role, alter role, grant role, or revoke role

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

See also

Commands grant, setuser, set

Functions proc_role

System procedures sp_activeroles, sp_adduser, sp_changedbowner, sp_changegroup, sp_displaylogin, sp_displayroles, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helprotect, sp_helpuser, sp_modifylogin, sp_role