You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Thomas Hill (JIRA)" <ji...@apache.org> on 2011/06/22 19:06:47 UTC
[jira] [Created] (DERBY-5292) SQLAuthorisation and views
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
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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13059402#comment-13059402 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
Committed version "d" as svn 1142635. This is a back-port candidate, so I am not closing it yet.
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Fix For: 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13059394#comment-13059394 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
Yes, I saw those added properties, too.. Not sure how they got int there, maybe just the presence of those files is enough when I produced the diff? As for final, I had a plan to do that but forgot it. Thanks for spotting it! Regressions ran OK.
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> 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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13059584#comment-13059584 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
Backported to 10.8 branch as svn 1142773. Thomas, feel free to close this issue if you believe the issue has been resolved.
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Fix For: 10.8.1.6, 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Assigned] (DERBY-5292) SQLAuthorisation and views
Posted by "Kathey Marsden (Assigned) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reassigned DERBY-5292:
-------------------------------------
Assignee: Dag H. Wanvik
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Assignee: Dag H. Wanvik
> Fix For: 10.8.2.2, 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13057727#comment-13057727 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
The problem appears to be a missing definition in TableOperatorNode of disablePrivilegeCollection. For views the premissions collection is disabled from tables in the query from-list with this QueryTreeNode#disablePrivilegeCollection. In the repro, where explicit JOINs are used, the disabling is not propagated down to the left and right tables of the join. The same error would appear for other subclasses of TableOperatorNode, e.g. set operations.
> 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: 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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Thomas Hill (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13059620#comment-13059620 ]
Thomas Hill commented on DERBY-5292:
------------------------------------
Amazed again about level of support and timely feedback I am receiving from you and Derby community.
As I have never built Derby from source (and am not technical enough to start such an undertaking) I will need to wait for binaries before I can test. I think you advised me last time that there are period binary builds which are being produced? (but can't remember details). In regards to seeing the fix included in a *production* release, is 'fix version' 10.8.6 on this Jira indicating that this won't happen prior to this version?, i.e. not as part of the next maintenance release 10.8.2 tetatively scheduled for middle Sep 2011?
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Fix For: 10.8.1.6, 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5292:
---------------------------------
Affects Version/s: 10.2.1.6
10.2.2.0
10.3.1.4
10.3.2.1
10.3.3.0
10.4.1.3
10.4.2.0
10.5.1.1
10.5.2.0
10.5.3.0
10.6.1.0
10.6.2.1
10.8.1.2
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> 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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13059875#comment-13059875 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
Thanks, Thomas! Yes, 'fix version' 10.8.1.6 does mean it will be part of the next maintenance release :) You could pick up
tinderbox jars for head of the 10.8 branch here (debug jars) and see if they solve your issue (they should!):
https://builds.apache.org/job/Derby-branch-10.8/ws/10.8-head/jars/sane/
(Please note that these jars are not intended for production.)
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Fix For: 10.8.1.6, 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5292:
---------------------------------
Fix Version/s: 10.8.1.6
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Fix For: 10.8.1.6, 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13057736#comment-13057736 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
Actually, the patch only solved the first failing case in the repro, so the patch is insufficient.
> 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, 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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13058591#comment-13058591 ]
Knut Anders Hatlen commented on DERBY-5292:
-------------------------------------------
The patch looks fine to me. There are two added svn:ignore properties though, that you may want to revert before committing (#SelectNode.java# and #GrantRevokeTest.java#).
Perhaps we should also make QueryTreeNode.disablePrivilegeCollection() final now, so that we get a compile-time check that we haven't forgotten it somewhere or accidentally overridden it. And possibly also make it package private since it's only used in impl.sql.compile.
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> 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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Thomas Hill (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Hill updated DERBY-5292:
-------------------------------
Attachment: script.txt
output.txt
> 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: 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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5292:
---------------------------------
Attachment: derby5292d.stat
derby5292d.diff
Uploaded version "d" of this patch which removes the
extraneous properties settings and adds a "final" to QTN#disablePrivilegeCollection and makes the method package private.
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5292:
---------------------------------
Issue & fix info: [Repro attached]
Bug behavior facts: [Deviation from standard, Security, Seen in production]
> 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: 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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5292:
---------------------------------
Attachment: derby5292b.stat
derby5292b.diff
Uploading patch "b", which makes both failing cases work.
The next missing pieces were sub-selects inside WHERE clause (and also HAVING clause, I found). I still need to add tests. Instead of populating the ValueNode hierarchy with new overrides, I used a CollectNodesVisitor so reach nested SELECT(s) to disable permission collection for them.
> 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, 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
[jira] [Issue Comment Edited] (DERBY-5292) SQLAuthorisation and
views
Posted by "Thomas Hill (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13059620#comment-13059620 ]
Thomas Hill edited comment on DERBY-5292 at 7/4/11 9:42 PM:
------------------------------------------------------------
Amazed again about level of support and timely feedback I am receiving from you and Derby community - thanks for this.
As I have never built Derby from source (and am not technical enough to start such an undertaking) I will need to wait for binaries before I can test. I think you advised me last time that there are periodic binary builds which are being produced? (but unfortunately I can't remember details). In regards to seeing the fix included in a *production* release, is 'fix version' 10.8.1.6 and 10.9.0 on this Jira indicating that this will be part of the next maintenance release 10.8.2 tentatively scheduled for middle Sep 2011?
was (Author: thomashill):
Amazed again about level of support and timely feedback I am receiving from you and Derby community.
As I have never built Derby from source (and am not technical enough to start such an undertaking) I will need to wait for binaries before I can test. I think you advised me last time that there are period binary builds which are being produced? (but can't remember details). In regards to seeing the fix included in a *production* release, is 'fix version' 10.8.6 on this Jira indicating that this won't happen prior to this version?, i.e. not as part of the next maintenance release 10.8.2 tetatively scheduled for middle Sep 2011?
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Fix For: 10.8.1.6, 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13057376#comment-13057376 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
Thanks for reporting this, Thomas. I can reproduce on trunk. Looks like a bug to me.
> 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: 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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5292:
---------------------------------
Issue & fix info: [Patch Available, Repro attached] (was: [Repro attached])
> 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, 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
[jira] [Resolved] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik resolved DERBY-5292.
----------------------------------
Resolution: Fixed
Fix Version/s: 10.9.0.0
Issue & fix info: [Repro attached] (was: [Repro attached, Patch Available])
> 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.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
> Environment: windows xp
> Reporter: Thomas Hill
> Fix For: 10.9.0.0
>
> Attachments: derby5292a.diff, derby5292b.diff, derby5292b.stat, derby5292c.diff, derby5292c.stat, derby5292d.diff, derby5292d.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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13058232#comment-13058232 ]
Dag H. Wanvik commented on DERBY-5292:
--------------------------------------
Yes, that could be a natural further improvement, I agree. I didn't go there yet in the interest of a minimal change, but I think it's worth trying. It would definitely make the code simpler.
> 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, 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
[jira] [Commented] (DERBY-5292) SQLAuthorisation and views
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13057866#comment-13057866 ]
Knut Anders Hatlen commented on DERBY-5292:
-------------------------------------------
Would an alternative be to remove all the overrides and use the visitor for all nodes? Since the disablePrivilegeCollection() method is defined in QueryTreeNode, and ValueNode is a subclass of QueryTreeNode, it feels a bit inconsistent to require overrides only in the non-ValueNode subclasses. Removing the overrides and relying on the visitor might reduce this code to just a single invocation of the visitor (with QueryTreeNode.class as argument instead of SelectNode.class) in FromBaseTable.bindNonVTITables().
> 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, 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
[jira] [Updated] (DERBY-5292) SQLAuthorisation and views
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-5292:
---------------------------------
Attachment: derby5292a.diff
Uploading a patch which makes the repro script work.
Not for commit, I still need to add new tests and check if there are other similar missing definitions.
> 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, 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