Revoking Privileges

---

Privileges can only be revoked by the grantor. Care must be taken when revoking privileges, especially when those privileges were granted `with grant option'. Revoking such privileges from an ident can have recursive effects on all idents who have been granted privileges by that ident. See Recursive Effects of Revoking Privileges for details.

The keywords CASCADE and RESTRICT can be used in the REVOKE statements to control whether the recursive effects should be allowed or not. If RESTRICT (the default) is specified and any recursive effects are identified the whole revoke operation will fail, leaving all objects intact. If the keyword CASCADE is specified, the revoke operation will proceed with recursive effects.

Privileges granted to a group cannot be revoked separately from individual members of the group. To revoke a group privilege from an individual, either revoke the privilege from the group or revoke the membership of the individual in the group.

If a privilege has been granted with the WITH GRANT OPTION it is possible to revoke the grant option only. That is, the ident looses the right to grant the privilege to other idents. The ident still has the privilege, but privileges granted to other idents are revoked.

Revoking System Privileges

Revoking system privileges does not affect objects already created under the authorization of the privilege.

The following examples show how to revoke system privileges.

Take away the privilege to create new databanks from the ident MIMER_STORE:

 REVOKE DATABANK FROM mimer_store RESTRICT;

Take away the privilege to create new idents from the ident MIMER_STORE:

  REVOKE IDENT FROM mimer_store RESTRICT;

Revoking Object Privileges

The following examples show how to revoke object privileges.

Take away the privilege to execute the COMING_SOON procedure from MIMER_WEB:

 REVOKE EXECUTE ON PROCEDURE coming_soon FROM mimer_web;

Take away MIMER_ADM's membership of the MIMER_ADMIN_GROUP group:

 REVOKE MEMBER ON mimer_admin_group FROM mimer_adm;

Take away the right to use the domain NAME from the group MIMER_ADMIN_GROUP:

 REVOKE USAGE ON DOMAIN name FROM mimer_admin_group;
 

Note: Revoking usage on domain prevents the ident from using that domain as a data type in new definitions, any existing definitions created by the ident will remain unaffected.

Revoking Access Privileges

The following examples show how to revoke access privileges.

Revoke the privilege to read and update rows from the PRODUCERS table from the group MIMER_STORE_GROUP:

 REVOKE SELECT, UPDATE ON producers FROM mimer_admin_group;
 

When the REFERENCES privilege on a table is taken away from an ident, all foreign key links referencing that table are removed.

Revoke the right to use columns in the ITEMS table as a foreign key from MIMER_ADM:

 REVOKE REFERENCES ON mimer_store.items FROM mimer_adm RESTRICT;

Revoke the right to grant select on the COUNTRIES table. Any grants that members of the group have made will also be revoked:

 REVOKE GRANT OPTION FOR SELECT ON countries
    FROM mimer_user_group CASCADE;

The Keyword ALL

The keyword ALL may be used as a shorthand for all the privileges that may be revoked in the current context.

Recursive Effects of Revoking Privileges

If CASCADE is specified in a REVOKE statement, the following recursive effects may occur:

• If a privilege WITH GRANT OPTION is revoked from an ident, all instances of that privilege granted to other idents under the authorization of the WITH GRANT OPTION are also revoked. Privileges granted for procedures, functions and triggers that reference objects accessed by the WITH GRANT OPTION will also disappear.
• If SELECT privilege on a table is revoked from an ident, views created by the ident under the authorization of that SELECT privilege are dropped.
• If REFERENCE privilege on a table is revoked from an ident, any FOREIGN KEY constraints in tables created by that ident under the authorization of that REFERENCE privilege are removed.
• If the privilege held by an ident on an object referenced in a routine or trigger is revoked, the routine or trigger will be dropped. (This applies to EXECUTE on a routine, USAGE on a sequence or an access privilege on a table or view held WITH GRANT OPTION).

Dependencies

The recursive effect of revoking a privilege depends on how many instances of that privilege have been granted. An ident will hold more than one instance of a privilege when it has been granted more than once (by different idents, as an ident cannot grant the same privilege to the same ident more than once).

One or more of those instances may have been granted WITH GRANT OPTION.

The data dictionary keeps a record of which instance of a privilege has WITH GRANT OPTION and which does not.

The recursive effects will occur only when the last instance of the required privilege is revoked. That is, when the last instance of the privilege held WITH GRANT OPTION is revoked from an ident, all instances of the ident granting the privilege to others will be withdrawn; and when the last instance of the privilege is revoked from the ident, the cascade effects of the ident no longer holding the privilege will occur.

This is illustrated in the example cases that follow:

CASE 1

1. A grants with grant option to M

   M grants to X

2. B grants with grant option to M

   M grants to Y

3. A revokes from M

   Both X and Y keep privileges

4. B revokes from M

   Both X and Y lose privileges

CASE 2

1. A grants with grant option to M
2. B grants without grant option to M

   M grants to X

   M grants to Y

3. A revokes from M

   M loses grant option

   Both X and Y lose privileges

4. B revokes from M

   M loses privilege

As a consequence of the cascading effects of revoking privileges, careful advance planning of the hierarchical structure of idents in a system can be essential to the long term viability of the system.

An unplanned ident structure can easily become impossible to overview and control after a relatively short period of system use.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se