|
|
|
|
|
|
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:
- 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. All procedures, functions and triggers that reference objects accessed by the WITH GRANT OPTION 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
CASE 2
- A grants with grant option to M
- B grants without grant option to M
- A revokes from M
- B revokes from M
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 |
|
|
|
|
|
|