Effect of CASCADE Keyword on UNDER Privileges (IDS)

If you revoke the Under privilege on a typed table with the CASCADE option, the Under privilege is removed from the specified user, and any subtables created under the typed table by that user are dropped from the database.

If you revoke the Under privilege on a named ROW type with the CASCADE option when that data type is in use, the REVOKE fails. This exception to the default behavior of the CASCADE option occurs because the database server supports the DROP ROW TYPE statement with the RESTRICT keyword only.

For example, assume that user jeff creates a ROW type named rtype1 and grants the Under privilege on that ROW type to user mary. User mary now creates a ROW type named rtype2 under ROW type rtype1 and grants the Under privilege on ROW type rtype2 to user andy. Then user andy creates a ROW type named rtype3 under ROW type rtype2.

If user jeff now tries to revoke the Under privilege on ROW type rtype1 from user mary with the CASCADE option, the REVOKE statement fails, because ROW type rtype2 is still in use by ROW type rtype3.

Controlling the Scope of REVOKE with the RESTRICT Option

The RESTRICT keyword causes the REVOKE statement to fail when any of the following dependencies exist:

A view depends on a Select privilege that you are attempting to revoke.
A foreign-key constraint depends on a References privilege that you attempt to revoke.
You attempt to revoke a privilege from a user who subsequently granted this privilege to another user or to a role.

REVOKE does not fail if it specifies a user who has the right to grant the privilege to others but has not exercised that right. For example, assume that user clara specifies WITH GRANT OPTION when she grants the Select privilege on the customer table to user ted. Further assume that user ted, in turn, grants the Select privilege on the customer table to user tania. The following statement that clara issued has no effect, because ted has used his authority to grant the Select privilege:

REVOKE SELECT ON customer FROM ted RESTRICT

In contrast, if user ted does not grant the Select privilege to tania or to any other user, the same REVOKE statement succeeds. Even if ted does grant the Select privilege to another user, either of the following statements succeeds:

REVOKE SELECT ON customer FROM ted CASCADE
REVOKE SELECT ON customer FROM ted

Effect of Uncommitted Transactions

The REVOKE statement places an exclusive row lock on the entry in the systables system catalog table for the table on which privileges are revoked. This lock is not released until the transaction that contains the REVOKE statement terminates. When another transaction attempts to prepare a SELECT statement against this table while the first transaction is open, the concurrent transaction fails, because the systables row for the specified table remains exclusively locked. The attempt to prepare the SELECT statement cannot succeed until after the first transaction is either committed or rolled back.

Related Information

Related Statements: GRANT, GRANT FRAGMENT, and REVOKE FRAGMENT

For information about roles, see the following statements: CREATE ROLE, DROP ROLE , and SET ROLE .

For a discussion of privileges, see the IBM Informix Database Design and Implementation Guide.

For a discussion of how to embed GRANT and REVOKE statements in programs, see the IBM Informix Guide to SQL: Tutorial.