The CREATE Statement

SQL does not require explicit permission for a user to create a relation, unless the relation is defined to have a foreign key to another relation. In this case, the user must have the REFERENCES privilege for appropriate columns of the referenced relation. To create a view, a user must have the SELECT privilege on every relation mentioned in definition of the view. If a user has INSERT, DELETE, or UPDATE privileges on these relations, corresponding privileges will be obtained on the view (if it is updatable).

The GRANT Statement

The owner of a relation can grant one or more access privileges to another user. This can be done with or without the GRANT OPTION. If the owner grants SELECT with the GRANT OPTION, the user receiving this grant can further grant SELECT to other users. The latter GRANT can be done with or without the GRANT OPTION at the granting user’s discretion.

The general format of a grant operation in SQL is as follows:

                    GRANT    privileges
                    [ON      relation]
                    TO       users
                    [WITH    GRANT OPTION]

The GRANT command applies to base relations as well as to views. The brackets on the ON and WITH clauses denotes that these are optional and may not be present in every GRANT command. It is not possible to grant a user the grant option on a privilege, without allowing the grant option itself to be further granted.

INSERT, DELETE, and SELECT privileges apply to the entire relation as a unit. Because INSERT and DELETE are operations on entire rows, this is appropriate. SELECT, however, implies the ability to select on all columns. Selection on a subset of the columns can be achieved by defining a suitable view and granting SELECT on the view. This method is somewhat awkward, and there have been proposals to allow SELECT to be granted on a subset of the columns of a relation. In general, the UPDATE privilege applies to a subset of the columns. For example, a user can be granted the authority to update the OFFICE but not the SALARY of an EMPLOYEE. SQL’92 extends the INSERT privilege to apply to a subset of the columns. Thus, a clerical user, for example, can insert a tuple for a new employee with the NAME, DEPARTMENT, and RANK data. The OFFICE, SALARY, and SUPERVISOR data can then be updated in this tuple by a suitably authorized supervisory user.

SQL’89 has several omissions in its access control facilities. These omissions have been addressed by different vendors in different ways. The following section identifies the major omissions and illustrates how they have been addressed in products and in the evolving standard.

The REVOKE Statement

One major shortcoming of SQL’89 is the lack of a REVOKE statement to take away a privilege granted by a GRANT. IBM’s DB2 product provides a REVOKE statement for this purpose.

It is often necessary that revocation cascade. In a cascading revoke, not only is the privilege revoked, so too are all GRANTs based on the revoked privilege. For example, if user Tom grants Dick SELECT on relation R with the GRANT OPTION, Dick subsequently grants Harry SELECT on R, and Tom revokes SELECT on R from Dick, the SELECT on R privilege is taken away not only from Dick but also from Harry. The precise mechanics of a cascading revoke is somewhat complicated. If Dick had received the SELECT on R privilege (with GRANT OPTION) not only from Tom but also from Jane before Dick granted SELECT to Harry, Tom’s revocation of the SELECT from R privilege from Dick would not cause either Dick or Tom to lose this privilege. This is because the GRANT from Jane remains valid.

Cascading revocation is not always desirable. A user’s privileges to a given table are often revoked because the user’s job functions and responsibilities have changed. For example, if Mary, the head of a department moves on to a different assignment, her privileges to her former department’s data should be revoked. However, a cascading revoke could cause lots of employees of that department to lose their privileges. These privileges must then be regranted to keep the department functioning.

SQL’92 allows a revocation to be cascading or not cascading, as specified by the revoker. This is a partial solution to the more general problem of how to reassign responsibility for managing access to data from one user to another as their job assignments change.

Other Privileges

Another major shortcoming of SQL’89 is the lack of control over who can create relations. In SQL’89, every user is authorized to create relations. The Oracle DBMS requires possession of a RESOURCE privilege to create new relations. SQL’89 does not include a privilege to DROP a relation. Such a privilege is included in DB2.

SQL’89 does not address the issue of how new users are enrolled in a data base. Several DBMS products take the approach that a data base is originally created to have a single user, usually called the DBA (data base administrator). The DBA essentially has all privileges with respect to this data base and is responsible for enrolling users and creating relations. Some systems recognize a special privilege (called DBA in Oracle and DBADM in DB2) that can be granted to other users at the original DBA’s discretion and allows these users effectively to act as the DBA.