Chapter 1-2-3
Relational Data Base Access Controls Using SQL

Ravi S. Sandhu

This chapter discusses access controls in relational data base management systems. Access controls have been built into relational systems since they first emerged. Over the years, standards have developed and are continuing to evolve. In recent years, products incorporating mandatory controls for multilevel security have also started to appear.

The chapter begins with a review of the relational data model and SQL language. Traditional discretionary access controls provided in various dialects of SQL are then discussed. Limitations of these controls and the need for mandatory access controls are illustrated, and three architectures for building multilevel data bases are presented. The chapter concludes with a brief discussion of role-based access control as an emerging technique for providing better control than do traditional discretionary access controls, without the extreme rigidity of traditional mandatory access controls.

RELATIONAL DATA BASES

A relational data base stores data in relations that are expected to satisfy some simple mathematical properties. Roughly speaking, a relation can be thought of as a table. The columns of the table are called attributes, and the rows are called tuples. There is no significance to the order of the columns or rows; however, duplicate rows with identical values for all columns are not allowed.

Relation schemes must be distinguished from relation instances. The relation scheme gives the names of attributes as well as their permissible values. The set of permissible values for an attribute is said to be the attribute’s domain. The relation instance gives the tuples of the relation at a given instant.

For example, the following is a relation scheme for the EMPLOYEE relation:

    EMPLOYEE (NAME, DEPT, RANK, OFFICE, SALARY, SUPERVISOR)

The domain of the NAME, DEPT, RANK, OFFICE, and SUPERVISOR attributes are character strings, and the domain of the SALARY attribute is integers. A particular instance of the EMPLOYEE relation, reflecting the employees who are currently employed, is as follows:

The relation instance of EMPLOYEE changes with the arrival of new employees, changes to data for existing employees, and with their departure. The relation scheme, however, remains fixed. The NULL value in place of Black’s supervisor signifies that Black’s supervisor has not been defined.

Primary Key

A candidate key for a relation is a minimal set of attributes on which all other attributes depend functionally. In other words, two tuples may not have the same values of the candidate key in a relation instance. A candidate key is minimal — no attribute can be discarded without destroying this property. A candidate key always exists, because, in the extreme case, it consists of all the attributes.

In general, there can be more than one candidate key for a relation. If, for example in the EMPLOYEE previously described, duplicate names can never occur, NAME is a candidate key. If there are no shared offices, OFFICE is another candidate key. In the particular relation instance above there are no duplicate salary values. This, however, does not mean that salary is a candidate key. Identification of the candidate key is a property of the relation scheme and applies to every possible instance, not merely to the one that happens to exist at a given moment. SALARY would qualify as a candidate key only in the unlikely event that the organization forbids duplicate salaries.

The primary key of a relation is one of its candidate keys that has been designated as such. In the previous example, NAME is probably more appropriate than OFFICE as the primary key. Realistically, a truly unique identifier, such as social security number or employee identity number, rather than NAME should be used as the primary key.