When you create a table with the CREATE TABLE statement, you are the table owner and automatically receive all table-level privileges. You cannot transfer ownership to another user, but you can grant table-level privileges to another user or to a role. (See, however, RENAME TABLE statement, which can change both the name and the ownership of a table.)
|column||Column on which the References, Select, or Update privilege is granted. Default scope is all columns of table, view, or synonym.||Must be a column of the table, view, or synonym||Identifier|
|owner||Name of the user who owns the table, view, or synonym||Must be a valid authorization identifier||Owner name|
|synonym, table, view||Synonym, table, or view on which privileges are granted.||Must exist in the current database||Identifier|
|INSERT||Lets you insert rows|
|DELETE||Lets you delete rows|
|SELECT||Lets you access any column in SELECT statements. You can restrict the Select privilege to one or more columns by listing the columns.|
|UPDATE||Lets you access any column in UPDATE statements. You can restrict the Update privilege to one or more columns by listing the columns.|
|REFERENCES||Lets you define referential constraints on columns. You must have the Resource privilege to take advantage of the References privilege. (You can add, however, a referential constraint during an ALTER TABLE statement without holding the Resource privilege on the database.) You need only the References privilege to indicate cascading deletes. You do not need the Delete privilege to place cascading deletes on a table. You can restrict the References privilege to one or more columns by listing the columns.|
|INDEX||Lets you create permanent indexes. You must have the Resource privilege to use the Index privilege. (Any user with the Connect privilege can create an index on temporary tables.)|
|ALTER||Lets you add or delete columns, modify column data
types, add or delete constraints, change the locking mode of the table
from PAGE to ROW, or add or drop a corresponding ROW data type for
your table. It also lets you enable or disable indexes, constraints
and triggers, as described in SET Database Object Mode statement.
You must have the Resource privilege to use the Alter privilege. In addition, you also need the Usage privilege for any user-defined data type affected by the ALTER TABLE statement.
|UNDER||Lets you create sub-tables under a typed table.|
|ALL||Provides all privileges listed above. The PRIVILEGES keyword is optional.|
You can narrow the scope of a Select, Update, or References privilege by specifying the columns to which the privilege applies.
Specify the keyword PUBLIC as user if you intend the GRANT statement to apply to all users.
Some simple examples that follow illustrate how to give table-level privileges with the GRANT statement.
GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname) ON customer TO mary, john;
GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname) ON customer TO PUBLIC;
GRANT UNDER ON tab1 TO john;
After receiving the Under privilege on table tab1, user john can create one or more subtables under tab1.