SQL Security: Revoking Privileges (REVOKE)

In most SQL-based databases, the privileges that you have granted with the GRANT statement can be taken away with the REVOKE statement, shown in Figure 15-7. The REVOKE statement has a structure that closely parallels the GRANT statement, specifying a specific set of privileges to be taken away, for a specific database object, from one or more user-ids.

A REVOKE statement may take away all or some of the privileges that you previously granted to a user-id. For example, consider this statement sequence:

Grant and then revoke some SALESREPS table privileges.

GRANT SELECT, INSERT, UPDATE

   ON SALESREPS

   TO ARUSER, OPUSER

 

REVOKE INSERT, UPDATE

    ON SALESREPS

  FROM OPUSER

The INSERT and UPDATE privileges on the SALESREPS table are first given to the two users and then revoked from one of them. However, the SELECT privilege remains for both user-ids. Here are some other examples of the REVOKE statement:

Take away all privileges granted earlier on the OFFICES table.

 REVOKE ALL PRIVILEGES

     ON OFFICES

   FROM ARUSER

Take away UPDATE and DELETE privileges for two user-ids.

REVOKE UPDATE, DELETE

    ON OFFICES

  FROM ARUSER, OPUSER

Take away all privileges on the OFFICES that were formerly granted to all users.

 REVOKE ALL PRIVILEGES

     ON OFFICES

   FROM PUBLIC

When you issue a REVOKE statement, you can take away only those privileges that you previously granted to another user. That user may also have privileges that were granted by other users; those privileges are not affected by your REVOKE statement. Note specifically that if two different users grant the same privilege on the same object to a user and one of them later revokes the privilege, the second user’s grant will still allow the user to access the object. This handling of overlapping grants of privileges is illustrated in the following example sequence.

Suppose that Sam Clark, the sales vice president, gives Larry Fitch SELECT privileges for the SALESREPS table and SELECT and UPDATE privileges for the ORDERS table, using the following statements:

GRANT SELECT

   ON SALESREPS

   TO LARRY

GRANT SELECT, UPDATE

   ON ORDERS

   TO LARRY

A few days later George Watkins, the marketing vice president, gives Larry the SELECT and DELETE privileges for the ORDERS table and the SELECT privilege for the CUSTOMERS table, using these statements:

GRANT SELECT, DELETE

   ON ORDERS

   TO LARRY

 

GRANT SELECT

   ON CUSTOMERS

   TO LARRY

Note that Larry has received privileges on the ORDERS table from two different sources. In fact, the SELECT privilege on the ORDERS table has been granted by both sources. A few days later, Sam revokes the privileges he previously granted to Larry for the ORDERS table:

 REVOKE SELECT, UPDATE

     ON ORDERS

   FROM LARRY

After the DBMS processes the REVOKE statement, Larry still retains the SELECT privilege on the SALESREPS table, the SELECT and DELETE privileges on the ORDERS table, and the SELECT privilege on the CUSTOMERS table, but he has lost the UPDATE privilege on the ORDERS table.

1. Revoke and the grant option

When you grant privileges with the GRANT OPTION and later revoke these privileges, most DBMS brands will automatically revoke all privileges derived from the original grant. Consider again the chain of privileges in Figure 15-6, from Sam Clark, the sales vice president, to Larry Fitch, the Los Angeles office manager, and then to Sue Smith.

If Sam now revokes Larry’s privileges for the WESTREPS view, Sue’s privilege is automatically revoked as well.

The situation gets more complicated if two or more users have granted privileges and one of them later revokes the privileges. Consider Figure 15-8, a slight variation on the last example. Here, Larry receives the SELECT privilege with the GRANT OPTION from both Sam (the sales vice president) and George (the marketing vice president) and then grants privileges to Sue. This time when Sam revokes Larry’s privileges, the grant of privileges from George remains. Furthermore, Sue’s privileges also remain because they can be derived from George’s grant.

