[ http://issues.apache.org/jira/browse/DERBY-1782?page=all ]
Rick Hillegas updated DERBY-1782: --------------------------------- Downgrading the urgency of this issue. Although it violates the ANSI rules, I don't think this issue should block the release at this point. > When a privilege is revoked at table level, Derby should only drop objects > that require that particular privilege and not all the objects that require > some form of privilege on that table. > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > Key: DERBY-1782 > URL: http://issues.apache.org/jira/browse/DERBY-1782 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.1.0 > Reporter: Mamta A. Satoor > Fix For: 10.2.1.0 > > > Views/triggers/constraints can depend on different privileges at table level. > As per SQL specification, if the required privilege is revoked, the dependent > objects(Views, triggers and constraints) should get dropped automatically. > Derby 10.2 supports this partially. In Derby 10.2, when any granted privilege > is revoked at a table level, all the objects(views, triggers and constraints) > that require any kind of privilege on that table get dropped. > eg > user1 > create table t1 > grant select, update on t1 to user2 > user2 > create view v1 as select * from user1.t1 -- this view requires SELECT > privilege on user1.t1 and doesn't rely on UPDATE privilege on user1.t1 > user1 > revoke update on t1 from user2 -- in Derby 10.2, this ends up dropping the > view user2.v1 even though it does not rely on that particular privilege on > user1.t1 > Similar behavior exists for column level privileges. When a privilege is > revoked at column level, we should only drop objects that require that > privilege on that particular column list. In Derby 10.2, we end up dropping > all the objects that rely on that privilege type even though they do not > require the particular columns on which privilege is getting revoked. > eg > user1 > create table t1(c11 int, c12 int, c13 int) > grant select(c11,c12) on t1 to user2 > user2 > create view v1 as select c11 from user1.t1 > -- the view above requires SELECT privilege on column c11 in user1.t1 and > doesn't rely on SELECT privilege on column c12 in user1.t1 > user1 > revoke select(c12) on t1 from user2 > -- in Derby 10.2, the above revoke ends up dropping the view user2.v1 even > though it does not rely on SELECT privilege on column c12 in user1.t1 > Both of these behavior manifest from how the permission related system tables > are designed and how Derby keeps the permission dependency information for > the objects. Some solutions for this problem are discussed in DERBY-1539. -- 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
