revoke

Description

Revokes authorizations from users.

Syntax

revoke [grant option for]{all [privileges]| permission_list}
on {table_name | view_name} | stored_procedure_name}
from {public | name_list} [cascade] revoke {all [privileges] | command_list}
from {public | name_list}

Parameters

all: (in the first syntax format) specifies that all privileges applicable to the specified object are revoked when used to revoke authorizations to access database objects. The second syntax format can revoke create command authorizations:

When the System Administrator uses this command, all create authorizations are revoked.

When the database owner uses this command, all create authorizations are revoked except create database.

permission_list: is a list of authorizations to be revoked.

command_list: is a list of commands for which authorizations are to be revoked.

table_name: is the name of the specified table in the database.

view_name: is the name of a view in the current database. Only one view can be listed for each revoke statement.

stored_procedure: is the name of a stored procedure in the database. Only one object can be listed for each revoke statement.

public: is all users of the “public” group, which includes all users of the system.

name_list: is a list of users’ database names and group names, separated by commas.

grant option for: prohibits the users specified in name_list from granting the privileges specified by permission_list to other users.

cascade: revokes grant authorization for the privileges specified in permission_list from the users specified in name_list, and from all users to whom they granted privileges.

The cascading effect can happen even if it is not specified by the user. For example, UserA has granted UserB some privileges, and in turn, UserB granted some to UserC. If UserA is revoked, all privileges that UserA granted to UserB and UserB indirectly granted to UserC are revoked.

Consult the IBM DB2 SQL Reference manual to see how IBM implements DB2 authorization schemes.

Examples

Example 1

revoke insert, delete
 on titles
 from mary, sales

Example 2

revoke all on titles
 from public

Usage

DB2 does not support the revoking of a stored procedure.
Authorizations can only be revoked on objects in the current database.
grant and revoke commands are order-sensitive. When a conflict occurs, the most recently issued command takes effect.
The word to can be substituted for the word from in the revoke syntax.
The DB2 access service does not support role_name.