REVOKE Statement

Removes permissions for specified users.

Syntax

Syntax 1

REVOKE
{ BACKUP 
| CONNECT 
| DBA 
| GROUP
| INTEGRATED LOGIN 
| KERBEROS LOGIN 
| MEMBERSHIP IN GROUP userid [, …]
| MULTIPLEX ADMIN
| OPERATOR
| PERMS ADMIN
| PROFILE
| RESOURCE
| SPACE ADMIN
| USER ADMIN }
| VALIDATE
… FROM userid [, …]

Syntax 2

REVOKE
{…ALL [ PRIVILEGES ] | ALTER | DELETE | INSERT
| REFERENCE | SELECT [ ( column-name [, …] ) ] | UPDATE [ ( column-name, …) ] }
… ON [ owner.]table-name FROM userid [, …]

Syntax 3

REVOKE EXECUTE ON [ owner.]procedure-name FROM userid [, …]

Syntax 4

REVOKE CREATE ON dbspace-name FROM userid [, …]

Examples

Example 1 – Prevent user dave from inserting into the Employees table:
```
REVOKE INSERT ON Employees FROM dave 
```
Example 2 – Revoke resource permission from user Jim:
```
REVOKE RESOURCE FROM Jim 
```
Example 3 – Prevent user dave from updating the Employees table:
```
REVOKE UPDATE ON Employees FROM dave 
```
Example 4 – Revoke integrated login mapping from the user profile name Administrator:
```
REVOKE INTEGRATED LOGIN FROM Administrator
```
Example 5 – Disallow the finance group from executing the procedure sp_customer_list:
```
REVOKE EXECUTE ON sp_customer_list
FROM finance
```
Example 6 – Drop user ID franw from the database:
```
REVOKE CONNECT FROM franw
```
Example 7 – Revoke CREATE privilege on dbspace DspHist from user Smith:
```
REVOKE CREATE ON DspHist FROM Smith
```
Example 8 – Revoke CREATE permission on dbspace DspHist from user ID fionat from the database:
```
REVOKE CREATE ON DspHist FROM fionat 
```

Usage

The REVOKE statement is used to remove permissions that were given using the GRANT statement.

Syntax 1 is used to revoke special user permissions (authorities) and Syntax 2 is used to revoke table permissions. Syntax 3 is used to revoke permission to execute a procedure. REVOKE CONNECT is used to remove a user ID from a database.

Note: Use system procedures, not GRANT and REVOKE, to add and remove user IDs.

REVOKE GROUP automatically revokes membership from all members of the group.

REVOKE CREATE removes CREATE permission on the specified dbspace from the specified user IDs.

You cannot revoke permissions for a specific user within a group. If you do not want a specific user to access a particular table, view, or procedure, then do not make that user a member of a group that has permissions on that object.

Note: You cannot revoke the connect privileges of a user if that user owns database objects, such as tables. Attempting to do so with a REVOKE statement or sp_dropuser procedure returns an error such as “Cannot drop a user that owns tables in runtime system.”

Side Effects

Automatic commit

Standards

SQL—Syntax 1 is a vendor extension to ISO/ANSI SQL grammar. Syntax 2 is an entry-level feature. Syntax 3 is a Persistent Stored Module feature.
Sybase—Syntax 2 and 3 are supported by Adaptive Server Enterprise. Syntax 1 is not supported by Adaptive Server Enterprise. User management and security models are different for Sybase IQ and Adaptive Server Enterprise.

Permissions

Must be the grantor of the permissions that are being revoked, or must have DBA authority.

For Syntax 1, REVOKE CONNECT, REVOKE INTEGRATED LOGIN and REVOKE KERBEROS LOGIN require DBA or USER ADMIN authority. REVOKE GROUP, REVOKE (authority, except DBA), and REVOKE MEMBERSHIP IN GROUP require DBA or PERMS ADMIN authority. Only a DBA can revoke DBA authority.

If revoking CONNECT permissions or revoking table permissions from another user, the other user must not be connected to the database.

For Syntax 2, REVOKE, REVOKE ALTER, REVOKE DELETE, REVOKE INSERT, REVOKE REFERENCE, REVOKE SELECT, and REVOKE UPDATE require DBA or PERMS ADMIN authority.

For Syntax 3, you must have DBA or PERMS ADMIN authority.

For Syntax 4, you must have DBA or SPACE ADMIN authority.