Effect of the ALL Keyword

The ALL keyword grants all possible table-level privileges to the specified user. If any or all of the table-level privileges do not exist for the grantor, the GRANT statement with the ALL keyword succeeds (in the sense of SQLCODE being set to zero, even if the possible privileges are an empty set for the grantor on the table). In this case, however, the following SQLSTATE warning is returned: 
01007 - Privilege not granted.

For example, assume that user ted has the Select and Insert privileges on the customer table with the authority to grant those privileges to other users.

User ted wants to grant all table-level privileges to user tania. So user ted issues the following GRANT statement: 
GRANT ALL ON customer TO tania;
This statement executes successfully but returns SQLSTATE code 01007 for the following reasons:
  • The statement succeeds in granting the Select and Insert privileges to user tania because user ted has those privileges and the right to grant those privileges to other users.
  • The other privileges implied by the ALL keyword were not grantable by user ted and, therefore, were not granted to user tania.

If you grant all table-level privileges with the ALL keyword, the privileges includes the Under privilege only if the table is a typed table. The grant of ALL privileges does not include the Under privilege if the table is not based on a ROW type.

If the table owner grants ALL privileges on a traditional relational table and later changes that table to a typed table, the table owner must explicitly grant the Under privilege to allow other users to create subtables under it.