revoke

Revokes permissions from users, roles, or groups.

Syntax

Revokes 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 | function_name
		| keyname}
	[with {pred_name | {all |no} predicates}]
	from {public | name_list | role_list}
	[cascade]
	[granted by grantor]

Revokes permission to select built-in functions:

revoke select 
	on [builtin] builtin 
	from {name_list | role_list}
	[granted by grantor]

Revokes system privileges:

revoke {all [privileges] | privilege_list} 
	from {public | name_list | role_list}
	[granted by grantor]

Revokes permission to run set proxy

revoke set proxy 
	from {public | name_list | role_list}
	[granted by grantor]

Revokes dbcc privileges:

revoke {dbcc_privilege [on database]
		[, dbcc_privilege [on database], ...]} 
	from {user_list | role_list}
	[granted by grantor]

Revokes the default permissions from public:

revoke default permissions on system tables

Parameters

all – when used to assign permission to access database objects (see syntax for “Revokes permission to access database objects”), all specifies that all permissions, except decrypt, that are applicable to the specified object are revoked. All object owners can use revoke all with an object name to revoke permissions on their own objects. You must revoke decrypt permissions separately.
When granular permissions is not enabled, a system administrator or the database owner can use revoke all to revoke privileges to create database objects (see syntax for “Revokes system privileges”). When used by a system administrator, revoke all revokes all create privileges (create database, create default, create procedure, create rule, create table, create function, and create view). When the database owner uses revoke all, or executes revoke all outside the master database, the SAP ASE server revokes all create privileges except create database and prints an informational message.

Revoking all create privileges using revoke all is not supported when granular permissions is enabled. For more information, see Using Granular Permissions in the Security Administration Guide.

all cannot be used for a revoke statement that includes a where clause.

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, truncate table, decrypt, identity_insert , identity_update
View	select, insert, delete, update, decrypt, identity_insert , identity_update
Column	select, update, references, decrypt Column names can be specified in either `permission_list` or `column_list`.
Stored procedure	execute
Encryption key	select
Function	execute *

Note: Permissions with an asterisk (*) can only be granted when granular permissions is enabled.

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, authmech, and rm_appcontext.
privilege_list – is a list of system privileges that can be revoke. System privileges include server-wide and database-wide privileges. See the “Usage” section for details on how to revoke system privileges. Use commas to separate multiple commands.
table_name – is the name of the table on which you are revoking permissions. The table must be in your current database.
column_list – is a list of columns, separated by commas, to which the privileges apply. If columns are specified, only select, reference, decrypt, and update permissions can be revoked.
view_name – is the name of the view on which you are revoking permissions. The view must be in your current database.
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.
function_name – is the name of the function for which you are revoking permissions. The function must be in your current database.
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.
[with {pred_name | {all | no} predicates}] – may be followed by a named predicate, the double keyword all predicates, or the double keyword no predicates.
- pred_name – name of the predicated grant you are revoking. sp_helprotect displays the predicate names that identify row-filtering grants.
- no predicates – instructs the SAP ASE server to remove only non-predicated grants for the given access from the named grantee.
- all predicates - instructs the SAP ASE server to remove all the predicated grants for the given access from the named grantor. Any non-predicated grants remain.
If you omit the with clause, both predicated and non-predicated grants are revoked (the default behavior).
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.
set proxy – Revokes privilege from a user to impersonate another user.
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.
granted by grantor – indicates to revoke the permission or privilege granted by grantor instead of the user executing the revoke command.
grantor – a valid user name in current database.
dbcc_privilege – is the name of the dbcc privileges you are revoking. It cannot be a variable.
database – is the name of the database on which you are revoking permissions. It is used with database-specific dbcc privileges 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.
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.
Note: You cannot grant or revoke dbcc privileges to public or groups.
all [privileges] – uses all or all privileges to revoke all granted and denied privileges.
column_list – if used with with pred_name, the predicated row access is removed for the named columns. If there still exist other columns referenced for this row-level privilege, the privilege and its related named predicate remain in sysprotects for the reduced column list.
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 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 all object creation permissions from Mary in the current database (except create encryption key and create trigger):
```
revoke all from mary
```
Example 8 – Revokes all object access permissions on the titles table from Mary (except decrypt permission):
```
revoke all on titles from mary
```
Example 9 – 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 10 – 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 11 – 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 12 – Revokes dbcc privileges from Frank:
```
revoke dbcc checkdb on all from frank
```
Example 13 – 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
```
Users Billy and Harry can no longer run these commands on authors.

Example 14 – 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

Example 15 – Revokes decrypt permissions from public:
```
revoke decrypt on customer from public
```
Example 16 – Revokes create encryption key permissions from user joe:
```
revoke create encryption key from joe
```
Example 17 – Revokes select permission for the ssn_key from the database owner.
```
revoke select on ssn_key from dbo
```
Example 18 – The following examples assume the following grants have been made for selecting from table t1 by user1:
- An unconditional grant to see all rows for t1.col1 and t1.col4:
```
grant select on t1 (col1, col4) to user1
```
- A row-filtering grant to be applied when selecting t1.col2 or t1.col3:
```
grant select on t1 (col2, col3)
  where col1 = 1 as pred1
  to user1
```
Removes select permission on t1.col2 with pred1:
```
revoke select on t1 (col2) with pred1
  from user1
```
Note: After this revoke, pred1 is still applied when user1 selects t1.col3.
If the grantor issued either of the following, then all permissions on t1 using pred1 would be revoked from user1:
```
revoke select on t1 (col2, col3) with pred1
  from user1
```
or
```
revoke select on t1 with pred1
```
Example 19 – After the grants described in the previous example, the following removes the grant on t1.col2 and t1.col3 with predicate pred1 (all predicates revokes all row-filtering predicated grants for a given access and grantee):
```
revoke select on t1 with all predicates
  from user1
