Row level security

The system of privileges that are provided by the DBMS provide adequate security at the system level and at the level of the individual database object. Thus the DBA can prevent or allow access to the system, and schema owners can prevent or allow access to tables, views and so on. Different levels that involve increasingly small differences are referred to as granularity - the smaller the object, the lower or finer the level of granularity.

There are situations though where access control over individuals objects does not provide a sufficiently fine level of granularity. Take for example a hospital records system where consultants are only allowed to see the records for their own registered patients in order to protect the privacy of others. Here it is not sufficient to allow or deny access to the patient records table because it contains all records. A finer level of granularity is required whereby a user can have access to certain rows in a table but not others.

The DBMS provides a solution to this problem through the use of views. A view can be created which extracts only the required records from a table. When access privileges are granted on a view, they are independent of any access rights on the underlying table. This means that a user can have access to that subset of records in the view without having any rights at all on the rest of the table. The following example illustrates this.

Assume that the hospital system contains the PATIENT table shown on the right. The column assigned_consultant contains the identifier for the consultant. If we further assume that the consultant identifier, as well as being the primary key in the CONSULTANT table, is also the username that the consultant uses to log into the database, we can user that information to select the appropriate columns from PATIENT.

Oracle and other database platforms always provide some way to identify the current user. In Oracle it is done using a pseudo-column caller USER. A pseudo-column is a context dependent value which behaves like a column with respect to SQL statements, but which is not actually stored in a table. SYSDATE is another example.

If it is referenced, USER evaluates to the username of the current user. If we include it in the definition of a view therefore, the contents of the view can be made to vary depending on which user is performing the query.

In the hospital example, we could create a view called ASSIGNED_PATIENTS using the following statement:

    CREATE VIEW assigned_patients AS
    SELECT *
    FROM   patient
    WHERE  assigned_consultant = USER;

patient
patient_id
first_name
last_name
gender
date_of_birth
last_admission_date
assigned_consultant
date_of_death