Use of views to control access

The table privileges DELETE, INSERT, SELECT, and UPDATE can also be granted on a view. By creating a view and granting privileges on it, you can give an ID access to only a specific subset of data. This capability is sometimes called field-level access control or field-level sensitivity.

Example

Suppose that you want a particular ID, say MATH110, to be able to extract certain data from the EMP table for statistical investigation. To be exact, suppose that you want to allow access to data like this:

  • From columns HIREDATE, JOB, EDL, SALARY, COMM (but not an employee's name or identification number)
  • Only for employees that were hired after December 15, 1996
  • Only for employees with an education level of 14 or higher
  • Only for employees whose job is not MGR or PRS

You can create and name a view that shows exactly that combination of data:

Begin general-use programming interface information.
CREATE VIEW SALARIES AS
   SELECT HIREDATE, JOB, EDL, SALARY, COMM
      FROM EMP
      WHERE HIREDATE> '1996-12-15' AND EDLEVEL>= 14
      AND JOB IS DISTINCT FROM 'MGR' AND JOB IS DISTINCT FROM 'PRS';
End general-use programming interface information.

Then you can use the GRANT statement to grant the SELECT privilege on the view SALARIES to MATH110: 

GRANT SELECT ON SALARIES TO MATH110;

Now, MATH110 can run SELECT statements that query only the restricted set of data.