REVOKE statement

Description

Removes permissions for specified user(s).

Syntax

Syntax 1

REVOKE
{ CONNECT | DBA | INTEGRATED LOGIN | GROUP
| MEMBERSHIP IN GROUP userid [, ...] | RESOURCE }
... 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 [, ...]

Examples

Example 1

• Prevent user dave from inserting into the employee table.

REVOKE INSERT ON employee FROM dave ;

Example 2

• Revoke resource permissions from user Jim.

REVOKE RESOURCE FROM Jim ;

Example 3

• Prevent user dave from updating the employee table.

REVOKE UPDATE ON employee FROM dave ;

Example 4

• Revoke integrated login mapping from 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 ;

Usage

The REVOKE statement is used to remove permissions that were given using the GRANT statement. Form 1 is used to revoke special user permissions and Form 2 is used to revoke table permissions. Form 3 is used to revoke permission to execute a procedure. REVOKE CONNECT is used to remove a user ID from a database. REVOKE GROUP will automatically REVOKE MEMBERSHIP from all members of the group.

Automatic commit.

Standards

Permissions

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

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

See also