Change of MDX security in 2.0.9 - Discussion

Post Reply
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Change of MDX security in 2.0.9 - Discussion

Post by Alan Kirk »

Rather than let this issue potentially clutter the fix pack releases thread, I'm shifting discussion over to a separate thread.

PAL 2.0.9 (17 Dec 2019)
Things that can potentially break, kill and mutilate your applications
  • The change to the security evaluation order for MDX, which you can read about on the Quebit site here.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Change of MDX security in 2.0.9 - Discussion

Post by Alan Kirk »

There is a potentially important change in 2.0.9 which Mike Cowie's Quebit has uncovered (and many thanks to Moby91 for pointing me to it), though it doesn't seem to be in the release notes and there doesn't seem to be a full technote on it yet.

In short, it used to be that MDX would execute then check security. Now it checks security then executes. What difference does that make? Suppose that one of your applications uses an MDX expression which drills down from your Total element for that dimension. Your average plebeian users do not have access to the Total element.

Previously it didn't matter because they'd end up seeing only the elements that they have access to.

From 2.0.9, however, the execution will check their security first, determine that they don't have access to the Total element and BOOM, your application is lying in pretty little shards of error messages scattered across the floor of the screen.

In application development, we know this phenomenon by the technical name "A. Bad. Thing."

You can read the whole of Quebit's analysis here.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Change of MDX security in 2.0.9 - Discussion

Post by lotsaram »

Alan Kirk wrote: Thu Jun 18, 2020 9:45 pm There is a potentially important change in 2.0.9 which Mike Cowie's Quebit has uncovered (and many thanks to Moby91 for pointing me to it), though it doesn't seem to be in the release notes and there doesn't seem to be a full technote on it yet.

In short, it used to be that MDX would execute then check security. Now it checks security then executes. What difference does that make? Suppose that one of your applications uses an MDX expression which drills down from your Total element for that dimension. Your average plebeian users do not have access to the Total element.

Previously it didn't matter because they'd end up seeing only the elements that they have access to.

From 2.0.9, however, the execution will check their security first, determine that they don't have access to the Total element and BOOM, your application is lying in pretty little shards of error messages scattered across the floor of the screen.

In application development, we know this phenomenon by the technical name "A. Bad. Thing."

You can read the whole of Quebit's analysis here.
And likewise here
Yes, in this instance you really wonder whether closing a security loophole to make things function "like they always should have from the start" is really worth it, becasue for sure this will break a lot of existing reports.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Change of MDX security in 2.0.9 - Discussion

Post by Steve Rowe »

Yeah, a change this fundamental has to be configurable in the cfg surely? The mind-boggles as to how we got here...
Technical Director
www.infocat.co.uk
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Alan Kirk »

As I've just posted in the Releases thread,
IBM has now released a technote, which can be found here.
The really interesting part of the technote was this bit:
IBM Tech Note wrote:IBM senior management wish to sincerely apologise for implementing this change without consultation or notice, and for breaking a lot of your models. We promise that we have listened to your response, and have ensured that this will never happen again.
Half of the Forum wrote: THEY SAID THAT??? ARE YOU FREAKING KIDDING ME???
Why yes. Yes I am. I am lying to you all with extreme prejudice. This, however, is a real quote:
IBM Tech Note, Really This Time wrote:In the case where a dynamic subset is impacted by this change in behaviour the following options may be considered:

1 - When possible change the MDX in the dynamic subset so that it does not reference members that users do not have READ access on.

2 - Consider the use of static subsets. The list of elements in a static subset are still filtered for non-admin users based on element security.
1 is potentially problematic if your goal is just to light up whatever consolidations and sub-elements the user has, especially if they aren't in the same rollup tree. 2 could work well enough on slowly changing dimensions. For others, if the problem only applies to elements referenced in the MDX I expect that you'd be able to:
- Add a dummy element that sits above your "real" total;
- Weight the real total as 0;
- Have the dummy element readable by your "everyone" group; and
- Use the dummy element instead of the real total in your MDX.

