Creating views of employee data

The Spiffy security planners decide to use views for implementing the restrictions on managers' access to employee data.

To create a view of employee data for every employee that reports to a manager, the Spiffy security planners perform the following steps:

1. Add a column that contains manager IDs to DSN8D10.DEPT, as shown in the following statement:

   ALTER TABLE DSN8D10.DEPT
      ADD MGRID CHAR(8) FOR SBCS DATA NOT NULL WITH DEFAULT;

   Populate the MGRID column with the user ID for each department manager.

2. Create a view that selects employee information about employees that work for a given manager, as shown in the following statement:

   CREATE VIEW DEPTMGR AS
      SELECT * FROM DSN8D10.EMP, DSN8D10.DEPT
         WHERE WORKDEPT = DEPTNO
         AND MGRID = USER;

3. Ensure that every manager has the SELECT privilege on the view.