REVOKE ACCESS PRIVILEGE

Revokes access privileges on a table or view, from one or more idents.

 

where access is:

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

The specified access privileges are revoked from the ident(s) listed. If the privileges are revoked from a GROUP ident, all members of the group lose the privileges.

The access privileges are described under GRANT ACCESS PRIVILEGE.

The access privileges may be revoked in any combination. Specification of the keyword ALL (followed by the optional keyword PRIVILEGES) instead of an explicit list of privileges results in all access privileges on the table or view being revoked from the specified ident(s).

The GRANT OPTION FOR clause specifies that only the WITH GRANT OPTION is to be revoked from the specified instance(s) of the privilege(s).

The keywords CASCADE and RESTRICT specify whether the REVOKE statement will allow the recursive effects that cause views to be dropped or FOREIGN KEY constraints to be removed, as a result of the REVOKE statement. Refer to the Notes section below for details of the recursive effects. If CASCADE is specified, such recursive effects will be allowed. If RESTRICT is specified, the REVOKE statement will return an error if it would cause such recursive effects and then no access privileges will be revoked.

If neither CASCADE nor RESTRICT is specified, then RESTRICT is implicit.

Restrictions

Privileges may only be revoked explicitly by the grantor.

Notes

If an access privilege has been granted to the same ident more than once (by different grantors), the REVOKE statement will only revoke (or will revoke the WITH GRANT OPTION from) the single instance of the privilege that was granted by the current ident.

The access rights attached to the privilege (or the WITH GRANT OPTION) will only be lost when the last instance of the privilege has been revoked.

Revoking access privileges has recursive effects.

When SELECT access on a table or view is revoked, views based on that table or view and created under the authorization of that access, are recursively dropped.

When UPDATE, INSERT, DELETE or REFERENCES access on a table or view is revoked, the same privilege on views based on that table or view and created under the authorization of the access are recursively revoked.

When REFERENCES access on an entire table or on one or more explicitly specified columns of the table is revoked, any FOREIGN KEY constraints in tables created by that ident under the authorization of that privilege are removed.

When INSERT, REFERENCES or UPDATE access is revoked from one or more explicitly specified columns of a table or view, the same privilege on columns of views based on that table or view and created under the authorization of the access are recursively revoked.

Revoking INSERT, REFERENCES or UPDATE access from one or more explicitly specified columns of a table or view will not affect access held on other column(s) of that table or view. If the original access was granted on the entire table or view, the access will stay in effect at the table level and will, therefore, apply to any new columns added to the table.

When the last instance of the required access held by the creator of a routine or trigger on a table is revoked, any routines or triggers created by that ident which contain references to the table will be dropped.

When the last instance of a privilege WITH GRANT OPTION is revoked, all instances of the privilege granted by the ident under that authorization are recursively revoked.

An ident may not revoke access privileges from itself.

Example

REVOKE INSERT ON countries FROM joe RESTRICT;

 

For more information, see the Mimer SQL User's Manual, Revoking Access Privileges,

Standard Compliance