Checking Element Level Security in a TI processes

Post Reply
MGrain
Posts: 8
Joined: Wed Nov 15, 2017 11:36 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Checking Element Level Security in a TI processes

Post by MGrain » Wed Jun 06, 2018 2:40 pm

This is hopefully one for the useful section.

John Turner and I came up with a fairly slick solution to a problem this morning and I thought it was worth sharing.

As you may or may not know, all TI processes, regardless of who actually executes them, run with DataAdmin rights. I confess that I didn't know that, but in my defence I very rarely, if ever, give users the rights to run TI processes.

We had a problem with this because we needed to give the users the ability to run an archiving process but only for those Entities they actually had write access to.

The Entity is passed to the process as a parameter, sure we can limit the options through an Action Button but that wouldn't stop people using Perspectives from typing in anything they liked. Essentially we needed a way to test the element level security within the TI process.

Disclaimer: The only security we needed to worry about is element level security and that's very straightforward, we haven't got the "groups as members of other groups" stuff going on. No Cell Security here either.

So given an Entity we need to find out if the user has WRITE access to it. We tossed around all sorts of options:
  • 1. Nested while loops checking for membership of a group and WRITE access for the group for the given entity.
  • 2. Building a reference cube with }Client and Entity as the dimensions and populating it with rules.
  • 3. Building a reference cube with }Client and Entity as the dimensions and populating it with TI.
The first option works, but it's not very fast, the second option just doesn't work at all. You can't write rules to populate the cube, firstly they are all strings and so don't consolidate and secondly there are two cubes to work through. Try it if you don't believe me, I'd love to be proved wrong. The last option arose when the problem with the rules dawned on us, but this is a clunky work around at best, the process would need to be scheduled to run regularly or manually kicked off every time security was updated.

The cunning solution we came up with was to use two MDX subsets on the }Groups dimension.

Firstly establish which Groups the user is a member of:

Code: Select all

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [}Groups] )}, 0)},
[}ClientGroups].(StrToMember("[}Clients].["+USERNAME+"]"))<>"")}
Secondly establish which Groups have WRITE access to the Entity in question:

Code: Select all

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [}Groups] )}, 0)},
[}ElementSecurity_Entity].([Entity].['| pEntity |'])="WRITE")}
Now if there are any groups common to both sets, we know the user has write access to the Entity. We can check for that by using INTERSECT (returns only those members common to both sets) an empty subset means no write access.

However, generating an empty subset via MDX causes TI to error, so John had the bright idea of adding in the Admin group. This group is always present and never has any element level security so won't interfere with the results. Now we just need to test for a subset with a size of 1 to know the user has no write access to the given Entity.

Code: Select all

UNION({[}Groups].[Admin]},
{INTERSECT(
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [}Groups] )}, 0)},
[}ClientGroups].(StrToMember("[}Clients].["+USERNAME+"]"))<>"")}
,
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [}Groups] )}, 0)},
[}ElementSecurity_Entity].([Entity].[' | pEntity | '])="WRITE")}
)})
It seems to work very nicely and it's very fast, definitely much faster than the nested loops approach.

John took it a stage further and split it into two separate processes, the checking process and the archiving process. Users only have access to the checking process - if security check is passed it calls the archiving process (remember it's running as DataAdmin). In the meantime administrators can use the archiving process without going through the unnecessary security check.

Hope someone finds it useful.

tm123
Posts: 110
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Checking Element Level Security in a TI processes

Post by tm123 » Wed Jun 06, 2018 5:07 pm

This is what I have been doing also, but you can avoid the Union with ADMIN Group.

You can create empty MDX-based subsets through TI by using this syntax:

SUBSETCREATEBYMDX ( subName , sMDX , dimName ) ;

So when you specify the third parameter, TM1 will not throw an error when MDX does not return elements

Wim Gielis
MVP
Posts: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Checking Element Level Security in a TI processes

Post by Wim Gielis » Wed Jun 06, 2018 6:42 pm

MGrain wrote:
Wed Jun 06, 2018 2:40 pm
  • 1. Nested while loops checking for membership of a group and WRITE access for the group for the given entity.
Hi,

Thanks for posting.
But I can't think the option I quoted takes a long time.
A loop over the }Groups dimension elements and doing a CellGetS to the }ClientGroups cube,
with a CellGetS to the }ElementSecurity cube can't take long, can it ? The entity is a parameter so you check only 1 entity.
I've done this countless times before.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
paulsimon
MVP
Posts: 578
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Checking Element Level Security in a TI processes

Post by paulsimon » Wed Jun 06, 2018 8:32 pm

Hi

I have used some other approaches to do this.

a) Have a Security Group per user. Then you don't get the issue since you can just add eg '-Grp' to the user name to get the name of their group and then check from the }ElementSecurity cube whether they have access to the Entity, Cost Centre or whatever the dimension is. In most companies a user will only look after eg one cost centre so this works. There are various methods of cascading security rights down that I also use for users who have access to eg everything below a section of the cost centre hierarchy. I generally have a few dedicated groups for eg Management Accounts who can write to all Entities.

b) Run a process using loops to populate a cube that has Client and Entity, which I think you mentioned. However, although the looping can take a while, this only needs to be done overnight. Then when running a process, all it needs to do is to check the User-Entity cube to see if the user has WRITE access to the Entity. I just store a 1 for every combination where the user has at least WRITE access. Therefore you are not running loops every time a process is run. You just populate the cube overnight and then check the cube in the prolog of each process to make sure the user has WRITE access. The only downside being that when a new user is added, you need to run this process before they can do anything. In practice in the prolog of each process, I execute a Check Write Access process to do the check and pass in the user and entity. This process can then check for any other issues eg attempt to write to a consolidated Entity, or a global flag to prevent updates if the Entity is temporarily disabled. There isn't much overhead in running a small process.

Which approach is best depends on lots of factors such as the ratio between the number of groups and the number of users, whether you need to just control write access or also read access, etc.

You should also limit access to processes so users only have READ access to the top level processes, and NONE access to any sub processes that they should not be able to call directly. The chances are that they will be relatively few top level processes that need to call the Check Write Access process.

When we deployed Perspectives to our users we did this via Citrix and set the TM1P.INI file to hide Processes, Chores, and Replications, so that they can only see Applications, Cubes and Dimensions. A user with a good knowledge of TM1 could probably circumvent this, but it does help to minimise the risk of a user running a process other than by the Action Buttons that you have set up on the Sheets in the Application Folders.

Regards

Paul Simon

MGrain
Posts: 8
Joined: Wed Nov 15, 2017 11:36 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: Checking Element Level Security in a TI processes

Post by MGrain » Thu Jun 07, 2018 7:51 am

Thanks for the replies. As Paul rightly points out the right approach depends on a lot of factors, it's one of the great strengths of TM1 that we can adopt so many different solutions to the problem.

The reason I shy away from the security lookup cube is because it is adding another step of maintenance and another link in the security chain. I'm not always around to look after a system after it has gone live and no matter how well you document it (nobody reads it) or hand over to the client, the potential is there to trip them up.

I also have a system with over 700 groups, there are 750 users so it's at the tipping point where it would make sense to switch to user driven groups - but that's a whole different approach and it will be a very interesting exercise to make the conversion. Nonetheless, looping through that many groups isn't necessarily slow, but the MDX is definitely faster.

For me, in this case, the MDX solution is the best one. Ultimately, I am a TM1 nerd, I take satisfaction in solving a problem with 8 lines of MDX rather than using while loops or building supporting look up cubes. :geek:

Cheers
Mike

User avatar
macsir
Community Contributor
Posts: 512
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Checking Element Level Security in a TI processes

Post by macsir » Fri Jun 08, 2018 12:21 am

Thanks for sharing. I reckon MDX is handy and helpful.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
Steve Rowe
Site Admin
Posts: 1765
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Checking Element Level Security in a TI processes

Post by Steve Rowe » Fri Jun 08, 2018 11:19 am

You could take this a step further.

1. Set up a subset that uses the MDX.
2. Create a "Front end cube", dimension by }Clients and a dimension with the various parameters.
3. Give the parameter list a pick list attribute and assign the MDX subsets.
4. Remove the parameter from the TI and read it from the front end cube.
5. User selects from a pre-verified list of options, i.e. no manual entry into parameters. Big win.
6. You can also see the last set of parameters that a user ran the job with, also handy.

This of course assumes that the user will run the TI from the front end you provide and not directly from Persp.

Post Reply