REVOKE ACCESS PRIVILEGE
Revokes access privileges on a table or view, from one or more idents.
The specified access privileges are revoked from the ident(s) listed. If the privileges are revoked from a
GROUPident, 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).
GRANT OPTION FORclause specifies that only the
WITH GRANT OPTIONis to be revoked from the specified instance(s) of the privilege(s).
RESTRICTspecify whether the
REVOKEstatement will allow the recursive effects that cause views to be dropped or
FOREIGN KEYconstraints to be removed, as a result of the
REVOKEstatement. Refer to the Notes section below for details of the recursive effects. If
CASCADEis specified, such recursive effects will be allowed. If
RESTRICTis specified, the
REVOKEstatement will return an error if it would cause such recursive effects and then no access privileges will be revoked.
RESTRICTis specified, then
Privileges may only be revoked explicitly by the grantor.
If an access privilege has been granted to the same ident more than once (by different grantors), the
REVOKEstatement will only revoke (or will revoke the
WITH GRANT OPTIONfrom) 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.
SELECTaccess 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.
REFERENCESaccess 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.
REFERENCESaccess on an entire table or on one or more explicitly specified columns of the table is revoked, any
FOREIGN KEYconstraints in tables created by that ident under the authorization of that privilege are removed.
UPDATEaccess 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.
UPDATEaccess 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 OPTIONis 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.
ExampleREVOKE INSERT ON countries FROM joe RESTRICT;
For more information, see the Mimer SQL User's Manual, Revoking Access Privileges,
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40