Use this statement to remove permissions from users.

Syntax 1

REVOKE permission, ... FROM userid, ...

permission : 
CONNECT 
| DBA 
| BACKUP 
| CREATE ON dbspace
| GROUP 
| INTEGRATED LOGIN
| KERBEROS LOGIN 
| MEMBERSHIP IN GROUP userid, ... 
| PROFILE 
| RESOURCE
| VALIDATE

Syntax 2

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

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

Syntax 3

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

Remarks

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, and so on) owned by that user and any permissions granted by that user. You cannot execute a REVOKE CONNECT on a user if the user being dropped owns a table referenced by a view owned by another user.

REVOKE GROUP automatically revokes MEMBERSHIP IN GROUP from all members of the group.

When you add a user to a group, the user inherits all the permissions assigned to that group. SQL 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 to each individual user ID that requires them.

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

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.

When you are connected to the utility database, executing REVOKE CONNECT FROM DBA disables future connections to the utility database. This means that no future connections can be made to the utility database unless you use a connection that existed before the REVOKE CONNECT was done, or restart the database server.

Side effects

Automatic commit.

See also

• GRANT statement

Standards and compatibility

Example

Prevent user Dave from updating the Employees table.

REVOKE UPDATE ON Employees FROM Dave;

Revoke resource permissions from user Jim.

REVOKE RESOURCE FROM Jim;

Revoke an integrated login mapping from the user profile named Administrator.

REVOKE INTEGRATED LOGIN FROM Administrator;

Disallow the Finance group from executing the procedure ShowCustomers.

REVOKE EXECUTE ON ShowCustomers FROM Finance;

Drop the user ID FranW from the database.

REVOKE CONNECT FROM FranW;