[jira] [Closed] (DERBY-1631) Derby needs to support drop view cascade in order for revoke privilege to function correctly

["Kim Haase (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-1631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kim Haase closed DERBY-1631.
----------------------------
    Resolution: Fixed

The documentation seems to have been fixed as part of DERBY-5591 (10.9).

There seem to be no plans to implement DROP VIEW CASCADE.

> Derby needs to support drop view cascade in order for revoke privilege to function correctly
> --------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1631
>                 URL: https://issues.apache.org/jira/browse/DERBY-1631
>             Project: Derby
>          Issue Type: Improvement
>          Components: Documentation, SQL
>    Affects Versions: 10.2.1.6
>            Reporter: Mamta A. Satoor
>              Labels: derby_triage10_11
>
> In Derby, you can not drop a view if there is another view based on it. The dependent view has to be dropped before the provider view can be dropped
> eg
> create table t1
> create view v1 as select * from t1
> create view v2 as select * from v1
> drop view v1
> -- the drop view v1 will fail because v2 depends on view v1
> This behavior causes a problem with revoke privilege. If a view is dependent on a privilege, then the revoke of the privilege is supposed to drop the view. But that will not happen if there is another view dependent on the already defined view
> eg
> user1
> create table t1
> grant select on t1 to user2
> user2
> create view v1 as select * from t1
> create view v2 as select * from v1
> user1
> revoke select on t1 from user2
> -- the above revoke statement will fail because when it tries to dop view v1, it can't do so because view v2 depends on v1
> -- the error message returned will be
> ERROR X0Y23: Operation 'DROP VIEW' cannot be performed on object 'V1' because VIEW 'V2' is dependent on that object.
> Until this issue is resolved. we should document this behavior in grant/revoke + view description
> I will attach this Jira entry to DERBY-1611 which is the higher level jira entry for views and revoke privilege



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)