With the SQL statement REVOKE, users can revoke privileges they have granted. In order to guarantee consistent access protection, SQL requires that users comply with certain rules when revoking privileges.
The SQL statement REVOKE can be represented schematically as follows:
REVOKE privilege(s) ON object FROM grantee(s) { CASCADE | RESTRICT }
Users use REVOKE to revoke the specified privileges previously granted to “grantees”. Depending on the privilege, the “object” can be a table, a database, or a storage group. A privilege may only be revoked from a grantee by one user, namely the user who originally granted the privilege.
REVOKE ... FROM PUBLIC allows users to revoke a specific privilege from all other users, provided they granted the privilege with GRANT... TO PUBLIC in the first place.
REVOKE ... RESTRICT only allows a user to revoke the specified privileges from a grantee if the grantee has not in turn granted the privileges to other users or if these other users no longer possess the privileges. If privileges have been passed on in this way, the privileges specified with REVOKE are not revoked and an error message is issued.
REVOKE ... CASCADE on the other hand, allows a user to revoke the specified privileges from the grantees in all cases. In this case, all the those privileges are revoked which were passed on to other users by the grantees on the basis of the specified privileges.
The user can use the tables of the INFORMATION_SCHEMA to determine the sequence in which the privileges have to be revoked with REVOKE ... RESTRICT. The INFORMATION_SCHEMA describes which privileges are assigned to which authorization identifiers in the tables TABLE_PRIVILEGES, COLUMN_PRIVILEGES,
USAGE_PRIVILEGES and CATALOG_PRIVILEGES.
Since a single REVOKE ... CASCADE statement can, under certain circumstances revoke a large number of privileges and delete views (see "Revoking privileges with the SQL statement REVOKE") and referential constraints (see "Revoking privileges with the SQL statement REVOKE"), it is recommended that you obtain information on the existing privileges in the tables of the INFORMATION_SCHEMA before issuing a REVOKE ... CASCADE statement.
As already mentioned, two ostensibly identical privileges granted to the grantee C by two different grantors, A and B, are regarded as different privileges.
A, for example, has passed the same privilege to C and with “WITH GRANT OPTION” to B. B, on the other hand, has in turn passed this privilege to C. If A now revokes this privilege from grantee C with REVOKE Privilege ON Object FROM C RESTRICT, C still has the privilege granted by B. Only if B revokes the privilege from the grantee C, is the privilege actually revoked from C.
If instead of this A now also revokes the privilege from grantee B with REVOKE Privilege ON Object FROM B CASCADE, B and C no longer have the privilege.
The revoking of privileges with the aid of REVOKE is demonstrated below in a simple example.
A privilege is generally identified by the following:
(grantor, grantee, action, object, [WITH GRANT OPTION]).
The following initial situation exists:
Figure 10: The passing on of a privilege
O, the owner of the schema, has granted user A the table privilege SELECT for the table COMPANY with the clause “WITH GRANT OPTION” (abbreviated to WGO in the example). This privilege is described as (O, A, SELECT, COMPANY, WGO), see figure 10:
User A has passed on this privilege to user B and has included the “WITH GRANT OPTION”. B has passed on the privilege to C, C has passed it on to D, and, finally, D to E. B, C and D have included the privilege “WITH GRANT OPTION”.
The following privileges are currently assigned:
(O, A, SELECT, COMPANY, WGO)
(A, B, SELECT, COMPANY, WGO)
(B, C, SELECT, COMPANY, WGO)
(C, D, SELECT, COMPANY, WGO)
(D, E, SELECT, COMPANY, WGO)
In figure 10, the fact that “User B has been granted the privilege by user A” is apparent from B's position directly under A. A line between related privileges indicates that A has not yet revoked the privilege from B.
D revokes the privilege from E with the statement:
REVOKE SELECT ON company FROM E RESTRICT
Once the REVOKE has been carried out, E no longer has the privilege (D, E, SELECT, COMPANY, WGO), see figure 11.
Figure 11: The revoke of the privilege (D,E,SELECT,COMPANY,WGO)
In the same way, C could now revoke the privilege from D, B from C, etc. For D to revoke the privilege from E, other users must not have been granted the same privilege by E and must not still be in possession of it. This is expressed in figure 10 and figure 11 as follows: No lines connect E or D with users below E or D.
Avoidance of abandoned privileges
In the situation shown in figure 11, A wishes to revoke the privilege from B. However, the system rejects the following REVOKE, because it is invalid:
REVOKE SELECT ON company FROM B RESTRICT
If the REVOKE had been executed, it would have resulted in the following situation:
Figure 12: Hypothetical situation: abandoned privileges
C still has the privilege granted by B (B, C, SELECT, COMPANY, WGO). B, however, no longer has the privilege granted by A (A, B, SELECT, COMPANY, WGO); this means that the privilege (B, C, SELECT, COMPANY, WGO) in figure 12 is “abandoned”. As a result, the privilege (C, D, SELECT, COMPANY, WGO) is likewise abandoned because the privilege (B, C, SELECT, COMPANY, WGO) is abandoned.
The abandoned privileges are indicated as follows in figure 12:
No connecting line exists any more from such a privilege upward to a privilege, or no connecting line exists upward to an abandoned privilege.
Generally, a privilege which has been granted and still exists is referred to as “abandoned” if the privilege still exists, but the grantor no longer possesses the privilege. Every privilege derived from an abandoned privilege is also referred to as “abandoned”. The term “abandoned privilege” refers to a purely hypothetical situation, since SESAM/SQL prevents them from occurring in the first place when REVOKE is used.
If REVOKE ... RESTRICT is used, SESAM/SQL prevents abandoned privileges by rejecting a REVOKE ... RESTRICT statement if execution of the statement would lead to an abandoned privilege. Before issuing a REVOKE ... RESTRICT statement, the user should therefore check whether revocation of a given privilege would lead to abandoned privileges and would therefore be rejected. Privileges which would be abandoned must be revoked with REVOKE ... RESTRICT by the user who granted the privilege.
Execution of a REVOKE ... CASCADE, on the other hand, can never lead to a situation in which abandoned privileges can occur. Consequently a REVOKE ... CASCADE is also never rejected for this reason. If, for example, in the situation illustrated in figure 10 grantor
A revokes the SELECT privilege from grantee B with REVOKE SELECT ON company FROM B CASCADE, all privileges which were granted by other grantees on the basis of the "WITH GRANT OPTION" privilege granted to B are then automatically revoked in a kind of "chain reaction": Initially grantor D revokes the privilege (D, E, SELECT, COMPANY, WGO) from grantee E, then grantor C revokes the privilege (C, D, SELECT, COMPANY, WGO) from grantee D, etc. This ensures that abandoned privileges can never occur.
The two examples that follow depict situations that would or would not lead to abandoned privileges.
Figure 13: Privilege granted by two users (C, D, SELECT, COMPANY, WGO)
The figure 13 depicts the following initial situation: C has been granted the same privilege by two different users (grantor A and grantor B). C, in turn, has passed on this privilege to D. A would now like to revoke the privilege granted to user C (A, C, SELECT, COMPANY, WGO).
To do this, A issues the statement: REVOKE SELECT ON company FROM C RESTRICT
The REVOKE succeeds. The figure 14 shows the situation after the REVOKE
Figure 14: The revoke of the privilege (D,E,SELECT,COMPANY,WGO)
The revoke would not produce an abandoned privilege because user C still has the privilege issued by B (B, C, SELECT, COMPANY, WGO). However, an attempt by B to revoke this privilege with the REVOKE ( REVOKE SELECT ON company FROM C RESTRICT) would be rejected. If the REVOKE were to succeed, it would lead to an abandoned privilege (C, D, SELECT, COMPANY, WGO).
The next example is based on the following situation (see figure 15): Two users, C1 and C2, have been granted the same privilege by two different users (grantor A in the case of C1 and grantor B in the case of C2). C1 then grants user D the privilege (C1, D, SELECT, COMPANY, WGO), and C2 grants user D the privilege (C2, D, SELECT, COMPANY, WGO).
Figure 15: Privileges (C1, D, SELECT, COMPANY, WGO) and (C2, D, SELECT, COMPANY, WGO)
An attempt by A to revoke the privilege (A, C1, SELECT, COMPANY, WGO) from C1 with REVOKE SELECT ON company FROM C1 RESTRICT is rejected because the revoke would cause the privilege (C1, D, SELECT, COMPANY, WGO) granted to D by C1 to be abandoned. The figure 16 shows the hypothetical result if the REVOKE were to be carried out illegally.
Figure 16: Hypothetical situation: an abandoned privilege (C1,D,SELECT,COMPANY,WGO)
The last two examples ( figure 13 and figure 14, and figure 15 and figure 16) underline once again why a REVOKE ... RESTRICT is always rejected if it would lead to an abandoned privilege.
In addition to “abandoned” privileges there are also “abandoned” referential constraints and “abandoned” views.
Avoidance of abandoned referential constraints
The concept of “abandoned” constraints is explained in the following.
A, the owner of base table T, wishes to revoke from user B with REVOKE ... RESTRICT the table privilege REFERENCES originally granted with GRANT, but user B has defined a referential constraint for columns in the table and the constraint has not yet been deleted with ALTER TABLE ... DROP CONSTRAINT. The REVOKE ... RESTRICT statement is rejected.
If the REVOKE ... RESTRICT statement were to be executed, this would leave a referential constraint applied to table T, for which the relevant table privilege REFERENCES no longer exists. As a result, the referential constraint would be abandoned.
On the other hand, an appropriate REVOKE ... CASCADE statement allows owner A to revoke from user B the REFERENCES privilege, even if a referential constraint is defined for the columns of table T. The REVOKE ... CASCADE statement executes an implicit ALTER TABLE...DROP CONSTRAINT statement for this referential constraint.
Avoidance of abandoned views
In addition to abandoned privileges and abandoned referential constraints there is the concept of “abandoned” views. This term will be explained on the basis of the following initial situation:
As grantor, the owner of a schema A assigns the table privilege SELECT for particular tables in a schema to the owner of schema B. The owner of schema B uses CREATE VIEW to create a view VB on which these tables are based.
The owner of A would now like to revoke the SELECT privilege granted to the owner of B with REVOKE ... RESTRICT. However, the REVOKE ... RESTRICT statement is rejected. If the REVOKE ... RESTRICT were successful, the owner of B, i.e. the owner of VB, would still be able to access the tables of schema A on which V B is based via V B despite the fact that the owner of B no longer has the table privilege SELECT for these tables, consequently may no longer access the table directly, and should therefore not even be permitted to define view V B.
A view definition would exist, even though the owner of B is no longer entitled to issue the relevant CREATE VIEW statement. The right to the definition of view VB has been revoked and the view is “abandoned”. Generally, a view is termed abandoned if the view's owner's table privilege SELECT has been revoked for one of the tables on which the view is based. A REVOKE ... RESTRICT statement that would normally produce an abandoned view if executed is always rejected. Users should therefore make sure that all views that required a SELECT privilege in order to be defined have been deleted with the SQL statement DROP VIEW before issuing the REVOKE ... RESTRICT that is to revoke that privilege.
If, in the above example, the owner of A revokes the privilege from the owner of B with a REVOKE ... CASCADE statement, all dependent views are deleted. The REVOKE ... CASCADE statement does not only revoke from the owner of B the SELECT privilege for the tables of his/her schema A, but executes the following statement implicitly:
DROP VIEW B.VBCASCADE
This deletes view VB and all the views which use view VB in their definition.