REVOKE revokes the following privileges from authorization identifiers:
Table and column privileges
Special privileges
EXECUTE privileges for routines
Only the authorization identifier that granted a privilege (the “grantor”) can revoke that privilege from an authorization identifier (see section "GRANT - Grant privileges").
The TABLE_PRIVILEGES, COLUMN_PRIVILEGES, USAGE_PRIVILEGES, CATALOG_PRIVILEGES and ROUTINE_PRIVILEGES tables of the INFORMATION_SCHEMA provide you with information on the privileges assigned to the authorization identifiers (see chapter "Information schemas").
The REVOKE statement has several formats. Examples are provided under the format concerned.
See also
GRANT
REVOKE format for table and column privileges
REVOKE { ALL PRIVILEGES | table_and_column_privilege ,...}
ON [TABLE] table
FROM { PUBLIC | authorization_identifier },...
{ RESTRICT | CASCADE }
table_and_column_privilege ::=
{
SELECT |
DELETE |
INSERT |
UPDATE [( column ,...)] |
REFERENCES [( column ,...)]
}
ALL PRIVILEGES
All the table privileges that the current authorization identifier can revoke are revoked. ALL PRIVILEGES comprises the privileges SELECT, DELETE, INSERT, UPDATE and REFERENCES.
table_and_column_privilege
The table and column privileges are revoked individually. You can specify more than one privilege.
ON [TABLE] table
Name of the table for which you want to revoke privileges.
The table can be a base table or a view. You can only revoke the SELECT privilege for a table that cannot be updated.
FROM PUBLIC
The privileges are revoked from all authorization identifiers. The individual privileges of the individual authorization identifiers are not affected.
FROM authorization_identifier
The privileges are revoked from the user with the authorization identifier authorization_identifier. You may specify more than one authorization identifier.
CASCADE
An authorization identifier can revoke any privileges it has granted:
All the specified privileges are revoked.
If a specified privilege has been forwarded to other authorization identifiers, all privileges forwarded directly or indirectly are deleted.
Views which were defined either directly or indirectly on the basis of the specified or forwarded privileges are deleted.
Referential constraints defined on the basis of the specified and forwarded privileges are deleted.
Routines which were defined either directly or indirectly on the basis of the specified and forwarded privileges are deleted.
RESTRICT
The following restrictions apply to the revoking of privileges:
A privilege forwarded to other authorization identifiers cannot be revoked for as long as a forwarded privilege like this still exists.
A privilege on the basis of which a view or referential constraint has been defined cannot be revoked if the view or referential constraint still exists.
A privilege on the basis of which a routine has been defined cannot be revoked if the routine still exists.
table_and_column_privilege
Specification of the individual table and column privileges.
SELECT
Privilege that allows rows in the table to be read.
DELETE
Privilege that allows rows to be deleted from the table.
INSERT
Privilege that allows rows to be inserted into the table.
UPDATE [(column,...)]
Privilege that allows rows in the table to be updated.
The revoke operation can be limited to the specified columns.
column must be the name of a column in the specified table. You can specify more than one column.
(column,...) omitted: The privilege for updating all the columns in the table is revoked.
REFERENCES [(column,...)]
Privilege that allows the definition of referential constraints that reference the table.
The revoke operation can be limited to the specified columns.
column must be the name of a column in the specified table. You can specify more than one column.
(column,...) omitted: The privilege for referencing all the columns in the table is revoked.
REVOKE format for special privileges
REVOKE { ALL SPECIAL PRIVILEGES | special_privilege ,...}
ON CATALOG { catalog | STOGROUP stogroup }
FROM { PUBLIC | authorization_identifier },...
{ RESTRICT | CASCADE }
special_privilege ::=
{
CREATE USER |
CREATE SCHEMA |
CREATE STOGROUP |
UTILITY |
USAGE
}
ALL SPECIAL PRIVILEGES
All the special privileges that the current authorization identifier may revoke are revoked. ALL SPECIAL PRIVILEGES revokes the special privileges.
special_privilege
The special privileges are revoked individually. You can specify more than one special privilege.
ON CATALOG catalog
Name of the database for which you want to revoke special privileges.
ON STOGROUP stogroup
Name of the storage group for which you want to revoke the USAGE privilege. You can qualify the name of the storage group with a database name.
FROM authorization_identifier
The privileges are revoked from the user with the authorization identifier authorization_identifier. You may specify more than one authorization identifier.
CASCADE
An authorization identifier can revoke any privileges it has granted:
All the specified privileges are revoked.
If a specified privilege has been forwarded to other authorization identifiers, all forwarded privileges are deleted implicitly.
RESTRICT
The following restrictions apply to the revoking of privileges:
A privilege forwarded to other authorization identifiers cannot be revoked for as long as a forwarded privilege like this still exists.
special_privilege
Specification of the individual special privileges.
CREATE USER
Special privilege that allows you to define authorization identifiers. You can only revoke the CREATE USER privilege for a database.
CREATE SCHEMA
Special privilege that allows you to define database schemas. You can only revoke the CREATE SCHEMA privilege for a database.
CREATE STOGROUP
Special privilege that allows you to define storage groups. You can only revoke the CREATE STOGROUP privilege for a database.
UTILITY
Special privilege that allows you to use utility statements. You can only revoke the UTILITY privilege for a database.
USAGE
Special privilege that allows you to use a storage group. You can only revoke the USAGE privilege for a storage group.
Example
Revoke the UPDATE privilege for all columns in the table DESCRIPTIONS from the authorization identifier UTIUSR2.
REVOKE UPDATE ON TABLE descriptions FROM utiusr2 RESTRICT
REVOKE format for EXECUTE privileges (procedure)
REVOKE EXECUTE ON SPECIFIC PROCEDURE procedure
FROM { PUBLIC | authorization_identifier },...
{ RESTRICT | CASCADE }
procedure ::= routine
EXECUTE ON SPECIFIC PROCEDURE procedure
Name of the procedure for which the privilege is to be revoked. You can qualify the procedure name with a database and schema name.
FROM authorization_identifier
The privileges are revoked from the user with the authorization identifier authorization_identifier. You may specify more than one authorization identifier.
CASCADE
An authorization identifier can revoke any privileges it has granted:
All the specified privileges are revoked.
If a specified privilege has been forwarded to other authorization identifiers, all privileges forwarded directly or indirectly are deleted.
Views which were defined either directly or indirectly on the basis of the specified or forwarded privileges are deleted.
Routines which were defined either directly or indirectly on the basis of the specified or forwarded privileges are deleted.
RESTRICT
The following restrictions apply to the revoking of privileges:
A privilege forwarded to other authorization identifiers cannot be revoked for as long as a forwarded privilege like this still exists.
A privilege on the basis of which a view has been defined cannot be revoked if the view still exists.
A privilege on the basis of which a routine has been defined cannot be revoked if the routine still exists.
REVOKE format for EXECUTE privileges (UDF)
REVOKE EXECUTE ON SPECIFIC FUNCTION udf
FROM { PUBLIC | authorization_identifier },...
{ RESTRICT | CASCADE }
udf ::= routine
EXECUTE ON SPECIFIC FUNCTION udf
Name of the UDF for which the privilege is to be revoked. You can qualify the unqualified UDF name with a database and schema name.
FROM
See "REVOKE - Revoke privileges".
CASCADE
An authorization identifier can revoke any privileges it has granted:
All the specified privileges are revoked.
If a specified privilege has been forwarded to other authorization identifiers, all forwarded privileges and all routines and views created on the basis of these privileges are deleted in a cascade.
Views defined on the basis of the specified privilege are deleted in a cascade.
Routines defined on the basis of this privilege are deleted in a cascade.
RESTRICT
The following restrictions apply to the revoking of privileges:
A privilege forwarded to other authorization identifiers cannot be revoked for as long as a forwarded privilege like this still exists.
A privilege on the basis of which a view has been defined cannot be revoked if the view still exists.
A privilege on the basis of which a routine has been defined cannot be revoked if the routine still exists.