SQL Series...43

Granting the Power to Grant Privileges

The DBA can grant any privileges to anyone. An object owner can grant any privileges on that object to anyone. But users who receive privileges this way can’t in turn grant those privileges to someone else. This restriction helps the DBA or table owner retain control. Only users the DBA or object owner empowers to do so can gain access to the object in question.

From a security standpoint, putting limits on the capability to delegate access privileges makes a lot of sense. Many occasions arise, however, in which users need such delegation authority. Work can’t come to a screeching halt every time someone is ill, on vacation, or out to lunch. You can trust some users with the power to delegate their access rights to reliable desig-nated alternates. To pass such a right of delegation to a user, the GRANT uses the WITH GRANT OPTION clause. The following statement shows one example of how you can use this clause:

GRANT UPDATE (BonusPct)

ON BONUSRATE

TO SalesMgr

WITH GRANT OPTION ;

Now the sales manager can delegate the UPDATE privilege by issuing the following statement:

GRANT UPDATE (BonusPct)

ON BONUSRATE

TO AsstSalesMgr ;

After the execution of this statement, the assistant sales manager can make changes to the BonusPct column in the BONUSRATE table — a power she didn’t have before.

A tradeoff exists between security and convenience. The owner of the BONUSRATE table relinquishes considerable control in granting the UPDATE privilege to the sales manager by using the WITH GRANT OPTION. The table owner hopes that the sales manager takes this responsibility seriously and is careful about passing on the privilege.