REVOKE

The REVOKE statement revokes privileges. It removes database privileges or role access granted to the specified users, groups, roles, or PUBLIC. (To confer privileges, use the GRANT statement.) You cannot revoke privileges granted by other users.

[EXEC SQL] REVOKE [GRANT OPTION FOR]
              ALL [PRIVILEGES] | privilege {, privilege} | role {, role}
              [ON [object_type] [schema.]objectname {, [schema.]object_name} |
                            CURRENT INSTALLATION]
              FROM PUBLIC | [auth_type] auth_id {, auth_id}
              [CASCADE | RESTRICT];

Specifies the privileges to revoke. To revoke all privileges, use ALL. The privileges must agree with the object_type as follows:

Object Type	Valid Privileges
Table (omit object_type)	COPY_INTO COPY_FROM DELETE EXCLUDING INSERT REFERENCES SELECT UPDATE
DATABASE (or CURRENT INSTALLATION)	[NO]ACCESS [NO]CONNECT_TIME_LIMIT [NO]CREATE_TABLE [NO]DB_ADMIN [NO]IDLE_TIME_LIMIT [NO]QUERY_COST_LIMIT [NO]QUERY_CPU_LIMIT [NO]QUERY_PAGE_LIMIT [NO]SELECT_SYSCAT [NO]SESSION_PRIORITY [NO]TIMEOUT_ABORT [NO]UPDATE_SYSCAT
PROCEDURE	EXECUTE
DBEVENT	REGISTER RAISE
SEQUENCE	NEXT
Role	Omit this clause

Specifies the type of object on which the privileges were granted. To revoke permission on a table, omit the object_type parameter. Valid object_types are:

Specifies the name of the table, database procedure, database event, or role on which the privileges were granted.

Specifies the type of authorization identifier to which privileges were granted. Auth_type must be USER, GROUP, or ROLE. The default is USER. More than one auth_type cannot be specified.

Specifies the users, groups, or roles from which privileges are being revoked. The auth_ids must agree with the type specified by the auth_type.

For example, if you specify GROUP as auth_type, the auth_id list must be a list of group identifiers. If you specify PUBLIC for the auth_id, you must omit auth_type. You can revoke from users and PUBLIC in the same REVOKE statement.

Revoking a database privilege makes that privilege on the specified database undefined for the specified grantee (auth_id). If an attempt is made to revoke a privilege that was not granted to a specified auth_id, no changes are made to the privileges of that auth_id.

Privileges granted on specific databases are not affected by REVOKE...ON CURRENT INSTALLATION, and privileges granted on current installation are not affected by REVOKE...ON DATABASE. Revoking privileges from PUBLIC does not affect privileges granted to a specific user.

If a privilege was granted using its “no” form (for example, NOCREATE_TABLE), the same form must be used when revoking the privilege. For example, the following grant prevents a user from creating tables:

Note: In some cases granting a database privilege imposes a restriction, and revoking the privilege removes the restriction. For example, GRANT NOCREATE_TABLE prevents the user from creating tables.