Using GRANT and REVOKE Together Saves Time and Effort
Multiple privileges for multiple users on selected table columns may require a lot of typing. Consider this example: The vice president of sales wants everyone in sales to see everything in the CUSTOMER table. But only sales managers should update, delete, or insert rows. Nobody should update the CustID field. The sales managers’ names are Tyson, Keith, and David. You can grant appropriate privileges to these managers with GRANT statements, as follows:
GRANT SELECT, INSERT, DELETE
ON CUSTOMER
TO Tyson, Keith, David ;
GRANT UPDATE
ON CUSTOMER (Company, CustAddress, CustCity,
CustState, CustZip, CustPhone, ModLevel)
TO Tyson, Keith, David ;
GRANT SELECT
ON CUSTOMER
TO Jenny, Valerie, Melody, Neil, Robert, Sam,
Brandon, MichelleT, Allison, Andrew,
Scott, MichelleB, Jaime, Linleigh, Matthew, Amanda;
That should do the trick. Everyone has SELECT rights on the CUSTOMER table. The sales managers have full INSERT and DELETE rights on the table, and they can update any column but the CustID column. Here’s an easier way to get the same result:
GRANT SELECT
ON CUSTOMER
TO SalesReps ;
GRANT INSERT, DELETE, UPDATE
ON CUSTOMER
TO Tyson, Keith, David ;
REVOKE UPDATE
ON CUSTOMER (CustID)
FROM Tyson, Keith, David ;
You still take three statements in this example for the same protection of the three statements in the preceding example. No one may change data in the CustID column; only Tyson, Keith, and David have INSERT, DELETE, and UPDATE privileges. These latter three statements are significantly shorter than those in the preceding example because you don’t name all the users in the sales department and all the columns in the table. (The time you spend typing names is also significantly shorter. That’s the idea.)
