Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


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, but he still has the privilege.

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 HOTELADM:
 REVOKE  DATABANK
   FROM  HOTELADM RESTRICT;
Take away the privilege to create new idents from the idents AUDIT and ECONOMY_DEPT:
  REVOKE  IDENT
    FROM  AUDIT, ECONOMY_DEPT RESTRICT;

Revoking Object Privileges

The following examples show how to revoke object privileges.

Take away the privilege to execute the ALLOCATE_ROOM procedure from STEVE and MARIANNE:
 REVOKE  EXECUTE ON PROCEDURE ALLOCATE_ROOM
   FROM  STEVE, MARIANNE RESTRICT;
Take away the privilege to enter the AUDIT program from the ident ECONOMY_DEPT:
 REVOKE  EXECUTE ON PROGRAM AUDIT
   FROM  ECONOMY_DEPT RESTRICT;
Take away the idents' STEVE, MARIANNE and JAMES memberships in the group ECONOMY_DEPT:
 REVOKE  MEMBER ON ECONOMY_DEPT
   FROM  STEVE, MARIANNE, JAMES RESTRICT;
Take away the right to use the domain BOOK_RATE from the ident ECONOMY_DEPT:
 REVOKE  USAGE ON DOMAIN BOOK_RATE
   FROM  ECONOMY_DEPT RESTRICT;
 
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 privileges to delete and insert rows and to retrieve data from the BOOK_GUEST table from the ident MARIANNE:
 REVOKE  SELECT, DELETE, INSERT ON BOOK_GUEST
   FROM  MARIANNE RESTRICT;
 

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 ROOMS as foreign keys from ECONOMY_DEPT.
 REVOKE  REFERENCES
     ON  ROOMS
   FROM  ECONOMY_DEPT RESTRICT;
Revoke the right to grant select on the BOOK_GUEST table from JAMES. Any grants that JAMES has made will also be revoked.
 REVOKE  GRANT OPTION FOR SELECT
     ON  BOOK_GUEST
   FROM  JAMES 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:

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.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX