TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Change of MDX security in 2.0.9 - Discussion

https://www.tm1forum.com/viewtopic.php?t=15371

Page 1 of 1

Change of MDX security in 2.0.9 - Discussion

Posted: Sun Mar 19, 2017 6:48 pm
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.

Change of MDX security in 2.0.9 - Discussion

Posted: Thu Jun 18, 2020 9:45 pm
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.

Change of MDX security in 2.0.9 - Discussion

Posted: Thu Jun 18, 2020 11:08 pm
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.

Change of MDX security in 2.0.9 - Discussion

Posted: Fri Jun 19, 2020 8:14 am
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...

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Sat Jun 20, 2020 3:36 am
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.

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Sat Jun 20, 2020 8:11 am
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;

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Sat Jun 20, 2020 9:36 pm
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!

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Sun Jun 21, 2020 7:04 am
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 👍

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Sun Jun 21, 2020 2:13 pm
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.

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Sun Jun 21, 2020 4:11 pm
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 😮

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Sun Jun 21, 2020 4:20 pm
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.

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Thu Jul 02, 2020 9:32 am
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

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Thu Jul 02, 2020 10:09 am
by gtonkin
That will save a lot of everyone's time - thanks for posting Scrumthing

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Thu Jul 02, 2020 10:54 am
by Mark RMBC
Yeah, thanks from me too :D

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Thu Jul 02, 2020 11:00 am
by Wim Gielis
Good catch, thanks.

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Fri Jul 17, 2020 7:37 pm
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.

Re: Change of MDX security in 2.0.9 - Discussion

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

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Tue Jul 28, 2020 9:32 am
by MarenC
Hi Moby,

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

Maren

Re: Change of MDX security in 2.0.9 - Discussion

Posted: Wed Jul 29, 2020 3:05 pm
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
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited