Table-Level Privileges

Table-level privileges, also called table privileges, specify which operations a user or role can perform on a table or view in the database. You can use a synonym to specify the table or view on which you grant or revoke table privileges.

Select, Update, and References privileges can be granted on a subset of the columns of a table or view, but can be revoked only for all columns. If Select privileges are revoked from a user for a table that is referenced in the SELECT statement defining a view that the same user owns, then that view is dropped, unless it also includes columns from tables in another database.

For table objects that the CREATE EXTERNAL TABLE statement has registered in the current database, only the Select privilege and the Insert privilege are supported; no other table or column access privileges can be granted or revoked.

Use the following syntax to specifying which table-level privileges to revoke from one or more users or roles: 
(1)
Table-Level Privileges

1  ALL!  PRIVILEGES
2.1+ ,
2.2.1 INSERT
2.2.1 DELETE
2.2.1 UPDATE
2.2.1 1
2.2.2.1 SELECT
2.2.2.1 ALTER
2.2.2.1 INDEX
2.2.2.1 REFERENCES
2.2.2.1  UNDER
2  ON
2?  owner .
1 table
1 view
1 synonym
Notes:
  • 1 HCL OneDB™ extension
Element Description Restrictions Syntax
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
In one REVOKE statement, you can list one or more of the following keywords to specify the privileges on the specified table to be revoked from the users or roles.
Privilege Effect after REVOKE
INSERT User cannot insert rows.
DELETE User cannot delete rows.
SELECT User cannot display data retrieved by a SELECT statement.
UPDATE User cannot change column values.
INDEX User cannot create permanent indexes. You must have the Resource privilege to take advantage of the Index privilege. (But any user who has the Connect privilege can create indexes on temporary tables.)
ALTER The holder cannot add or delete columns, modify column data types, add or delete constraints, change the locking mode of a table from PAGE to ROW, nor add or drop a corresponding named ROW type table. The user also cannot enable or disable indexes, constraints, nor triggers, as described in SET Database Object Mode statement.
Privilege Effect after REVOKE
REFERENCES User cannot reference columns in referential constraints. You must also have the Resource privilege on the database to take advantage of the References privilege on tables. (You can add, however, a referential constraint during an ALTER TABLE statement. without holding the Resource privilege on the database.) Revoking the References privilege disallows cascading DELETE operations.
UNDER User cannot create subtables under a typed table.
ALL This removes all of the table privileges that are listed above. (Here the PRIVILEGES keyword is optional. )

See also Table-Level Privileges.

If a user receives the same privilege from two different grantors and one grantor revokes the privilege, the grantee still has the privilege until the second grantor also revokes the privilege. For example, if both you and a DBA grant the Update privilege on your table to ted, both you and the DBA must revoke the Update privilege to prevent ted from updating your table.

If user ted holds the same privileges through a role or as PUBLIC, however, this REVOKE operation does not prevent ted from exercising the Update privilege.