The TO Clause

The list of one or more users or roles that follows the TO keyword identifies the grantees. You can specify the PUBLIC keyword to grant the specified fragment-level privileges to all users.

You cannot use GRANT FRAGMENT to grant fragment-level privileges to yourself, either directly or through roles.

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.

The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in part1 to user larry:

The following statement grants the Insert, Update, and Delete privileges on the fragments of the customer table in part1 and part2 to user millie:

To grant privileges on all fragments of a table to the same user or users, you can use the GRANT statement instead of the GRANT FRAGMENT statement. You can also use the GRANT FRAGMENT statement for this purpose.

Assume that the customer table is fragmented by expression into three fragments, and these fragments reside in the dbspaces named part1, part2, and part3. You can use either of the following statements to grant the Insert privilege on all fragments of the table to user helen:

Granting Privileges to One User or a List of Users

You can grant fragment-level privileges to a single user or to a list of users.

The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in part3 to user oswald:

GRANT FRAGMENT ALL ON customer (part3) TO oswald

The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in part3 to users jerome and hilda:

GRANT FRAGMENT ALL ON customer (part3) TO jerome, hilda

Granting One Privilege or a List of Privileges

When you specify fragment-level privileges in a GRANT FRAGMENT statement, you can specify one privilege, a list of privileges, or all privileges.

The following statement grants the Update privilege on the fragment of the customer table in part1 to user ed:

GRANT FRAGMENT UPDATE ON customer (part1) TO ed

The following statement grants the Update and Insert privileges on the fragment of the customer table in part1 to user susan:

GRANT FRAGMENT UPDATE, INSERT ON customer (part1) TO susan

The following statement grants the Insert, Update, and Delete privileges on the fragment of the customer table in part1 to user harry:

GRANT FRAGMENT ALL ON customer (part1) TO harry

WITH GRANT OPTION Clause

As in other GRANT statements, the WITH GRANT OPTION keywords specify that the grantee can grant the same fragment-level privileges to other users. WITH GRANT OPTION is not valid if the TO clause specifies a role as grantee. For additional information, see WITH GRANT OPTION Keywords.

The following statement grants the Update privilege on the fragment of the customer table in part3 to user george and also gives george the right to grant the Update privilege on the same fragment to other users:

GRANT FRAGMENT UPDATE ON customer (part3) TO george WITH GRANT OPTION

AS grantor Clause

The AS grantor clause of the GRANT FRAGMENT statement can specify the grantor of the privilege. You can use this clause only if you have the DBA privilege on the database. hen you include the AS grantor clause, the database server lists the user or role who is specified as grantor as the grantor of the privilege in the grantor column of the sysfragauth system catalog table.

In the next example, the DBA grants the Delete privilege on the fragment of the customer table in part3 to user martha, and uses the AS grantor clause to specify that user jack is listed in sysfragauth as the grantor of the privilege:

GRANT FRAGMENT DELETE ON customer (part3) TO martha AS jack

One effect of the AS grantor clause in the previous example is that user jack can execute the REVOKE FRAGMENT statement to cancel the Delete fragment-level privilege that martha holds, if this GRANT FRAGMENT statement were the only source of the fragment authority of martha on the customer rows in part3.