[ http://issues.apache.org/jira/browse/DERBY-1646?page=comments#action_12430172 ] Mamta A. Satoor commented on DERBY-1646: ----------------------------------------
One final area that requires documentation has to do with revoke privilege on table/column levels. Currently, all the privilege types (SELECT, DELETE, INSERT, UPDATE, REFERENCES, TRIGGER) for a given grantee and tableid are kept in one row in SYSTABLEPERMS. ie say user user2 has SELECT and DELETE privileges on table user1.t1, then the row in SYSTABLEPERMS for grantee user2 and table user1.t1 will have SELECTPRIV and DELETEPRIV set to "Y" but the rest of the privilege type fields, INSERTPRIV, UPDATEPRIV, REFERENCESPRIV and TRIGGERPRIV will be set to "N". Later, when an object is created which relies on any granted privilege type for a given tableid and grantee, the Derby engine tracks the dependency of the newly created object on the specific row in SYSTABLEPERMS ie the dependency manager only knows that the object is dependent on some privilege type in that specific row but it doesn't know exactly what privilege type. Later, when a revoke table level privilege is issued for the given grantee and tableid, all the dependents of grantee and tableid get notified. All the dependents might not need the specific privilege type being revoked for the given grantee and tableid, but the dependents of that row in SYSTABLEPERMS will still end up dropping themselves. This behavior of Derby should be enhanced in future so that real dependents of a privilege type on a given grantee, tableid get notified but until then, we should document this behavior. Following is an example showing the current behavior for privilege granted at table level. user1 create table t1 grant select, delete on t1 to user2 -- one row in SYSTABLEPERMS for grantee(user2), table(t1) with SELECTPRIV and DELETEPRIV set to "Y" user2 create view v1 as select * from user1.t1 -- dependency manager tracks dependency of v1 on row in SYSTABLEPERMS for grantee(user2), table(t1) -- Note that dependency manager does not track dependency on specific privilege type for this row which in this case is SELECTPRIV -- Note that view v1 has no requirement for DELETEPRIV user1 revoke delete on t1 from user2 -- since row in SYSTABLEPERMS for grantee(user2), table(t1) got modified because of this revoke, -- dependency manager sends a revoke invalidation message to view user2.v1 and the view ends up dropping itself eventhough it was -- never dependent on DELETEPRIV -- Derby behavior should be modified in future such that the revoke delete above does not end up dropping view user2.v1 -- View user2.v1 should get dropped automatically, only if revoke select on t1 from user2 is issued. Until we implement this behavior, -- we should document the current behavior somewhere. Similar behavior exists for column level privileges. Currently, if a privilege is granted at columns level, then we create a row in SYSCOLPERMS for grantee, tableid, columns on which permission is granted and permission type. ie say user user2 has SELECT privilege on table user1.t1's columns c11, c12 but not on columns c13, c14, then the row in SYSCOLPERMS will have privilege type as SELECT for grantee user2, table user1.t1 and column bits set for columns c11, c12. Later, when an object is created which relies on a granted privilege type for a given grantee and subset of columns in a tableid, the Derby engine tracks the dependency of the newly created object on the specific row in SYSCOLPERMS ie the dependency manager only knows that the object is dependent on some columns in that specific row but it doesn't know exactly which columns. Later, when a revoke column level privilege is issued for the given grantee and tableid and privilege type, all the dependents of grantee, tableid and privilege type get notified. All the dependents might not need the specific columns whose privilege is being revoked for the given grantee and tableid and privilege type, but the dependents of that row in SYSCOLPERMS will still end up dropping themselves. This behavior of Derby should be enhanced in future so that real dependents of the column list for a privilege type on a given grantee, tableid get notified but until then, we should document this behavior. Following is an example showing the current behavior for privilege granted at column level. user1 create table t1(c11, c12, c13, c14) grant select(c11, c12) on t1 to user2 -- one row in SYSCOLPERMS for grantee(user2), table(t1), columns(c11, c12) and privilege type(SELECT) user2 create view v1 as select c11 from user1.t1 -- dependency manager tracks dependency of v1 on row in SYSCOLPERMS for grantee(user2), table(t1) -- and privilege type SELECT. Note that dependency manager does not track dependency on specific column list of table t1 in SYSCOLPERMS/ -- Note that view v1 has no requirements for SELECT privilege on column c12 in user1.t1 user1 revoke select(c12) on t1 from user2 -- since row in SYSCOLPERMS for grantee(user2), table(t1), privilege type(SELECT) got modified because -- of this revoke, dependency manager send a revoke invalidation message to view user2.v1 and view ends up dropping itself even though it -- was never dependent on SELECT privilege on column c12 of table user1.t1 -- Derby behavior should be modified in future such that the revoke select on column c12 above does not end up dropping view user2.v1 -- View user2.v1 should get dropped automatically only if revoke select on column c11 on t1 from user2 is ussed. But until this behavior -- is implemented, we should document the current behavior somewhere. Laura, please let me know if this is unclear. An understanding of the structure on SYSTABLEPERMS and SYSCOLPERMS might make it easier to understand the current behavior and ideal behavior. > Documentation to address Grant/Revoke Authorization for > views/triggers/constraints/routines(DERBY-1330) > ------------------------------------------------------------------------------------------------------- > > Key: DERBY-1646 > URL: http://issues.apache.org/jira/browse/DERBY-1646 > Project: Derby > Issue Type: New Feature > Components: Documentation > Affects Versions: 10.2.1.0 > Reporter: Mamta A. Satoor > Assigned To: Laura Stewart > > Creating a separate jira entry for documentation of Grant/Revoke > Authorization for views/triggers/constraints/routines(Engine changes are > going as part of DERBY-1330). > Will link this jira entry to DERBY-1330 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
