Discretionary Access Control

Discretionary Access Control
In Section 2.4 we stated that a DBMS should provide a mechanism to ensure that only
authorized users can access the database. Modern DBMSs typically provide one or both of
the following authorization mechanisms:
n Discretionary access control Each user is given appropriate access rights (or privileges)
on specific database objects. Typically users obtain certain privileges when they
create an object and can pass some or all of these privileges to other users at their discretion.
Although flexible, this type of authorization mechanism can be circumvented
by a devious unauthorized user tricking an authorized user into revealing sensitive data.
n Mandatory access control Each database object is assigned a certain classification
level (for example, Top Secret, Secret, Confidential, Unclassified) and each subject (for
example, users, programs) is given a designated clearance level. The classification
levels form a strict ordering (Top Secret > Secret > Confidential > Unclassified) and
a subject requires the necessary clearance to read or write a database object. This type
of multilevel security mechanism is important for certain government, military, and
corporate applications. The most commonly used mandatory access control model
is known as Bell–LaPadula (Bell and LaPadula, 1974), which we discuss further in
Chapter 19.
SQL supports only discretionary access control through the GRANT and REVOKE statements.
The mechanism is based on the concepts of authorization identifiers, ownership,
and privileges, as we now discuss.
Authorization identifiers and ownership
Authorization identifiers and ownership
An authorization identifier is a normal SQL identifier that is used to establish the identity
of a user. Each database user is assigned an authorization identifier by the Database
Administrator (DBA). Usually, the identifier has an associated password, for obvious
security reasons. Every SQL statement that is executed by the DBMS is performed on
behalf of a specific user. The authorization identifier is used to determine which database
objects the user may reference and what operations may be performed on those objects.
Each object that is created in SQL has an owner. The owner is identified by the authorization
identifier defined in the AUTHORIZATION clause of the schema to which the
object belongs (see Section 6.3.1). The owner is initially the only person who may know
of the existence of the object and, consequently, perform any operations on the object.
Privileges
Privileges are the actions that a user is permitted to carry out on a given base table or view.
The privileges defined by the ISO standard are:
n SELECT – the privilege to retrieve data from a table;
n INSERT – the privilege to insert new rows into a table;
n UPDATE – the privilege to modify rows of data in a table;
n DELETE – the privilege to delete rows of data from a table;
n REFERENCES – the privilege to reference columns of a named table in integrity
constraints;
n USAGE – the privilege to use domains, collations, character sets, and translations. We
do not discuss collations, character sets, and translations in this book; the interested
reader is referred to Cannan and Otten (1993).
The INSERT and UPDATE privileges can be restricted to specific columns of the
table, allowing changes to these columns but disallowing changes to any other column.
Similarly, the REFERENCES privilege can be restricted to specific columns of the table,
allowing these columns to be referenced in constraints, such as check constraints and
foreign key constraints, when creating another table, but disallowing others from being
referenced.
When a user creates a table using the CREATE TABLE statement, he or she automatically
becomes the owner of the table and receives full privileges for the table. Other users initially
have no privileges on the newly created table. To give them access to the table, the
owner must explicitly grant them the necessary privileges using the GRANT statement.
When a user creates a view with the CREATE VIEW statement, he or she automatically
becomes the owner of the view, but does not necessarily receive full privileges on the
view. To create the view, a user must have SELECT privilege on all the tables that make
up the view and REFERENCES privilege on the named columns of the view. However,
the view owner gets INSERT, UPDATE, and DELETE privileges only if he or she holds
these privileges for every table in the view.