Informix Guide to SQL: Syntax SQL Statements

Use the GRANT FRAGMENT statement to grant Insert, Update, and Delete privileges on individual fragments of a fragmented table.

The GRANT FRAGMENT statement is similar to the GRANT statement. Both statements grant privileges to users. The difference between the two statements is that you use GRANT to grant privileges on a table while you use GRANT FRAGMENT to grant privileges on table fragments.

Use the GRANT FRAGMENT statement to grant the Insert, Update, or Delete privilege on one or more fragments of a table to one or more users.

The GRANT FRAGMENT statement is valid only for tables that are fragmented according to an expression-based distribution scheme. For an explanation of expression-based distribution schemes, see Syntax.

The following table defines each of the fragment-level privileges.

When a fragmented table is created in an ANSI-compliant database, the table owner implicitly receives all table-level privileges on the new table, but no other users receive privileges.

When a fragmented table is created in a database that is not ANSI compliant, the table owner implicitly receives all table-level privileges on the new table, and other users (that is, PUBLIC) receive the following default set of privileges on the table: Select, Update, Insert, Delete, and Index. The privileges granted to PUBLIC are explicitly recorded in the systabauth system catalog table.

A user who has table privileges on a fragmented table has the privileges implicitly on all fragments of the table. These privileges are not recorded in the sysfragauth system catalog table.

Whether or not the database is ANSI compliant, you can use the GRANT FRAGMENT statement to grant explicit Insert, Update, and Delete privileges on one or more fragments of a table that is fragmented by expression. The privileges granted by the GRANT FRAGMENT statement are explicitly recorded in the sysfragauth system catalog table.

The Insert, Update, and Delete privileges that are conferred on table fragments by the GRANT FRAGMENT statement are collectively known as fragment-level privileges or fragment-level authority.

Fragment-level authority lets users execute INSERT, DELETE, and UPDATE statements on table fragments even if they lack Insert, Update, and Delete privileges on the table as a whole. Users who lack privileges at the table level can insert, delete, and update rows in authorized fragments because of the algorithm by which the database server validates commands. This algorithm consists of the following checks:

1. When a user executes an INSERT, DELETE, or UPDATE statement, the database server first checks whether the user has the table authority necessary for the operation attempted. If the table authority exists, the command continues processing.
2. If the table authority does not exist, the database server checks whether the table is fragmented by expression. If the table is not fragmented by expression, the database server returns an error to the user. This error indicates that the user does not have the privilege to execute the command.
3. If the table is fragmented by expression, the database server checks whether the user has the fragment authority necessary for the operation attempted. If the fragment authority exists, the command continues processing. If the fragment authority does not exist, the database server returns an error to the user. This error indicates that the user does not have the privilege to execute the command.

The duration of fragment-level authority is tied to the duration of the fragmentation strategy for the table as a whole.

If you drop a fragmentation strategy by means of a DROP TABLE statement or the INIT, DROP, or DETACH clauses of an ALTER FRAGMENT statement, you also drop any authorities that exist for the affected fragments. Similarly, if you drop a dbspace, you also drop any authorities that exist for the fragment that resides in that dbspace.

Tables that are created as a result of a DETACH or INIT clause of an ALTER FRAGMENT statement do not keep the authorities that the former fragment or fragments had when they were part of the fragmented table. Instead, such tables assume the default table authorities.

If a table with fragment authorities defined on it is changed to a table with a round-robin strategy or some other expression strategy, the fragment authorities are also dropped, and the table assumes the default table authorities.

You can grant fragment-level privileges on one fragment of a table or on a list of fragments.

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

GRANT FRAGMENT ALL ON customer (dbsp1) TO larry

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

GRANT FRAGMENT ALL ON customer (dbsp1, dbsp2) TO millie

If you want 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. However, 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 dbsp1, dbsp2, and dbsp3. You can use either of the following statements to grant the Insert privilege on all fragments of the table to the user helen:

GRANT FRAGMENT INSERT ON customer (dbsp1, dbsp2, dbsp3)

TO helen;



GRANT INSERT ON customer TO helen;

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 dbsp3 to the user oswald:

GRANT FRAGMENT ALL ON customer (dbsp3) TO oswald

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

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

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 dbsp1 to the user ed:

GRANT FRAGMENT UPDATE ON customer (dbsp1) TO ed

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

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

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

GRANT FRAGMENT ALL ON customer (dbsp1) TO harry

By including the WITH GRANT OPTION clause in the GRANT FRAGMENT statement, you convey the specified fragment-level privileges to a user and the right to grant those same privileges to other users.

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

GRANT FRAGMENT UPDATE ON customer (dbsp3) TO george

	WITH GRANT OPTION

The AS grantor clause is optional in a GRANT FRAGMENT statement. Use this clause to specify the grantor of the privilege.

When you include the AS grantor clause in the GRANT FRAGMENT statement, you specify that the user who is named as grantor is listed as the grantor of the privilege in the grantor column of the sysfragauth system catalog table.

In the following example, the DBA grants the Delete privilege on the fragment of the customer table in dbsp3 to the user martha. In the GRANT FRAGMENT statement, the DBA uses the AS grantor clause to specify that the user jack is listed as the grantor of the privilege in the sysfragauth system catalog table.

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

When a GRANT FRAGMENT statement does not include the AS grantor clause, the user who issues the statement is the default grantor of the privileges that are specified in the statement.

In the following example, the user grants the Update privilege on the fragment of the customer table in dbsp3 to the user fred. Because this statement does not specify the AS grantor clause, the user who issues the statement is listed by default as the grantor of the privilege in the sysfragauth system catalog table.

GRANT FRAGMENT UPDATE ON customer (dbsp3) TO fred

If you omit the AS grantor clause, or if you specify your own user name in the grantor parameter, you can later revoke the privilege that you granted to the specified user. However, if you specify someone other than yourself as the grantor of the specified privilege to the specified user, only that grantor can revoke the privilege from the user.

For example, if you grant the Delete privilege on the fragment of the customer table in dbsp3 to user martha but specify user jack as the grantor of the privilege, user jack can revoke that privilege from user martha, but you cannot revoke that privilege from user martha.

Related statements: GRANT and REVOKE FRAGMENT

For a discussion of fragment-level and table-level privileges, see the Informix Guide to Database Design and Implementation.