Removes permissions for specified users.
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 [, …]
Prevents user dave from inserting into the Employees table:
REVOKE INSERT ON Employees FROM dave
Revokes resource permission from user Jim:
REVOKE RESOURCE FROM Jim
Prevents user dave from updating the Employees table:
REVOKE UPDATE ON Employees FROM dave
Revokes integrated login mapping from the user profile name Administrator:
REVOKE INTEGRATED LOGIN FROM Administrator
Disallows the finance group from executing the procedure sp_customer_list:
REVOKE EXECUTE ON sp_customer_list FROM finance
Drops user ID franw from the database:
REVOKE CONNECT FROM franw
Revokes CREATE privilege on dbspace DspHist from user Smith:
REVOKE CREATE ON DspHist FROM Smith
Revokes CREATE permission on dbspace DspHist from user ID fionat from the database:
REVOKE CREATE ON DspHist FROM fionat
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.
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.
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.”
Automatic commit.
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.
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.
