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 ] ; 

This is the syntax for the privilege value:

 { SELECT | INSERT | DELETE | INDEX 
      | UPDATE [ ( column , column , ... ) ]
      | REFERENCES [ ( column , column , ... ) ]}

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.

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.

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-20228
[JDBC Progress Driver}:Access Denied (Authorisation
failed) (7512)