Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-694

Incorrect handling of child/parent relationship with hierarchy grants

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: 3.5.0 Suite Release
    • Component/s: None
    • Labels:
      None
    • Environment:
      Database mySQL
      Schema: Foodmart
    • Customer Case:

      Description

      (1) Copy the FoodMart.xml, security_example_food_mart.analysisview.xaction and
      security_example_food_mart_expanded.analysisview.xaction files attached to a Pentaho BI server.
      (2) Modify the xactions to point to the correct schema and use role REG1.
      (3) Open the security_example_food_mart analysis view.

      The MDX Query Editor shows this:

      select NON EMPTY {[Measures].[Org Salary]} ON COLUMNS,
      NON EMPTY {([Department].[All Departments].[Store Information Systems], [Employees].[All Employees])} ON ROWS
      from [HR]

      And the output shows this:

      Department Employees Org Salary
      Store Information Systems All Employees $97.20

      (4) Now open the security_example_food_mart_expanded analysis view. Click on the All Employees cell to condense the output to one line.

      The MDX Query Editor shows the same query as we saw before:

      select NON EMPTY {[Measures].[Org Salary]} ON COLUMNS,
      NON EMPTY {([Department].[All Departments].[Store Information Systems], [Employees].[All Employees])} ON ROWS
      from [HR]

      but the output has a different value for Org Salary:

      Department Employees Org Salary
      Store Information Systems All Employees $874.80

      So the 'exact same MDX query' (according to the PUC) produces 'different results' for the 'exact same user'. This can't be right.

      Queries from customer:
      1) The Mondrian schema documentation is ambiguous on this question: If a role restricts access on one Dimension/Hierarchy to certain members, but does not have restrictions on any other dimensions and hierarchies, what does that role see from an MDX query that doesn't include the restricted dimension?

      2) If the role sees full aggregates in such an MDX query, then Mondrian's roles are not useful. Consider a company with two regions, A and B, each with a regional manager. The managers should be restricted to their own regions, but all that Manager A has to do to deduce B's data is look at MDX queries that don't use the dimension with regions.
      1. FoodMart.xml
        33 kB
        Anthony Carter
      2. security_example_food_mart_expanded.analysisview.xaction
        7 kB
        Anthony Carter
      3. security_example_food_mart.analysisview.xaction
        7 kB
        Anthony Carter

        Issue Links

          Activity

          Hide
          Julian Hyde added a comment -
          Regarding two identical queries returning the different results. Are you absolutely sure that they are being run under the same role?

          > If the role sees full aggregates in such an MDX query, then Mondrian's roles are not useful.
          > Consider a company with two regions, A and B, each with a regional manager. The managers
          > should be restricted to their own regions, but all that Manager A has to do to deduce B's data is
          > look at MDX queries that don't use the dimension with regions.

          OLAP access control can have several goals. It's often OK to let people see the grand totals, but you apply access control to prevent them from seeing the details that contribute to those grand totals. E.g. a California sales manager can see that USA sales are $1m and that California sales are $70k but not show any other states. The default rollup policy, 'full' achieves this behavior.

          If you want a manager to be able to see the total of only the members they can see, use rollupPolicy='Partial'.

          MONDRIAN-611 only applies to drill-through. It's not an issue here.
          Show
          Julian Hyde added a comment - Regarding two identical queries returning the different results. Are you absolutely sure that they are being run under the same role? > If the role sees full aggregates in such an MDX query, then Mondrian's roles are not useful. > Consider a company with two regions, A and B, each with a regional manager. The managers > should be restricted to their own regions, but all that Manager A has to do to deduce B's data is > look at MDX queries that don't use the dimension with regions. OLAP access control can have several goals. It's often OK to let people see the grand totals, but you apply access control to prevent them from seeing the details that contribute to those grand totals. E.g. a California sales manager can see that USA sales are $1m and that California sales are $70k but not show any other states. The default rollup policy, 'full' achieves this behavior. If you want a manager to be able to see the total of only the members they can see, use rollupPolicy='Partial'. MONDRIAN-611 only applies to drill-through. It's not an issue here.
          Hide
          Rob Johnson added a comment -
          I opened the support case that generated this ticket, and yes, both queries are being run under the same role. The role is assigned as a session variable when the user logs into the PUC, and is then passed to each action sequence. When I run both action sequences in the same session, I have the problem.

          The rollup policy for the role in the attached FoodMart.xml is set to partial, and that's what is not working. In general, I have seen that a role assignment, with a partial rollup policy on a parent-child hierarchy, does not always work properly on the "All" member, because sometimes that All member shows full totals, not partial totals. This Bug case illustrates the problem with a small example.

          Also, in the line in the problem description where it says

          >>>So the 'exact same MDX query' (according to the PUC) produces 'different results' for the 'exact same user'. This can't be right. <<<

          In the support case, the single-quotes were originally asterisks, which were meant for emphasis. When you read that sentence here with the single quotes, it looks like we're not sure of these things, but in fact we are sure that this is exactly what is going on.
          Show
          Rob Johnson added a comment - I opened the support case that generated this ticket, and yes, both queries are being run under the same role. The role is assigned as a session variable when the user logs into the PUC, and is then passed to each action sequence. When I run both action sequences in the same session, I have the problem. The rollup policy for the role in the attached FoodMart.xml is set to partial, and that's what is not working. In general, I have seen that a role assignment, with a partial rollup policy on a parent-child hierarchy, does not always work properly on the "All" member, because sometimes that All member shows full totals, not partial totals. This Bug case illustrates the problem with a small example. Also, in the line in the problem description where it says >>>So the 'exact same MDX query' (according to the PUC) produces 'different results' for the 'exact same user'. This can't be right. <<< In the support case, the single-quotes were originally asterisks, which were meant for emphasis. When you read that sentence here with the single quotes, it looks like we're not sure of these things, but in fact we are sure that this is exactly what is going on.
          Hide
          Rob Johnson added a comment -
          If you prefer a Mondrian-only example of the partial-rollup/parent-child problem, define the REG1 role in the attached FoodMart.xml as the default role, and run these two queries in Schema Workbench with these results:

          select NON EMPTY {[Measures].[Org Salary]} ON COLUMNS,
          NON EMPTY Hierarchize(Union(Crossjoin({[Department].[All Departments].[Store Information Systems]}, {[Employees].[All Employees]}), Crossjoin({[Department].[All Departments].[Store Information Systems]}, [Employees].[All Employees].Children))) ON ROWS
          from [HR]

          Axis #0:
          {}
          Axis #1:
          {[Measures].[Org Salary]}
          Axis #2:
          {[Department].[All Departments].[Store Information Systems], [Employees].[All Employees]}
          {[Department].[All Departments].[Store Information Systems], [Employees].[All Employees].[Sheri Nowmer]}
          Row #0: $874.80
          Row #1: $97.20

          >>>Mondrian is not filtering on the row with "All Employees."<<<

          select NON EMPTY {[Measures].[Org Salary]} ON COLUMNS,
          NON EMPTY Crossjoin(Hierarchize(Union({[Employees].[All Employees]}, [Employees].[All Employees].Children)), {[Department].[All Departments].[Store Information Systems]}) ON ROWS
          from [HR]

          Axis #0:
          {}
          Axis #1:
          {[Measures].[Org Salary]}
          Axis #2:
          {[Employees].[All Employees], [Department].[All Departments].[Store Information Systems]}
          {[Employees].[All Employees].[Sheri Nowmer], [Department].[All Departments].[Store Information Systems]}
          Row #0: $97.20
          Row #1: $97.20

          >>>Mondrian is filtering on the row with All Employees<<<

          The MDX queries appear to be identical except for the ordering of the dimensions in the rows, yet the Org Salary for All Employees/All Departments has different values ($97.20 vs. $874.80).

          Show
          Rob Johnson added a comment - If you prefer a Mondrian-only example of the partial-rollup/parent-child problem, define the REG1 role in the attached FoodMart.xml as the default role, and run these two queries in Schema Workbench with these results: select NON EMPTY {[Measures].[Org Salary]} ON COLUMNS, NON EMPTY Hierarchize(Union(Crossjoin({[Department].[All Departments].[Store Information Systems]}, {[Employees].[All Employees]}), Crossjoin({[Department].[All Departments].[Store Information Systems]}, [Employees].[All Employees].Children))) ON ROWS from [HR] Axis #0: {} Axis #1: {[Measures].[Org Salary]} Axis #2: {[Department].[All Departments].[Store Information Systems], [Employees].[All Employees]} {[Department].[All Departments].[Store Information Systems], [Employees].[All Employees].[Sheri Nowmer]} Row #0: $874.80 Row #1: $97.20 >>>Mondrian is not filtering on the row with "All Employees."<<< select NON EMPTY {[Measures].[Org Salary]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize(Union({[Employees].[All Employees]}, [Employees].[All Employees].Children)), {[Department].[All Departments].[Store Information Systems]}) ON ROWS from [HR] Axis #0: {} Axis #1: {[Measures].[Org Salary]} Axis #2: {[Employees].[All Employees], [Department].[All Departments].[Store Information Systems]} {[Employees].[All Employees].[Sheri Nowmer], [Department].[All Departments].[Store Information Systems]} Row #0: $97.20 Row #1: $97.20 >>>Mondrian is filtering on the row with All Employees<<< The MDX queries appear to be identical except for the ordering of the dimensions in the rows, yet the Org Salary for All Employees/All Departments has different values ($97.20 vs. $874.80).
          Hide
          Julian Hyde added a comment -
          My hunch is that this issue just affects parent-child hierarchies. Which would be good news. That said, we take security issues seriously and we should fix this ASAP.
          Show
          Julian Hyde added a comment - My hunch is that this issue just affects parent-child hierarchies. Which would be good news. That said, we take security issues seriously and we should fix this ASAP.
          Hide
          Julian Hyde added a comment -
          Fixed in change 13554.
          Show
          Julian Hyde added a comment - Fixed in change 13554.
          Hide
          Mat Lowery added a comment - - edited
          Waiting for 3.6 GA build.
          Show
          Mat Lowery added a comment - - edited Waiting for 3.6 GA build.
          Hide
          Vikram NS added a comment -
          This issue has been fixed and validated using the 3.7.0-M1 build.
          Show
          Vikram NS added a comment - This issue has been fixed and validated using the 3.7.0-M1 build.

            People

            • Assignee:
              Mat Lowery
              Reporter:
              Man Shing Yau
            • Votes:
              3 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: