Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • 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.7.1.1, 10.8.1.2
    • Fix Version/s: 10.8.2.2, 10.9.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      windows xp
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard, Security, Seen in production

      Description

      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

      1. output.txt
        6 kB
        Thomas Hill
      2. script.txt
        4 kB
        Thomas Hill
      3. derby5292a.diff
        0.6 kB
        Dag H. Wanvik
      4. derby5292b.diff
        6 kB
        Dag H. Wanvik
      5. derby5292b.stat
        0.6 kB
        Dag H. Wanvik
      6. derby5292c.diff
        13 kB
        Dag H. Wanvik
      7. derby5292c.stat
        0.8 kB
        Dag H. Wanvik
      8. derby5292d.diff
        13 kB
        Dag H. Wanvik
      9. derby5292d.stat
        0.7 kB
        Dag H. Wanvik

        Activity

        Thomas Hill created issue -
        Thomas Hill made changes -
        Field Original Value New Value
        Attachment output.txt [ 12483462 ]
        Attachment script.txt [ 12483463 ]
        Dag H. Wanvik made changes -
        Bug behavior facts [Deviation from standard, Security, Seen in production]
        Issue & fix info [Repro attached]
        Dag H. Wanvik made changes -
        Attachment derby5292a.diff [ 12484754 ]
        Dag H. Wanvik made changes -
        Issue & fix info [Repro attached] [Patch Available, Repro attached]
        Dag H. Wanvik made changes -
        Attachment derby5292b.diff [ 12484769 ]
        Attachment derby5292b.stat [ 12484770 ]
        Dag H. Wanvik made changes -
        Attachment derby5292c.diff [ 12484858 ]
        Attachment derby5292c.stat [ 12484859 ]
        Dag H. Wanvik made changes -
        Affects Version/s 10.8.1.2 [ 12316362 ]
        Affects Version/s 10.6.2.1 [ 12315343 ]
        Affects Version/s 10.6.1.0 [ 12313727 ]
        Affects Version/s 10.5.3.0 [ 12314117 ]
        Affects Version/s 10.5.2.0 [ 12314116 ]
        Affects Version/s 10.5.1.1 [ 12313771 ]
        Affects Version/s 10.4.2.0 [ 12313345 ]
        Affects Version/s 10.4.1.3 [ 12313111 ]
        Affects Version/s 10.3.3.0 [ 12313142 ]
        Affects Version/s 10.3.2.1 [ 12312876 ]
        Affects Version/s 10.3.1.4 [ 12312590 ]
        Affects Version/s 10.2.2.0 [ 12312027 ]
        Affects Version/s 10.2.1.6 [ 11187 ]
        Dag H. Wanvik made changes -
        Attachment derby5292d.diff [ 12485134 ]
        Attachment derby5292d.stat [ 12485135 ]
        Dag H. Wanvik made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Issue & fix info [Repro attached, Patch Available] [Repro attached]
        Fix Version/s 10.9.0.0 [ 12316344 ]
        Resolution Fixed [ 1 ]
        Dag H. Wanvik made changes -
        Fix Version/s 10.8.1.6 [ 12316676 ]
        Myrna van Lunteren made changes -
        Fix Version/s 10.8.2.0 [ 12317955 ]
        Fix Version/s 10.8.1.6 [ 12316676 ]
        Myrna van Lunteren made changes -
        Fix Version/s 10.8.2.2 [ 12317968 ]
        Fix Version/s 10.8.2.0 [ 12317955 ]
        Kathey Marsden made changes -
        Assignee Dag H. Wanvik [ dagw ]
        Knut Anders Hatlen made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Gavin made changes -
        Workflow jira [ 12617575 ] Default workflow, editable Closed status [ 12802792 ]

          People

          • Assignee:
            Dag H. Wanvik
            Reporter:
            Thomas Hill
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development