1.1.1

Table-Level Privilege Limitations
All of the table-level privilege types for a specified grantee and table ID are stored in one row in the
SYSTABLEPERMS system table. For example, when user2 is granted the SELECT and DELETE privileges
on table user1.t1, a row is added to the SYSTABLEPERMS table. The GRANTEE field contains user2 and the
TABLEID contains user1.t1. The SELECTPRIV and DELETEPRIV fields are set to Y. The remaining privilege
type fields are set to N.
When a grantee creates an object that relies on one of the privilege types, the engine tracks the dependency of
the object on the specific row in the SYSTABLEPERMS table. For example, user2 creates the view v1 by using
the statement SELECT * FROM user1.t1, the dependency manager tracks the dependency of view v1 on the
row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1). The dependency manager knows only
that the view is dependent on a privilege type in that specific row, but does not track exactly which privilege
type the view is dependent on.
When a REVOKE statement for a table-level privilege is issued for a grantee and table ID, all of the objects that
are dependent on the grantee and table ID are dropped. For example, if user1 revokes the DELETE privilege on
table t1 from user2, the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1) is modified by
the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1
and the view is dropped even though the view is not dependent on the DELETE privilege for GRANTEE(user2),
TABLEID(user1.t1).
Column-Level Privilege Limitations
Only one type of privilege for a specified grantee and table ID are stored in one row in the SYSCOLPERMS
system table. For example, when user2 is granted the SELECT privilege on table user1.t1 for columns c12 and
c13, a row is added to the SYSCOLPERMS. The GRANTEE field contains user2, the TABLEID contains
user1.t1, the TYPE field contains S, and the COLUMNS field contains c12, c13.
When a grantee creates an object that relies on the privilege type and the subset of columns in a table ID, the
engine tracks the dependency of the object on the specific row in the SYSCOLPERMS table. For example, user2
creates the view v1 by using the statement SELECT c11 FROM user1.t1, the dependency manager tracks the
dependency of view v1 on the row in SYSCOLPERMS for GRANTEE(user2), TABLEID(user1.t1), TYPE(S).
The dependency manager knows that the view is dependent on the SELECT privilege type, but does not track
exactly which columns the view is dependent on.
When a REVOKE statement for a column-level privilege is issued for a grantee, table ID, and type, all of the
objects that are dependent on the grantee, table ID, and type are dropped. For example, if user1 revokes the
SELECT privilege on column c12 on table user1.t1 from user2, the row in SYSCOLPERMS for GRANTEE(user2),
TABLEID(user1.t1), TYPE(S) is modified by the REVOKE statement. The dependency manager sends a revoke
invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on
the column c12 for GRANTEE(user2), TABLEID(user1.t1), TYPE(S).
Examples
To revoke the SELECT privilege on table t from the authorization IDs maria and harry:
REVOKE SELECT ON TABLE t FROM sam,bob;
To revoke the UPDATE and TRIGGER privileges on table t from the authorization IDs
anita and zhi:
REVOKE UPDATE, TRIGGER ON TABLE t FROM sagarika,czhu;
To revoke the SELECT privilege on table s.v from all users:
REVOKE SELECT ON TABLE test.sample FROM PUBLIC;
527
SQL Language Reference