```
Example 20 – Removes all select access on t1 from user1; that is, all predicated and nonpredicated grants on t1 are granted to user1 for select on t1:
```
revoke select on t1 from user1
```
Example 21 – Applies to only the non-predicated grant:
```
revoke select on t1 with no predicates
```
Example 22 – Revokes select permission on table mary.books from john granted by mary:
```
revoke select on mary.books from john granted by mary
```
Example 23 – Revokes system privilege manage any login from user smith:
```
use master
```
```
revoke manage any login from smith
```

Usage

See the grant command for more information about permissions.
You can revoke permissions only on objects in your current database.
You can revoke only permissions or privileges that were granted by you without using the granted by grantor option. With the granted by grantor option, you can revoke permissions or privileges granted by other users.
grant and revoke commands are order-sensitive. When there is a conflict, the command issued most recently takes effect. An exeption are grants and revokes of predicated privileges. See How SAP ASE Saves Predicated Privileges in sysprotects in the Security Administration Guide.
You can substitute the word to for the word from in the revoke syntax.
If you do not specify grant option for in a revoke statement, with grant option permissions are revoked from the user along with the specified object access permissions. In addition, if the user has granted the specified permissions to any other users, all of those permissions are revoked. In other words, the revoke cascades.
A user, group, or role can be granted the same privilege or permission by different grantors. In this situation, there are multiple rows in sysprotects which represents multiple grants on the same privilege or permission. If one or more than one grants are revoked later, the user, group, or role may still have the privilege or permission if there is at least one grant remain unrevoked.
A grant statement adds one row to the sysprotects system table for each user, group, or role that receives the permission. If you subsequently revoke the permission from the user or group, the SAP ASE server removes the row from sysprotects. If you revoke the permission from only selected group members, but not from the entire group to which it was granted, the SAP ASE server retains the original row and adds a new row for the revoke.
Permission to issue create trigger is granted to users by default. When you revoke permission for a user to create triggers, a revoke row is added in the sysprotects table for that user. To grant permission to issue create trigger, you must issue two grant commands. The first command removes the revoke row from sysprotects; the second inserts a grant row. If you revoke permission to create triggers, the user cannot create triggers even on tables that the user owns. Revoking permission to create triggers from a user affects only the database where the revoke command was issued.
In a clustered environment, revoke fails if you attempt to revoke permissions from user-defined roles in a local temporary database.
Database user groups allow you to grant or revoke permissions to more than one user at a time. A user is always a member of the default group, “public” and can be a member of only one other group. The SAP ASE installation script assigns a set of permissions to “public.”

Create groups with sp_addgroup and remove groups with sp_dropgroup. Add new users to a group with sp_adduser. Change a user’s group membership with sp_changegroup. To display the members of a group, use sp_helpgroup.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, in general, revoke can be executed by a user with one of the following privilege management privileges, depending the privilege or permission being revoked. For server-wide privileges, you must be a user with `manage serverpermissions` privilege or `manage securitypermissions` privilege. For database-wide privileges, you must be a user with `manage database` `permissions` privilege. For object privileges, you must be the object owner or be a user with `manage any object permission` privilege. To execute revoke default, you must be the database owner or a user with `own database` privilege on the database.
Disabled	With granular permissions enabled: Command execution – only a system administrator can revoke create database permission, and only from the `master` database. Only a system security officer can revoke create trigger and create encryption key permission. Database consistency checking – only system administrators can run revoke dbcc commands. Database owners cannot run revoke dbcc. Database object access – revoke permission for database objects defaults to object owners. An object owner can revoke permission from other users on his or her own database objects. Functions – only system administrators can revoke permissions on built-in functions. Proxy and session authorization – only a system security officer can revoke set proxy or set session authorization, and only from the master database. Roles – you can revoke roles only from the `master` database. Only a system security officer can revoke sso_role, , or a user-defined role from a user or a role. Only system administrators can revoke oper_rolesa_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. Tables – database owners can revoke default permissions on system tables. Table owners and the system security officer can revoke decrypt permission on a table or a list of columns in a table. Defaults – database owners and logins with sa_role can revoke defaults.

Setting

Description

Enabled

With granular permissions enabled, in general, revoke can be executed by a user with one of the following privilege management privileges, depending the privilege or permission being revoked.

For server-wide privileges, you must be a user with manage serverpermissions privilege or manage securitypermissions privilege.

For database-wide privileges, you must be a user with manage database permissions privilege.

For object privileges, you must be the object owner or be a user with manage any object permission privilege.

To execute revoke default, you must be the database owner or a user with own database privilege on the database.

Disabled

With granular permissions enabled:

Command execution – only a system administrator can revoke create database permission, and only from the master database. Only a system security officer can revoke create trigger and create encryption key permission.
Database consistency checking – only system administrators can run revoke dbcc commands. Database owners cannot run revoke dbcc.
Database object access – revoke permission for database objects defaults to object owners. An object owner can revoke permission from other users on his or her own database objects.
Functions – only system administrators can revoke permissions on built-in functions.
Proxy and session authorization – only a system security officer can revoke set proxy or set session authorization, and only from the master database.
Roles – you can revoke roles only from the master database. Only a system security officer can revoke sso_role, , or a user-defined role from a user or a role. Only system administrators can revoke oper_rolesa_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.
Tables – database owners can revoke default permissions on system tables. Table owners and the system security officer can revoke decrypt permission on a table or a list of columns in a table.
Defaults – database owners and logins with sa_role can revoke defaults.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	47
Audit option	revoke
Command or access audited	revoke
Information in `extrainfo`	Roles – current active roles Keywords or options – Full command text of the revoke statement Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect