Change of MDX security in 2.0.9 - Discussion

Post Reply
User avatar
Alan Kirk
Site Admin
Posts: 6163
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Change of MDX security in 2.0.9 - Discussion

Post by Alan Kirk » Sun Mar 19, 2017 6:48 pm

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: 6163
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Change of MDX security in 2.0.9 - Discussion

Post by Alan Kirk » 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.
"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: 3417
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 » Thu Jun 18, 2020 11:08 pm

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: 2062
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Change of MDX security in 2.0.9 - Discussion

Post by Steve Rowe » Fri Jun 19, 2020 8:14 am

Yeah, a change this fundamental has to be configurable in the cfg surely? The mind-boggles as to how we got here...

User avatar
Alan Kirk
Site Admin
Posts: 6163
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Alan Kirk » Sat Jun 20, 2020 3:36 am

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: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis » Sat Jun 20, 2020 8:11 am

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

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

scrumthing
Posts: 43
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: Change of MDX security in 2.0.9 - Discussion

Post by scrumthing » Sat Jun 20, 2020 9:36 pm

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: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis » Sun Jun 21, 2020 7:04 am

scrumthing wrote:
Sat Jun 20, 2020 9:36 pm
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!
Thank you for the feedback and happy to see that the code will be used 👍
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

User avatar
gtonkin
MVP
Posts: 807
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by gtonkin » 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!
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: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis » Sun Jun 21, 2020 4:11 pm

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

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

User avatar
gtonkin
MVP
Posts: 807
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by gtonkin » Sun Jun 21, 2020 4:20 pm

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.

scrumthing
Posts: 43
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: Change of MDX security in 2.0.9 - Discussion

Post by scrumthing » 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
There is no OLAP database besides TM1!

User avatar
gtonkin
MVP
Posts: 807
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by gtonkin » Thu Jul 02, 2020 10:09 am

That will save a lot of everyone's time - thanks for posting Scrumthing

Mark RMBC
Community Contributor
Posts: 232
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 » Thu Jul 02, 2020 10:54 am

Yeah, thanks from me too :D

Wim Gielis
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Change of MDX security in 2.0.9 - Discussion

Post by Wim Gielis » Thu Jul 02, 2020 11:00 am

Good catch, thanks.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

moby91
MVP
Posts: 222
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 » Fri Jul 17, 2020 7:37 pm

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: 222
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 » Tue Jul 28, 2020 1:02 am

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
Posts: 112
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 » Tue Jul 28, 2020 9:32 am

Hi Moby,

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

Maren

moby91
MVP
Posts: 222
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 » Wed Jul 29, 2020 3:05 pm

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