SQL Series...45

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.)