Automate privileges
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.
- 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.