However, consider another variation on the chain of privileges, with the events slightly rearranged, as shown in Figure 15-9. Here, Larry receives the privilege with the GRANT OPTION from Sam, grants the privilege to Sue, and then receives the grant, with the GRANT OPTION, from George. This time when Sam revokes Larry’s privileges, the results are slightly different, and they may vary from one DBMS to another. As in Figure 15-8, Larry retains the SELECT privilege on the WESTREPS view because the grant from George is still intact. But in a DB2 or SQL/DS database, Sue automatically loses her SELECT privilege on the table. Why? Because the grant from Larry to Sue was clearly derived from the grant from Sam to Larry, which has just been revoked. It could not have been derived from George’s grant to Larry because that grant had not yet taken place when the grant from Larry to Sue was made.

In a different brand of DBMS, Sue’s privileges might remain intact because the grant from George to Larry remains intact. Thus, the time sequence of GRANT and REVOKE statements, rather than just the privileges themselves, can determine how far the effects of a REVOKE statement will cascade. Granting and revoking privileges with the GRANT OPTION must be handled very carefully, to ensure that the results are those you intend.

2. Revoke and the ANSI/ISO Standard

The SQL1 standard specified the GRANT statement as part of the SQL Data Definition Language (DDL). Recall from Chapter 13 that the SQL1 standard treated the DDL as a separate, static definition of a database and did not require that the DBMS permit dynamic changes to database structure. This approach applies to database security as well. Under the SQL1 standard, accessibility to tables and views in the database is determined by a series of GRANT statements included in the database schema. There is no mechanism for changing the security scheme once the database structure is defined. The REVOKE statement is therefore absent from the SQL1 standard, just as the DROP TABLE statement is missing from the standard.

Despite its absence from the SQL1 standard, the REVOKE statement was provided by virtually all commercial SQL-based DBMS products since their earliest versions. As with the DROP and ALTER statements, the DB2 dialect of SQL has effectively set the standard for the REVOKE statement. The SQL2 standard includes a specification for the REVOKE statement based on the DB2 statement with some extensions. One of the extensions gives the user more explicit control over how privileges are revoked when the privileges have, in turn, been granted to others. The other provides a way to revoke the GRANT OPTION without revoking the privileges themselves.

To specify how the DBMS should handle the revoking of privileges that have been in turn granted to others, the SQL2 standard requires that a CASCADE or RESTRICT option be specified in a REVOKE statement. (A similar requirement applies to many of the DROP statements in the SQL2 standard, as described in Chapter 13.) Suppose that SELECT and UPDATE privileges have previously been granted to Larry on the ORDERS table, with the GRANT OPTION, and that Larry has further granted these options to Bill. Then this REVOKE statement:

 REVOKE SELECT, UPDATE

     ON ORDERS

   FROM LARRY CASCADE

revokes not only Larry’s privileges, but Bill’s as well. The effect of the REVOKE statement thus cascades to all other users whose privileges have flowed from the original GRANT. Now, assume the same circumstances and this REVOKE statement:

REVOKE SELECT, UPDATE

    ON ORDERS

  FROM LARRY RESTRICT

In this case, the REVOKE fails. The RESTRICT option tells the DBMS not to execute the statement if it will affect any other privileges in the database. The resulting error calls the user’s attention to the fact that there are (possibly unintentional) side-effects of the REVOKE statement and allows the user to reconsider the action. If the user wants to go ahead and revoke the privileges, the CASCADE option can be specified.

The SQL2 version of the REVOKE statement also gives a user more explicit, separate control over privileges and the GRANT OPTION for those privileges. Suppose again that Larry has been granted privileges on the ORDERS table, with the GRANT OPTION for those privileges. The usual REVOKE statement for those privileges:

 REVOKE SELECT, UPDATE

     ON ORDERS

   FROM LARRY

takes away both the privileges and the ability to grant those privileges to others. The SQL2 standard permits this version of the REVOKE statement:

REVOKE GRANT OPTION FOR SELECT, UPDATE

    ON ORDERS

  FROM LARRY CASCADE

If the statement is successful, Larry will lose the ability to grant these privileges to other users, but he will not lose the privileges themselves. As before, the SQL2 standard requires the CASCADE or the RESTRICT option to specify how the DBMS should handle the statement if Larry has, in turn, granted the GRANT OPTION to other users.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *