Privileges when using a view

When you attempt to use a view, the database server tests only the privileges that you are granted on the view. It does not test your right to access the underlying tables.

If you create the view, your privileges are the ones noted in the preceding section. If you are not the creator, you have the privileges that the creator (or someone who had the WITH GRANT OPTION privilege) granted you.

Therefore, you can create a table and revoke access of PUBLIC to it; then you can grant limited access privileges to the table through views. Suppose you want to grant access privileges on the following table:

CREATE TABLE hr_data 
   (
   emp_key INTEGER,
   emp_name CHAR(40),
   hire_date DATE,
   dept_num SMALLINT,
   user-id CHAR(18),
   salary DECIMAL(8,2), 
   performance_level CHAR(1), 
   performance_notes TEXT
   )

The section Column-level privileges shows how to grant access privileges directly on the hr_data table. The examples that follow take a different approach. Assume that when the table was created, this statement was executed:

REVOKE ALL ON hr_data FROM PUBLIC

(Such a statement is not necessary in an ANSI-compliant database.) Now you create a series of views for different classes of users. For users who should have read-only access to the nonsensitive columns, you create the following view:

CREATE VIEW hr_public AS
   SELECT emp_key, emp_name, hire_date, dept_num, user_id
      FROM hr_data

Users who are given the Select privilege for this view can see nonsensitive data and update nothing. For Human Resources personnel who must enter new rows, you create a different view, as the following example shows:

CREATE VIEW hr_enter AS
   SELECT emp_key, emp_name, hire_date, dept_num
      FROM hr_data

You grant these users both Select and Insert privileges on this view. Because you, the creator of both the table and the view, have the Insert privilege on the table and the view, you can grant the Insert privilege on the view to others who have no privileges on the table.

On behalf of the person in the MIS department who enters or updates new user IDs, you create still another view, as the following example shows:

CREATE VIEW hr_MIS AS
   SELECT emp_key, emp_name, user_id
      FROM hr_data

This view differs from the previous view in that it does not expose the department number and date of hire.

Finally, the managers require access to all columns and they require the ability to update the performance-review data for their own employees only. You can meet these requirements by creating a table, hr_data, that contains a department number and computer user IDs for each employee. Let it be a rule that the managers are members of the departments that they manage. Then the following view restricts managers to rows that reflect only their employees:

CREATE VIEW hr_mgr_data AS
   SELECT * FROM hr_data
      WHERE dept_num = 
         (SELECT dept_num FROM hr_data
            WHERE user_id = USER)
      AND NOT user_id = USER

The final condition is required so that the managers do not have update access to their own row of the table. Therefore, you can safely grant the Update privilege to managers for this view, but only on selected columns, as the following statement shows:

GRANT SELECT, UPDATE (performance_level, performance_notes)
   ON hr_mgr_data TO peter_m