Creating views of payroll operations
The Spiffy security planners decide to use views for implementing the security objectives for members of the payroll operations department.
About this task
The PAYDEPT view shows all the columns of the employee table except for job, salary, bonus, and commission. The view does not show the rows for members of the payroll operations department.
CREATE VIEW PAYDEPT AS
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT,
PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE
FROM DSN8B10.EMP
WHERE WORKDEPT<>'P013'
WITH CHECK OPTION; The CHECK OPTION ensures that every row that is inserted or updated through the view conforms to the definition of the view.
A second view, the PAYMGR view, gives Spiffy payroll managers access to any record, including records for the members of the payroll operations department.
CREATE VIEW PAYMGR AS
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT,
PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE
FROM DSN8B10.EMP
WITH CHECK OPTION; Neither PAYDEPT nor PAYMGR provides access to compensation amounts. When a row is inserted for a new employee, the compensation amounts remain null. An update process can change these values at a later time. The owner of the employee table creates, owns, and grants privileges on both views.