Automate privileges

This design might seem to force you to execute a tedious number of GRANT statements when you first set up the database. Furthermore, privileges require constant maintenance, as people change jobs. For example, if a clerk in Human Resources is terminated, you want to revoke the Update privilege as soon as possible, otherwise the unhappy employee might execute a statement such as the following one: 
UPDATE hr_data
   SET (emp_name, hire_date, dept_num) = (NULL, NULL, 0)

Less dramatic, but equally necessary, privilege changes are required daily, or even hourly, in any model that contains sensitive data. If you anticipate this requirement, you can prepare some automated tools to help maintain privileges.

Your first step should be to specify privilege classes that are based on the jobs of the users, not on the structure of the tables. For example, a first-line manager requires the following privileges:
  • The Select and limited Update privileges on the hypothetical hr_data table
  • The Connect privilege to this and other databases
  • Some degree of privilege on several tables in those databases

When a manager is promoted to a staff position or sent to a field office, you must revoke all those privileges and grant a new set of privileges.

Define the privilege classes you support, and for each class specify the databases, tables, and columns to which you must give access. Then devise two automated routines for each class, one to grant the class to a user and one to revoke it.