Table-specific privileges can be granted to users so they can view, add, delete, or create indexes for data within a table. Privileges can also be granted to allow users to refer to a table from another table's constraint definitions.
Syntax
The GRANT statement syntax for granting table-specific privileges is:
GRANT {privilege[, privilege], ...| ALL }ON table_name
TO {username[, username], ...| PUBLIC }[ WITH GRANT OPTION ] ;
In this instance, a DBA restricts the types of activities a user is allowed to perform on a table. In the following example, 'GSP' is given permission to update the item name, item number, and catalog descriptions found in the item table.
Note: By employing the WITH GRANT OPTION clause, you enable a user to grant the same privilege he or she has been granted to others. This clause should be used carefully due to its ability to affect database security.
Examples: GRANT UPDATE statement
The following example illustrates the granting of table-specific privileges.
The GRANT UPDATE statement has limited GSP's ability to interact with the item table.
GRANT UPDATE
ON Item (ItemNum, ItemName, CatDescription)
TO 'GSP';
Now, if GSP attempts to update a column to which he has not been granted access, the database will return the error message in the following example.
