Granting privileges through views

You can grant most table privileges (except ALTER, REFERENCES, TRIGGER, and INDEX) on a view. By creating a view and granting privileges through it, you can give an ID access to only a specific combination of data.

About this task

The ability to grant privileges through views is sometimes called field-level access control or field-level sensitivity.

Suppose that you want the ID MATH110 to be able to extract the following column data from the sample employee table for statistical investigation: HIREDATE, JOB, EDLEVEL, SEX, SALARY, BONUS, and COMM for DSN8910.EMP. However, you want to impose the following restrictions:

• No access to employee names or identification numbers
• No access to data for employees hired before 1996
• No access to data for employees with an education level less than 13
• No access to data for employees whose job is MANAGER or PRES

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

To grant privileges to the view that you create:

1. Issue the following CREATE statement to create the view that you want:

   

   CREATE VIEW SALARIES AS
      SELECT HIREDATE, JOB, EDLEVEL, SEX, SALARY, BONUS, COMM
         FROM DSN8D10.EMP
         WHERE HIREDATE > '1995-12-31' AND 
               EDLEVEL >= 13 AND 
               JOB <> 'MANAGER' AND
               JOB <> 'PRES';

   
2. Issue the following statement to grant the SELECT privilege on the SALARIES view to MATH110:

   

   GRANT SELECT ON SALARIES TO MATH110;

   

Results

After you grant the privilege, MATH110 can execute SELECT statements on the restricted set of data only. Alternatively, you can give an ID access to only a specific combination of data by using multilevel security with row-level granularity.