I didn't say it was elegant. I suspect that it would be slightly performance de-enhancing. I certainly haven't tested it. But from what they've written I suspect that it may work as an option too.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis »

Hi all,

Here you can find TI process code I just wrote, to output:
- the MDX statements
- of all public subsets
- for all dimensions/hierarchies who have element security applied
- in a text file in the logging directory

Obviously it's a oneliner with tm1py but this is nice to have too.

Enjoy !

Code: Select all

###########################################################
# Wim Gielis
# June 2020
# https://www.wimgielis.com
###########################################################
#
# As of PAL 2.0.9, MDX queries and element security rules have changed
# Therefore, it can be useful to have an overview of all MDX statements in public subsets
#
###########################################################


# Where do we output text files ?
cDestination_Folder = GetProcessErrorFileDirectory | 'TM1 output\MDX of public objects\';
If( FileExists( cDestination_Folder ) = 0 );
   ExecuteCommand( Expand( 'cmd /c "md "%cDestination_Folder%""' ), 1 );
EndIf;

cOutputFile = cDestination_Folder | 'MDX.txt';
AsciiDelete( cOutputFile );

DataSourceAsciiQuoteCharacter = '';

# loop over dimensions and retrieve their public subsets
dLoop = 1;
While( dLoop <= Dimsiz( '}Dimensions' ));
   vDimension = Dimnm( '}Dimensions', dLoop );
   vScan = Scan( ':', vDimension );
   If( vScan = 0 );

      If( CubeExists( '}ElementSecurity_' | vDimension ) > 0 );

         vDim_Subsets = '}Subsets_' | vDimension;
         x = 1;
         While( x <= Dimsiz( vDim_Subsets ));
		 
            vSubsetName = Dimnm( vDim_Subsets, x );
            vScan = Scan( ':', vSubsetName );
            If( vScan = 0 );
               vHier = vDimension;
            Else;
               vHier = Subst( vSubsetName, 1, vScan - 1 );
               vSubsetName = Delet( vSubsetName, 1, vScan );
            EndIf;
		 
            s = Trim( HierarchySubsetMDXGet( vDimension, vHier, vSubsetName ));
            If( Long( s ) > 0 );
               TextOutput( cOutputFile, 'Dimension', vDimension, 'Hierarchy', vHier, 'Subset', vSubsetName, 'MDX', s );
            EndIf;
		 
            x = x + 1;
         End;

      EndIf;

   EndIf;

   dLoop = dLoop + 1;
End;
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
scrumthing
Posts: 81
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: Change of MDX security in 2.0.9 - Discussion

Post by scrumthing »

Wim Gielis wrote: Sat Jun 20, 2020 8:11 am Obviously it's a oneliner with tm1py but this is nice to have too.
I am pretty sure I would need at least two lines of code in tm1py but there you have the potential to write the script once and run it against every tm1 instance without having to create the process everywhere. :-)

Thanks for the to Wim! I haven’t thought of that solution before but will shamelessly use it. It is a pretty elegant way. Obviously not without letting the header intact. honor to whom honor is due!
There is no OLAP database besides TM1!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis »

scrumthing wrote: Sat Jun 20, 2020 9:36 pmThanks for the to Wim! I haven’t thought of that solution before but will shamelessly use it. It is a pretty elegant way. Obviously not without letting the header intact. honor to whom honor is due!
Thank you for the feedback and happy to see that the code will be used 👍
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by gtonkin »

This is indeed going to be fun, especially with some of those MDX statements located in Excel templates and reports!
Some lateral thinking will be needed too no doubt.

Thanks Mike and everyone else for the review and feedback on this.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis »

gtonkin wrote: Sun Jun 21, 2020 2:13 pm This is indeed going to be fun, especially with some of those MDX statements located in Excel templates and reports!.
Maybe we should find back VBA code to loop over all Excel files in the }Externals folder, open the file, loop through the worksheets and then look for active form formulas with an MDX argument 😮
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by gtonkin »

I like your thinking Wim - just trying to find the best way to report the findings.
Write back to the calling workbook or something else.
User avatar
scrumthing
Posts: 81
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: Change of MDX security in 2.0.9 - Discussion

Post by scrumthing »

As far as I understand it IBM reverted the change.
Update (June 30th, 2020):

The IBM Planning Analytics team will revert the change described in this Technote in an Interim Fix for Planning Analytics 2.0.9.1. This Technote will be updated as additional details about the Interim Fix are available. The current 2.0.9.1 release that is available on IBM Passport Advantage and IBM Fix Central will be updated when the Interim Fix is available.
https://www.ibm.com/support/pages/node/6226890
There is no OLAP database besides TM1!
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by gtonkin »

That will save a lot of everyone's time - thanks for posting Scrumthing
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Change of MDX security in 2.0.9 - Discussion

Post by Mark RMBC »

Yeah, thanks from me too :D
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis »

Good catch, thanks.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
moby91
MVP
Posts: 227
Joined: Fri Mar 11, 2011 2:18 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003 2007

Re: Change of MDX security in 2.0.9 - Discussion

Post by moby91 »

scrumthing wrote: Thu Jul 02, 2020 9:32 am As far as I understand it IBM reverted the change.
Update (June 30th, 2020):

The IBM Planning Analytics team will revert the change described in this Technote in an Interim Fix for Planning Analytics 2.0.9.1. This Technote will be updated as additional details about the Interim Fix are available. The current 2.0.9.1 release that is available on IBM Passport Advantage and IBM Fix Central will be updated when the Interim Fix is available.
https://www.ibm.com/support/pages/node/6226890

The IBM technote 6226890 has been updated:
Update (July 17th, 2020):

The behaviour change described in this Technote has been reverted in the Planning Analytics 2.0.9.2 release. In the 2.0.9.2 release security will no longer be evaluated when processing an MDX statement. The results of the MDX statement will only be filtered based on member security. This is consistent with the behaviour in 2.0.8 and lower versions. Planning Analytics customers are encouraged to update to the 2.0.9.2 release.

A future release of Planning Analytics may include a feature that allows for optional evaluation of security when processing MDX statements. This planned feature would allow the TM1 database owner to determine if security should be evaluated during MDX processing. The default behaviour from 2.0.8 and 2.0.9.2 will be maintained in any future release of Planning Analytics 2.0.9.
moby91
MVP
Posts: 227
Joined: Fri Mar 11, 2011 2:18 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003 2007

Re: Change of MDX security in 2.0.9 - Discussion

Post by moby91 »

Planning Analytics 2.0.9.2 has been released:

https://www.ibm.com/support/pages/node/6253337
IBM Planning Analytics Local 2.0.9.2 is now available for download on Fix Central

https://www.ibm.com/support/pages/node/6253335
IBM Planning Analytics v2.0.9.2 is now available for cloud deployments
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Change of MDX security in 2.0.9 - Discussion

Post by MarenC »

Hi Moby,

cant seem to see anything in those links about the changes to the mdx!

Maren
moby91
MVP
Posts: 227
Joined: Fri Mar 11, 2011 2:18 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003 2007

Re: Change of MDX security in 2.0.9 - Discussion

Post by moby91 »

MarenC wrote: Tue Jul 28, 2020 9:32 am cant seem to see anything in those links about the changes to the mdx!

See the IBM technote:
https://www.ibm.com/support/pages/node/6226890
Security Changes on MDX and Dynamic Subsets in Planning Analytics 2.0.9

See Quebit's analysis:
https://quebit.com/askquebit/IBM/planni ... -in-2-0-9/
Planning Analytics Changes to MDX Subset Evaluation in 2.0.9
Post Reply