The FROM Clause

You can specify the PUBLIC keyword to revoke the specified fragment-level privileges from PUBLIC, thereby revoking the privileges from all users to whom the privileges have not been explicitly granted, or who do not hold a role through which they have received the privileges.

If you enclose user or role in quotation marks, the name is case sensitive and is stored exactly as you typed it. In an ANSI-compliant database, if you do not use quotes around user or around role, the name is stored in uppercase letters.

When you include a role in the FROM clause of REVOKE FRAGMENT, the specified fragment privilege is revoked from that role. Users who have that role, however, retain any fragment privileges they hold that were granted to them individually or to PUBLIC.

Fragment-Level Privileges

The keyword or keywords that follow the FRAGMENT keyword specify fragment-level privileges, which are a logical subset of table-level privileges:

Fragment-Level Privileges:
 
|--+-ALL------------+-------------------------------------------|
   | .-,----------. |
   | V            | |
   '---+-INSERT-+-+-'
       +-DELETE-+
       '-UPDATE-'

You can revoke fragment-level privileges individually or in combination. The following keywords specify the fragment-level privileges that you can revoke.

Keyword: Effect
INSERT: Prevents the user from inserting rows in the fragment
DELETE: Prevents the user from deleting rows in the fragment
UPDATE: Prevents the user from updating rows in the fragment
ALL: Cancels Insert, Delete, and Update privileges on a fragment

If you specify the ALL keyword in a REVOKE FRAGMENT statement, the specified users and roles lose all fragment-level privileges that they currently possess on the specified fragments. For example, assume that a user currently has the Update privilege on one fragment of a table. If you use the ALL keyword to revoke all current privileges on this fragment from this user, the user loses the Update privilege that he or she had on this fragment.

For the distinction between fragment-level and table-level privileges, see the sections Definition of Fragment-Level Authorization and Effect of Fragment-Level Authorization in Statement Validation.

The AS Clause

Without the AS clause, the user who executes the REVOKE statement must be a grantor of the privilege that is being revoked. The DBA or the owner of the fragment can use the AS clause to specify another user (who must be the grantor of the privilege) as the revoker of privileges on a fragment.

The AS clause provides the only mechanism by which privileges can be revoked on a fragment whose owner is an authorization identifier that is not a valid user account known to the operating system.

Examples of the REVOKE FRAGMENT Statement

Examples that follow are based on the customer table. They all assume that the customer table is fragmented by expression into three fragments that reside in partitions that are named part1, part2, and part3.