Effect of the ALL Keyword

The ALL keyword revokes all table-level privileges. If any or all of the table-level privileges do not exist for the revokee, REVOKE with the ALL keyword executes successfully but returns the following SQLSTATE code:

01006--Privilege not revoked

For example, assume that user hal has the Select and Insert privileges on the customer table. User jocelyn wants to revoke all seven table-level privileges from user hal. So user jocelyn issues the following REVOKE statement:

REVOKE ALL ON customer FROM hal

This statement executes successfully but returns SQLSTATE code 01006. The SQLSTATE warning is returned with a successful statement, as follows:

• The statement succeeds in revoking the Select and Insert privileges from user hal because user hal had those privileges.
• SQLSTATE code 01006 is returned because user hal lacked other privileges implied by the ALL keyword, so these privileges were not revoked.

The ALL keyword instructs the database server to revoke everything possible, including nothing. If the user from whom privileges are revoked has no privileges on the table, the REVOKE ALL statement still succeeds, because it revokes everything possible from the user (in this case, no privileges at all).

Effect of the ALL Keyword on UNDER Privilege (IDS)

If you revoke ALL privileges on a typed table, the Under privilege is included in the privileges that are revoked. If you revoke ALL privileges on a table that is not based on a ROW type, the Under privilege is not included among the privileges that are revoked. (The Under privilege can be granted only on a typed table.)

Type-Level Privileges (IDS)

Type-Level Privileges:
 
|--+-USAGE ON TYPE--type_name-+---------------------------------|
   '-UNDER ON TYPE--row_type--'