The main idea is to have multiple roles on a user and to combined those role to get a good security model inside Pentaho Mondrian

The cube model

Let's take this very simple cube as example :

• Dimension Geography with contient, country, region and city
• Dimension Time with year, and month
• Measures Actual and Budget

So the cube is :

 <Schema name="test_security">
  <Cube name="security" description="Test security" cache="true" enabled="true">
    <Table name="fact_security"/>
      <Dimension foreignKey="geography_id" highCardinality="false" name="Geography" type="StandardDimension">
      <Hierarchy name="Geography" hasAll="true" primaryKey="geography_id" caption="Geography Hierarchy">
        <Table name="dim_geography"/>
        <Level name="continent" column="continent_name" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
        <Level name="country" column="country_name" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
        <Level name="region" column="region_name" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
        <Level name="city" column="city_id" nameColumn="city_name" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" />
      </Hierarchy>
    </Dimension>
    <Dimension type="TimeDimension" visible="true" foreignKey="month_wid" highCardinality="false" name="Time">
      <Hierarchy name="Time" visible="true" hasAll="true" defaultMember="2011">
        <Table name="dim_date_month" />
        <Level name="Year" visible="true" column="year" type="String" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Month" visible="true" column="date_month_id" nameColumn="month_desc" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
      </Hierarchy>
    </Dimension>
    <Measure name="Actual" column="Actual" datatype="Numeric" formatString="#.###" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Budget" column="Budget" datatype="Numeric" formatString="#.###" aggregator="sum" visible="true">
    </Measure>
  </Cube>
</Schema>

The security model

The roles are :

• Authenticated: for everyone authenticated on Mondrian application server
• View_security_cube: for people who can see this cube
• Switzerland: can only see Switzerland
• All_countries: can see everything