Try OpenEdge Now
skip to main content
SQL Development
Data Control Language and Security : Granting privileges : GRANT statement : Table-specific privileges
Table-specific privileges
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.
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:
   | 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.
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.
ON Item (ItemNum, ItemName, CatDescription)
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 ===
[JDBC Progress Driver}:Access Denied (Authorisation
failed) (7512)