Table-Level Privileges

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.)

A user with the database-level DBA privilege automatically receives all table-level privileges on every table in that database.

Notes:

¹ HCL OneDB™ extension


Element	Description	Restrictions	Syntax
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

The GRANT statement can list one or more of the following keywords to specify the table privileges that you grant to the same users or roles.


Privilege	Effect
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.

The following statement grants the privilege to delete and select values in any column in the table customer to users mary and john. It also grants the Update privilege, but only for columns customer_num, fname, and lname:

GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname)
   ON customer TO mary, john;

To grant the same privileges as those above to all authorized users, use the keyword PUBLIC as the following example shows:

GRANT DELETE, SELECT, UPDATE (customer_num, fname, lname)
   ON customer TO PUBLIC;

For example, suppose a user named mary has created a typed table named tab1. By default, only user mary can create subtables under the tab1 table. If mary wants to grant the ability to create subtables under the tab1 table to a user named john, mary must enter the following GRANT statement:

GRANT UNDER ON tab1 TO john;

After receiving the Under privilege on table tab1, user john can create one or more subtables under tab1.