<img src="02recvr10.gif" height="49" width="100"> REVOKE FRAGMENT

Informix Guide to SQL: Syntax
SQL Statements

REVOKE FRAGMENT

Use the REVOKE FRAGMENT statement to revoke privileges that were granted on individual fragments of a fragmented table. You can use this statement to revoke the Insert, Update, and Delete fragment-level privileges from users.

Syntax

Element Purpose Restrictions Syntax

dbspace Name of the dbspace where the fragment is stored Use this parameter to specify the fragment or fragments on which privileges are to be revoked. If you do not specify a fragment, the `REVOKE` statement applies to all fragments in the specified table that have the specified privileges. The specified dbspace or dbspaces must exist. Identifier, p. 4-205

table Name of the table that contains the fragment or fragments on which privileges are to be revoked No default value exists. The specified table must exist and must be fragmented by expression. Database Object Name, p. 4-50

user Name of the user or users from whom the specified privileges are to be revoked No default value exists. The user must be a valid user. The name must conform to the conventions of your operating system.

Element	Purpose	Restrictions	Syntax
dbspace	Name of the dbspace where the fragment is stored Use this parameter to specify the fragment or fragments on which privileges are to be revoked. If you do not specify a fragment, the `REVOKE` statement applies to all fragments in the specified table that have the specified privileges.	The specified dbspace or dbspaces must exist.	Identifier, p. 4-205
table	Name of the table that contains the fragment or fragments on which privileges are to be revoked No default value exists.	The specified table must exist and must be fragmented by expression.	Database Object Name, p. 4-50
user	Name of the user or users from whom the specified privileges are to be revoked No default value exists.	The user must be a valid user.	The name must conform to the conventions of your operating system.

Usage

Use the REVOKE FRAGMENT statement to revoke the Insert, Update, or Delete privilege on one or more fragments of a fragmented table from one or more users.

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

You can specify one fragment or a list of fragments in the REVOKE FRAGMENT statement. To specify a fragment, name the dbspace in which the fragment resides.

You do not have to specify a particular fragment or a list of fragments in the REVOKE FRAGMENT statement. If you do not specify any fragments in the statement, the specified users lose the specified privileges on all fragments for which the users currently have those privileges.

Fragment-Level Privileges

You can revoke fragment-level privileges individually or in combination. List the keywords that correspond to the privileges that you are revoking from user. The following table defines each of the fragment-level privileges.


Privilege	Purpose
ALL	Provides insert, delete, and update privileges on a fragment
INSERT	Lets you insert rows in the fragment
DELETE	Lets you delete rows in the fragment
UPDATE	Lets you update rows in the fragment and to name any column of the table in an UPDATE statement

If you specify the ALL keyword in a REVOKE FRAGMENT statement, the specified users lose all fragment-level privileges that they currently have 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.

Examples of the REVOKE FRAGMENT Statement

The examples that follow are based on the customer table. All the examples assume that the customer table is fragmented by expression into three fragments that reside in the dbspaces that are named dbsp1, dbsp2, and dbsp3.

Revoking One Privilege

The following statement revokes the Update privilege on the fragment of the customer table in dbsp1 from the user ed:

REVOKE FRAGMENT UPDATE ON customer (dbsp1) FROM ed

Revoking More Than One Privilege

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

REVOKE FRAGMENT UPDATE, INSERT ON customer (dbsp1) FROM susan

Revoking All Privileges

The following statement revokes all privileges currently granted to the user harry on the fragment of the customer table in dbsp1.:

REVOKE FRAGMENT ALL ON customer (dbsp1) FROM harry

Revoking Privileges on More Than One Fragment

The following statement revokes all privileges currently granted to the user millie on the fragments of the customer table in dbsp1 and dbsp2:

REVOKE FRAGMENT ALL ON customer (dbsp1, dbsp2) FROM millie

Revoking Privileges from More Than One User

The following statement revokes all privileges currently granted to the users jerome and hilda on the fragment of the customer table in dbsp3:

REVOKE FRAGMENT ALL ON customer (dbsp3) FROM jerome, hilda

Revoking Privileges Without Specifying Fragments

The following statement revokes all current privileges from the user mel on all fragments for which this user currently has privileges:

REVOKE FRAGMENT ALL ON customer FROM mel

Related Information

Related statements: GRANT FRAGMENT and REVOKE

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

Informix Guide to SQL: SyntaxSQL Statements