The basic GRANT statement, shown in Figure 15-5, is used to grant security privileges on database objects to specific users. Normally, the GRANT statement is used by the owner of a table or view to give other users access to the data. As shown in the figure, the GRANT statement includes a specific list of the privileges to be granted, the name of the table to which the privileges apply, and the user-id to which the privileges are granted.
The GRANT statement shown in the syntax diagram conforms to the ANSI/ISO SQL standard. Many DBMS brands follow the DB2 GRANT statement syntax, which is more flexible. The DB2 syntax allows you to specify a list of user-ids and a list of tables, making it simpler to grant many privileges at once. Here are some examples of simple GRANT statements for the sample database:
Give order-processing users full access to the ORDERS table.
GRANT SELECT, INSERT, DELETE, UPDATE
ON ORDERS
TO OPUSER
Let accounts receivable users retrieve customer data and add new customers to the CUSTOMERS table, but give order-processing users read-only access.
GRANT SELECT, INSERT
ON CUSTOMERS
TO ARUSER
GRANT SELECT
ON CUSTOMERS
TO OPUSER
Allow Sam Clark to insert or delete an office.
GRANT INSERT, DELETE
ON OFFICES
TO SAM
For convenience, the GRANT statement provides two shortcuts that you can use when granting many privileges or when granting them to many users. Instead of specifically listing all of the privileges available for a particular object, you can use the keywords ALL PRIVILEGES. This GRANT statement gives Sam Clark, the vice president of sales, full access to the SALESREPS table:
Give all privileges on the SALESREPS table to Sam Clark.
GRANT ALL PRIVILEGES
ON SALESREPS
TO SAM
Instead of giving privileges to every user of the database one-by-one, you can use the keyword PUBLIC to grant a privilege to every authorized database user. This GRANT statement lets anyone retrieve data from the OFFICES table:
Give all users SELECT acces to the OFFICES table.
GRANT SELECT
ON OFFICES
TO PUBLIC
Note that this GRANT statement grants access to all present and future authorized users, not just to the user-ids currently known to the DBMS. This eliminates the need for you to explicitly grant privileges to new users as they are authorized.
1. Column Privileges
The SQL1 standard allows you to grant the UPDATE privilege for individual columns of a table or view, and the SQL2 standard allows a column list for INSERT and REFERENCES privileges as well. The columns are listed after the UPDATE, INSERT, or REFERENCES keyword and enclosed in parentheses. Here is a GRANT statement that allows the orderprocessing department to update only the company name and assigned salesperson columns of the CUSTOMERS table:
Let order-processing users change company names and salesperson assignments.
GRANT UPDATE (COMPANY, CUST_REP)
ON CUSTOMERS
TO OPUSER
If the column list is omitted, the privilege applies to all columns of the table or view, as in this example:
Let accounts receivable users change any customer information.
GRANT UPDATE
ON CUSTOMERS
TO ARUSER
The ANSI/ISO standard does not permit a column list for the SELECT privilege; it requires that the SELECT privilege apply to all of the columns of a table or view. In practice, this isn’t a serious restriction. To grant access to specific columns, you first define a view on the table that includes only those columns and then grant the SELECT privilege only for the view. However, views defined solely for security purposes can clog the structure of an otherwise simple database. For this reason, some DBMS brands allow a column list for the SELECT privilege. For example, the following GRANT statement is legal for the Sybase, SQL Server, and Informix DBMS brands:
Give accounts receivable users read-only access to the employee number, name, and sales office columns of the SALESREPS table.
GRANT SELECT (EMPL_NUM, NAME, REP_OFFICE)
ON SALESREPS
TO ARUSER
This GRANT statement eliminates the need for the REPINFO view defined in Figure 15-3, and in practice, it can eliminate the need for many views in a production database. However, the use of a column list for the SELECT privilege is unique to certain SQL dialects, and it is not permitted by the ANSI/ISO standard or by the IBM SQL products.
2. Passing Privileges (grant option)
When you create a database object and become its owner, you are the only person who can grant privileges to use the object. When you grant privileges to other users, they are allowed to use the object, but they cannot pass those privileges on to other users. In this way, the owner of an object maintains very tight control both over who has permission to use the object and over which forms of access are allowed.
Occasionally, you may want to allow other users to grant privileges on an object that you own. For example, consider again the EASTREPS and WESTREPS views in the sample database. Sam Clark, the vice president of sales, created these views and owns them. He can give the Los Angeles office manager, Larry Fitch, permission to use the WESTREPS view with this GRANT statement:
GRANT SELECT
ON WESTREPS
TO LARRY
What happens if Larry wants to give Sue Smith (user-id SUE) permission to access the WESTREPS data because she is doing some sales forecasting for the Los Angeles office? With the preceding GRANT statement, he cannot give her the required privilege. Only Sam Clark can grant the privilege, because he owns the view.
If Sam wants to give Larry discretion over who may use the WESTREPS view, he can use this variation of the previous GRANT statement:
GRANT SELECT
ON WESTREPS
TO LARRY
WITH GRANT OPTION
Because of the WITH GRANT OPTION clause, this GRANT statement conveys, along with the specified privileges, the right to grant those privileges to other users.
Larry can now issue this GRANT statement:
GRANT SELECT
ON WESTREPS
TO SUE
which allows Sue Smith to retrieve data from the WESTREPS view. Figure 15-6 graphically illustrates the flow of privileges, first from Sam to Larry, and then from Larry to Sue. Because the GRANT statement issued by Larry did not include the WITH GRANT OPTION clause, the chain of permissions ends with Sue; she can retrieve the WESTREPS data but cannot grant access to another user. However, if Larry’s grant of privileges to Sue had included the GRANT OPTION, the chain could continue to another level, allowing Sue to grant access to other users.
Alternatively, Larry might construct a view for Sue including only the salespeople in the Los Angeles office and give her access to that view:
CREATE VIEW LAREPS AS
SELECT *
FROM WESTREPS
WHERE OFFICE = 21
GRANT ALL PRIVILEGES
ON LAREPS
TO SUE
Larry is the owner of the LAREPS view, but he does not own the WESTREPS view from which this new view is derived. To maintain effective security, the DBMS requires that Larry not only have the SELECT privilege on WESTREPS, but also requires that he have the GRANT OPTION for that privilege before allowing him to grant the SELECT privilege on LAREPS to Sue.
Once a user has been granted certain privileges with the GRANT OPTION, that user may grant those privileges and the GRANT OPTION to other users. Those other users can, in turn, continue to grant both the privileges and the GRANT OPTION. For this reason, you should use great care when giving other users the GRANT OPTION. Note that the GRANT OPTION applies only to the specific privileges named in the GRANT statement. If you want to grant certain privileges with the GRANT OPTION and grant other privileges without it, you must use two separate GRANT statements, as in this example:
Let Larry Fitch retrieve, insert, update, and delete data from the WESTREPS table, and let him grant retrieval permission to other users.
GRANT SELECT
ON WESTREPS
TO LARRY
WITH GRANT OPTION
GRANT INSERT, DELETE, UPDATE
ON WESTREPS
TO LARRY
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.