ASA SQL Reference
  SQL Statements

REVOKE statement

Description 

Use this statement to remove permissions for the specified users.

Syntax 1 

REVOKE special-priv, ... FROM userid, ...

special-priv :
  CONNECT
 | DBA
 | INTEGRATED LOGIN
 | GROUP
 | MEMBERSHIP IN GROUP userid, ...
 | RESOURCE

Syntax 2 

REVOKE table-priv, ... ON [ owner.]table-name FROM userid, ...

table-priv :
  ALL [PRIVILEGES]
 | ALTER
 | DELETE
 | INSERT
 | REFERENCES [ ( column-name, ...) ]
 | SELECT [ ( column-name, ...) ]
 | UPDATE [ ( column-name, ...) ]

Syntax 3 

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

Usage 

The REVOKE statement removes permissions given using the GRANT statement. Syntax 1 revokes special user permissions. Syntax 2 revokes table permissions. Syntax 3 revokes permission to execute a procedure. REVOKE CONNECT removes a user ID from a database, and also destroys any objects (tables, views, procedures, etc.) owned by that user and any permissions granted by that user. REVOKE GROUP automatically REVOKES MEMBERSHIP from all members of the group.

When you add a user to a group, the user inherits all the permissions assigned to that group. Adaptive Server Anywhere does not allow you to revoke a subset of the permissions that a user inherits as a member of a group because you can only revoke permissions that are explicitly given by a GRANT statement. If you need to have different permissions for different users, you can create different groups with the appropriate permissions, or you can explicitly grant each user the permissions they require.

When you grant or revoke group permissions for tables, views, or procedures, all members of the group inherit those changes. The DBA, RESOURCE, and GROUP permissions are not inherited: you must assign them individually to each individual user ID that requires them.

If you give a user GRANT option permission, and later revoke that permission, you also revoke any permissions that that user granted to others while they had the GRANT option.

Permissions 

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

If you are revoking connect permissions or table permissions from another user, the other user must not be connected to the database. You cannot revoke connect permissions from DBO.

Side effects 

Automatic commit.

See also 

GRANT statement

Standards and compatibility 

• SQL/92    Syntax 1 is a vendor extension. Syntax 2 is an entry-level feature. Syntax 3 is a Persistent Stored Modules feature.

• SQL/99    Syntax 1 is a vendor extension. Syntax 2 is a core feature. Syntax 3 is a Persistent Stored Modules 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 Adaptive Server Anywhere and Adaptive Server Enterprise.

Example 

Prevent user Dave from updating the employee table.

REVOKE UPDATE ON employee FROM dave;

Revoke resource permissions from user Jim.

REVOKE RESOURCE FROM Jim;

Revoke integrated login mapping from user profile name Administrator.

REVOKE INTEGRATED LOGIN FROM Administrator;

Disallow the Finance group from executing the procedure sp_customer_list.

REVOKE EXECUTE ON sp_customer_list
FROM finance;

Drop user ID FranW from the database.

REVOKE CONNECT FROM FranW