Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Native SQL
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      This was first logged over on Pentaho:
      http://jira.pentaho.org/browse/BISERVER-725

      I created the test case AccessControlTest.testNonEmptyAccess() to demonstrate the problem.

        Activity

        Hide
        Mondrian Importer User added a comment -
        {willgorman}, 02/07/2008: IP, Artifact Created: 64.132.248.34 |
        {jhyde}, 02/08/2008: IP, Comment Added: 69.230.120.162 |
        {jhyde}, 03/22/2008: IP, Comment Added: 64.81.57.79 |
        {jhyde}, 03/22/2008: resolution_id, 100 |
        {jhyde}, 03/22/2008: category_id, 100 |
        {jhyde}, 03/22/2008: assigned_to, 100 |
        {jhyde}, 03/22/2008: status_id, 1 |
        {jhyde}, 03/22/2008: close_date, 0
        Show
        Mondrian Importer User added a comment - {willgorman}, 02/07/2008: IP, Artifact Created: 64.132.248.34 | {jhyde}, 02/08/2008: IP, Comment Added: 69.230.120.162 | {jhyde}, 03/22/2008: IP, Comment Added: 64.81.57.79 | {jhyde}, 03/22/2008: resolution_id, 100 | {jhyde}, 03/22/2008: category_id, 100 | {jhyde}, 03/22/2008: assigned_to, 100 | {jhyde}, 03/22/2008: status_id, 1 | {jhyde}, 03/22/2008: close_date, 0
        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 02/08/2008: Logged In: YES
        user_id=312935
        Originator: NO

        Good catch. I hadn't thought of that one!

        One approach to fixing this would be to simply veto native SQL generation if there is any access-control going on.

        More sophisticated would be to try to convert the access-control into MDX. For example, consider

        SELECT [Product].Children on Columns,
         NON EMPTY [Customers].Members on Rows
        FROM [Sales]

        where [Product] is access controlled. We could expand

        NON EMPTY [Customers].Members

        to

        Filter([Customers].Members,
          EXISTS [Product].Children)

        then expand access control on the [Product] dimension, to

        Filter(
          [Customers].Members,
          EXISTS Filter(
            [Product].Children,
            Ancestor([Product].Member, [Product].[Brand])
              IS [Product].[Beer].[Budweiser]))

        This expression is no longer access-controlled, and is therefore eligible for native SQL expansion.

        Certain forms of access-control would not be translatable into MDX, or the MDX might be too complicated to translate into SQL. But this is a unifying approach, and a good start.

        By the way, I use the EXISTS function here. I don't know that I've used the syntax exactly right, but it's a powerful primitive that we need to start using more. Microsoft essentially replace NonEmptyCrossjoin with EXISTS from SSAS 2005 onwards, because it is more powerful and theoretically better behaved. We should be doing the same.
        Show
        Mondrian Importer User added a comment - {jhyde}, 02/08/2008: Logged In: YES user_id=312935 Originator: NO Good catch. I hadn't thought of that one! One approach to fixing this would be to simply veto native SQL generation if there is any access-control going on. More sophisticated would be to try to convert the access-control into MDX. For example, consider SELECT [Product].Children on Columns,  NON EMPTY [Customers].Members on Rows FROM [Sales] where [Product] is access controlled. We could expand NON EMPTY [Customers].Members to Filter([Customers].Members,   EXISTS [Product].Children) then expand access control on the [Product] dimension, to Filter(   [Customers].Members,   EXISTS Filter(     [Product].Children,     Ancestor([Product].Member, [Product].[Brand])       IS [Product].[Beer].[Budweiser])) This expression is no longer access-controlled, and is therefore eligible for native SQL expansion. Certain forms of access-control would not be translatable into MDX, or the MDX might be too complicated to translate into SQL. But this is a unifying approach, and a good start. By the way, I use the EXISTS function here. I don't know that I've used the syntax exactly right, but it's a powerful primitive that we need to start using more. Microsoft essentially replace NonEmptyCrossjoin with EXISTS from SSAS 2005 onwards, because it is more powerful and theoretically better behaved. We should be doing the same.
        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 03/22/2008: Logged In: YES
        user_id=312935
        Originator: NO

        Fixed in change 10748, will be in mondrian-3.0.1.
        Show
        Mondrian Importer User added a comment - {jhyde}, 03/22/2008: Logged In: YES user_id=312935 Originator: NO Fixed in change 10748, will be in mondrian-3.0.1.

          People

          • Assignee:
            Julian Hyde
            Reporter:
            Will Gorman
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: