How to Delegate SQL Access Privileges

By Allen G. Taylor

The DBA (database administrator) can grant any SQL 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 to retain control. Only users that the DBA or object owner empowers to do so can perform the operation 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 the power to delegate their 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 designated 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, anyone with the role of assistant sales manager can make changes to the BonusPct column in the BONUSRATE table.

Of course, you make a tradeoff between security and convenience when you delegate access rights to a designated alternate. 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.