db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-1782) 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.
Date Wed, 30 Aug 2006 13:47:22 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1782?page=all ]

Rick Hillegas updated DERBY-1782:
---------------------------------

          Urgency: Urgent
    Fix Version/s: 10.2.1.0

Assigning to 10.2 since the ANSI spec requires this behavior.

> 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: Improvement
>          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

        

Mime
View raw message