[Mondrian] MemberGrantTable feature Proposal

Nick Goodman ngoodman at bayontechnologies.com
Fri Sep 26 12:52:47 EDT 2008


My company is looking at contributing this.  Comments on the spec are  
appreciated - I wonder if it goes far enough?  Perhaps we should also  
look at making a HierarchyGrantTable too?

Addition of a tag <MemberGrantTable>, that can be used as a child of  
<HierarchyGrant> instead of using multiple <MemberGrants> nodes.   
Schema/Cube/HierarchyGrants are all general purpose (ie, they are  
"vertical security" and describe general things that people should or  
should not have access to).  MemberGrants are data specific and are  
sometimes lengthy, verbose, and need to match the Member names.  I've  
seen two customers with more than 500+ MemberGrants in their XML.   
Managing "data" (ie, the Divisions that a Role should be able to see)  
seems to be more appropriately managed outside the schema.  I don't  
suggest replacing the MemberGrant - it should stay as is.

There should be either
- one and only one MemberGrantTable as a child of HierarchyGrant
- multiple MemberGrants

<MemberGrantTable> has exactly one child (a <Table> or a <View>)

<MemberGrantTable> has the following attributes:
Required
   roleColumn - The name of the table column that has the "Role" name  
in it.  This should match the name= attribute of the <Role>
   accessColumn - The name of the table column that has the access  
type in it.  The column should contain the string "all" or "none"
   levelColumn - The name of the table column that has level this  
member grant applies to.  Should match the name in the <Level> node
   memberColumn - The name of the table column that has the member  
that the grant applies to.  This should match the values contained in  
the column specified in column= of a <Level>
Optional
   ordinalColumn - The column that should be used to order the Member  
grants since grant order matters.
   hierarchyColumn - In some circumstances, the same "level" name may  
be present in multiple Hiearchies. If the level name is ambiguous,  
this column will disambiguate.

Take the following example:

Table: ROLE_GRANTS
Columns: therole, accesstype, thelevel, themember, theorder

California manager, all, Store State, CA, 100
California manager, none, Store City, Los Angeles,101
California manager, none, City, Los Angeles, 103
California manager, all, Store State, CA, 102

Then the following XML fragments are equivalent in terms of their  
granting/denying access to Members

<Role name="California manager">
   <SchemaGrant access="none">
     <CubeGrant cube="Sales" access="all">
       <HierarchyGrant hierarchy="[Store]" access="custom"
           topLevel="[Store].[Store Country]">
         <MemberGrantTable accessColumn="accesstype"  
levelColumn="thelevel" memberColumn="themember"  
ordinalColumn="theorder">
		<Table name="ROLE_GRANTS" schema="security"/>
         <MemberGrantTable>
       </HierarchyGrant>
       <HierarchyGrant hierarchy="[Customers]" access="custom"
           topLevel="[Customers].[State Province]"  
bottomLevel="[Customers].[City]">
         <MemberGrantTable accessColumn="accesstypeblah"  
levelColumn="thelevel" memberColumn="themember"  
ordinalColumn="theorder" >
		<View alias="INLINE">
                 <SQL dialect="generic">
select therole, accesstype as "accesstypeblah", thelevel, themember,  
theorder from ROLE_GRANTS
                 </SQL>
                 </View>
	</MemberGrantTable>
        </HierarchyGrant>
       <HierarchyGrant hierarchy="[Gender]" access="none"/>
     </CubeGrant>
   </SchemaGrant>
</Role>


<Role name="California manager">
   <SchemaGrant access="none">
     <CubeGrant cube="Sales" access="all">
       <HierarchyGrant hierarchy="[Store]" access="custom"
           topLevel="[Store].[Store Country]">
         <MemberGrant member="[Store].[USA].[CA]" access="all"/>
         <MemberGrant member="[Store].[USA].[CA].[Los Angeles]"  
access="none"/>
       </HierarchyGrant>
       <HierarchyGrant hierarchy="[Customers]" access="custom"
           topLevel="[Customers].[State Province]"  
bottomLevel="[Customers].[City]">
         <MemberGrant member="[Customers].[USA].[CA]" access="all"/>
         <MemberGrant member="[Customers].[USA].[CA].[Los Angeles]"  
access="none"/>
       </HierarchyGrant>
       <HierarchyGrant hierarchy="[Gender]" access="none"/>
     </CubeGrant>
   </SchemaGrant>
</Role>








More information about the Mondrian mailing list