GRANT FRAGMENT

Use the GRANT FRAGMENT statement to grant to one or more users or roles any or all of the Insert, Update, and Delete access privileges on individual fragments of a table that has been fragmented by expression.

Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax

                                                 (1)
>>-GRANT FRAGMENT--| Fragment-Level Privileges |---------------->
 
                 .-,--------.
                 V          |
>--ON--table--(----fragment-+--)-------------------------------->
 
>--TO--+-+-PUBLIC---------+--+-------------------+-+--+---------------+-><
       | | .-,----------. |  '-WITH GRANT OPTION-' |  '-AS--'grantor'-'
       | | V            | |                        |
       | '-----'user'---+-'                        |
       | .-,------.                                |
       | V        |                                |
       '---'role'-+--------------------------------'

Notes:

See page Fragment-Level Privileges

Element	Description	Restrictions	Syntax
3fragment	Partition or dbspace that stores a fragment of table	Must exist; cannot be delimited by quotes	Identifier, p. Identifier
grantor	User who can revoke the privileges	Same as for user	Owner Name, p. Owner Name
role	Role to receive privileges	Must exist in sysusers	Owner Name, p. Owner Name
table	Fragmented table on which fragment privileges are granted	Must exist and must be fragmented by expression	Database Object Name, p. Database Object Name
user	User to whom privileges are to be granted	Must be a valid authorization identifier	Owner Name, p. Owner Name

Usage

The GRANT FRAGMENT statement is a special case of the GRANT statement for assigning privileges on table fragments. GRANT FRAGMENT is valid only for tables that are fragmented according to an expression-based distribution scheme. For an explanation of this type of fragmentation strategy, see Expression Distribution Scheme.

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-'

These keywords correspond to the following fragment-level privileges:

Keyword: Effect on Grantee
ALL: Receives Insert, Delete, and Update privileges on the fragment
INSERT: Can insert rows into the fragment
DELETE: Can delete rows from the fragment
UPDATE: Can update rows in the fragment and in any columns.