db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Date Fri, 01 Jul 2011 11:12:31 GMT

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

Dag H. Wanvik updated DERBY-5292:
---------------------------------

    Attachment: derby5292c.stat
                derby5292c.diff

Uploading a patch "c", which removes the overrides in favour of using a visitor always. This
approach failed at first, because FromSubquery did not implement Visitable#acceptChildren,
so I added that. I added a new test fixture to GrantRevokeTest: testViewDefinersRights based
on the repro.

Btw, it is not the first time we have had to add more implementations of acceptChildren to
the node types. Maybe it's time to go through them to verify that all implement it correctly..

Running regressions over again.

> SQLAuthorisation and views
> --------------------------
>
>                 Key: DERBY-5292
>                 URL: https://issues.apache.org/jira/browse/DERBY-5292
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.7.1.1
>         Environment: windows xp
>            Reporter: Thomas Hill
>         Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff,
derby5292c.stat, output.txt, script.txt
>
>
> Hi,
> in some cases there seem to be issues with SQLAuthorisation in conjunction with database
VIEWS.
> Please see attached files as repro (script.txt has only the SQL I executed, output is
the output resulting when running the script). 
> I would think identical results should be returned in all cases and independent on how
the view has been defined, but this to my surprise not being the case:
> 1) Trying to select from view appl."VW_MyTasks" - which is a simple view defined on just
one table - leads to expected results, i.e. "my" tasks are being returned.
> 2) Trying to select from view appl."VW_MyPriorityTasks - which is a view defined on two
joined tables without using an inner join clause - leads to expected results, i.e. "my" priority
tasks are being returned.
> 3) Trying to select from view appl."VW2_MyPriorityTasks - which is the same view but
now the two tables joined using an inner join clause - leads to an error and no tasks returned
(when the same results as in 2) above were expected).
> 4) Trying to select from view appl."VW3_MyPriorityTasks" - which is a view defined using
a subselect - also unexpectedly leads to an error.
> Note: While I could rewrite each inner join clause with changing the syntax like in 2)
above, this provides a simple work-around for such cases. May be there is a work-around for
subselects also (not sure if every subselect could be rewritten to a join?). However when
depending on using EXISTS constructs in the query there unfortunately is no way (I would know
of) to get around this problem. Unfortuanetly a view that makes use of EXISTS is also one
I would need to define in my data base...
> Thanks

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message