Column-Level Privileges

You can qualify the Select, Update, and References privileges with the names of specific columns. Naming specific columns allows you to grant specific access to a table. You can permit a user to see only certain columns, to update only certain columns, or to impose referential constraints on certain columns.

You can use the GRANT and REVOKE statements to grant or restrict access to table data. This feature solves the problem that only certain users should know the salary, performance review, or other sensitive attributes of an employee. Suppose a table of employee data is defined as the following example shows:

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
   )

Because this table contains sensitive data, you execute the following statement immediately after you create it:

REVOKE ALL ON hr_data FROM PUBLIC

For selected persons in the Human Resources department, and for all managers, execute the following statement:

GRANT SELECT ON hr_data TO harold_r

In this way, you permit certain users to view all columns. (The final section of this chapter discusses a way to limit the view of managers to their employees only.) For the first-line managers who carry out performance reviews, you could execute a statement such as the following one:

GRANT UPDATE (performance_level, performance_notes)
   ON hr_data TO wallace_s, margot_t

This statement permits the managers to enter their evaluations of their employees. You would execute a statement such as the following one only for the manager of the Human Resources department or whomever is trusted to alter salary levels:

GRANT UPDATE (salary) ON hr_data to willard_b

For the clerks in the Human Resources department, you could execute a statement such as the following one:

GRANT UPDATE (emp_key, emp_name, hire_date, dept_num)
   ON hr_data TO marvin_t

This statement gives certain users the ability to maintain the nonsensitive columns but denies them authorization to change performance ratings or salaries. The person in the MIS department who assigns computer user IDs is the beneficiary of a statement such as the following one:

GRANT UPDATE (user_id) ON hr_data TO eudora_b

On behalf of all users who are allowed to connect to the database, but who are not authorized to see salaries or performance reviews, execute statements such as the following one to permit them to see the nonsensitive data:

GRANT SELECT (emp_key, emp_name, hire_date, dept_num, user-id)
   ON hr_data TO george_b, john_s

These users can perform queries such as the following one:

SELECT COUNT(*) FROM hr_data WHERE dept_num IN (32,33,34)

However, any attempt to execute a query such as the following one produces an error message and no data:

SELECT performance_level FROM hr_data
   WHERE emp_name LIKE '*Smythe'