User driven security

Post Reply
EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

User driven security

Post by EvgenyT » Tue May 19, 2020 11:01 am

Hi All,

Working with unusual requirements here (well, not a common one at least).
In our model, cost center security is user-driven rather than a group. The business is adamant that we can't logically group cost centers to use vanilla group security. There are about 500 cost-centers and as you can imagine having 500 groups would be a nightmare!

I have got a cost center/user relationship table and my idea is to create a lookup cube to store that data. Then use a cut-down cell-security cube with CellSecurityCubeCreate to only include cost-center (possible version too) that way avoiding a need to run Security Refresh upon any changes. This cube would have a rule along the line of [ 'value' ] = S: ( IF ( 'lookup cube', TM1USER, !Cost_Centre ) <>"", Permission, STET ), basically creating a user driven-security.

Not having to do this in the past, I am looking for feedback from others in particular to:
  • What is the performance overhead of having to evaluate the IF statement each time?
  • What is the performance overhead of the cell security having to do a recalculation every time a user selects a new cost center/ a bunch of cost centers, given that strings are never cached in the memory?
  • Is there any known issue with using TM1USER in rules? I usually avoided this function in the past in rules
  • Any potential locking issue with the cell security cube referencing a lookup cube and turning it into a single reader?
Thanks

ET

tomok
MVP
Posts: 2724
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: User driven security

Post by tomok » Tue May 19, 2020 12:12 pm

EvgenyT wrote:
Tue May 19, 2020 11:01 am
Hi All,

Working with unusual requirements here (well, not a common one at least).
In our model, cost center security is user-driven rather than a group. The business is adamant that we can't logically group cost centers to use vanilla group security. There are about 500 cost-centers and as you can imagine having 500 groups would be a nightmare!

I have got a cost center/user relationship table and my idea is to create a lookup cube to store that data. Then use a cut-down cell-security cube with CellSecurityCubeCreate to only include cost-center (possible version too) that way avoiding a need to run Security Refresh upon any changes. This cube would have a rule along the line of [ 'value' ] = S: ( IF ( 'lookup cube', TM1USER, !Cost_Centre ) <>"", Permission, STET ), basically creating a user driven-security.

Not having to do this in the past, I am looking for feedback from others in particular to:
  • What is the performance overhead of having to evaluate the IF statement each time?
  • What is the performance overhead of the cell security having to do a recalculation every time a user selects a new cost center/ a bunch of cost centers, given that strings are never cached in the memory?
  • Is there any known issue with using TM1USER in rules? I usually avoided this function in the past in rules
  • Any potential locking issue with the cell security cube referencing a lookup cube and turning it into a single reader?
Thanks

ET
500 user groups? That's nothing. I had an installation once where we had over 10,000 user groups. They were all maintained through TI so it wasn't that big of a deal. We have a lot of }CellSecurity cubes, with rules in them, and everything performs acceptably. I've never used TM1User in any of those rules so I can't opine on what that will do to performance. I think the only way you are going to know is to implement in a development environment and do some load testing. Good luck.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

lotsaram
MVP
Posts: 3386
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: User driven security

Post by lotsaram » Tue May 19, 2020 2:41 pm

EvgenyT wrote:
Tue May 19, 2020 11:01 am
In our model, cost center security is user-driven rather than a group. The business is adamant that we can't logically group cost centers to use vanilla group security. There are about 500 cost-centers and as you can imagine having 500 groups would be a nightmare!
Did you mean 500 cost centers or 500 groups?
I wouldn't shy away from personalized groups. I have worked on a few systems with >1000 users where the collection of permissions could be highly configurable and the way to solve it with the lowest total number of groups was to have an individual group created per user, collect the permissions assigned to that user and bundle them into the group.

The processing to completely rebuild overnight can take a few minutes and the SecurityRefresh itself could also take a minute or 2. But no issue with performance from a end user perspective and that's what matters.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: User driven security

Post by EvgenyT » Tue May 19, 2020 10:25 pm

lotsaram wrote:
Tue May 19, 2020 2:41 pm
EvgenyT wrote:
Tue May 19, 2020 11:01 am
In our model, cost center security is user-driven rather than a group. The business is adamant that we can't logically group cost centers to use vanilla group security. There are about 500 cost-centers and as you can imagine having 500 groups would be a nightmare!
Did you mean 500 cost centers or 500 groups?
I wouldn't shy away from personalized groups. I have worked on a few systems with >1000 users where the collection of permissions could be highly configurable and the way to solve it with the lowest total number of groups was to have an individual group created per user, collect the permissions assigned to that user and bundle them into the group.

The processing to completely rebuild overnight can take a few minutes and the SecurityRefresh itself could also take a minute or 2. But no issue with performance from a end user perspective and that's what matters.
500 cost centres which essentially would be 500 groups under simplistic approach.
Ok, thanks for the feedback. I am still eager to try out user driven cell security, will share my results with you guys!
🙂

User avatar
ykud
Posts: 87
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: User driven security

Post by ykud » Wed May 20, 2020 1:33 am

I try to avoid being clever around security :)
I'd create an individual group for each user and use the lookup cube to populate element security for cost centres via a TI (it will be very fast and doesn't require a Security Refresh).
Doing anything with a rule as you describe would require some really good testing to ensure you're not running into this:
Any potential locking issue with the cell security cube referencing a lookup cube and turning it into a single reader?
as it wouldn't be cacheable and has to be fully recalculated each time anybody runs it. 2 users running it should lock from my point of view.

Also using CellSecurity exposes you to the usual "we need more dimensions in it" due to things like disabling input in totals, some of the "oh nobody but HR should these GLs" and the like, which if it was all Element Security would've been less intense. So rolling out the heavy artillery of CubeSecurity just for the single cost centre dimension looks like overkill, 500 groups is nothing extraordinary.

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: User driven security

Post by EvgenyT » Wed May 20, 2020 2:14 am

Thanks Yuriy, great feedback! I really wanted to be clever :roll: :roll: :roll: :roll:
I try to avoid being clever around security
- Can we ask IBM to add this to executive summary of TM1 security ? :lol: :lol: :lol: :lol:

User avatar
ykud
Posts: 87
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: User driven security

Post by ykud » Wed May 20, 2020 3:41 am

EvgenyT wrote:
Wed May 20, 2020 2:14 am
Thanks Yuriy, great feedback! I really wanted to be clever :roll: :roll: :roll: :roll:
No worries, happy to help.
I have a 'don't be clever' print out just behind my monitor so that I don't forget :)

User avatar
paulsimon
MVP
Posts: 749
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: User driven security

Post by paulsimon » Thu May 21, 2020 9:53 pm

Hi

I agree with what the others have said. A complex approach using Cell Security is not the right way to do. Create a Security Group per User.

I have done this in the past. It is relatively easy to create a cascading process so that someone who has access to a higher level in the hierarchy automatically gets access to cost centres below it. Similarly it is relatively easy to create a TI process to set up a new user, that creates the security group for the user and puts them into that group, as well as doing other useful things for the user.

One word of warning. Check your TM1S.CFG. There is typically a Group Creation Limit of 20 per session. You may need to temporarily increase this to allow the initial set of users to be created. However, after this, you should reduce it as TM1 sets aside memory to allow for expansion in the number of groups based on this setting. With a user base of 500, you probably are not going to add more than 20 new users and therefore 20 security groups on any one day, assuming that you do restart your TM1(PA) Service every night, which we do. Even if you only restart once a week, I doubt you would get more than 50 new users in a week.

My current client uses Group based security rather than User based and it is causing problems so I am actually recommending a move to User based security. Some of the problems caused by the Group rather than User based approach to security are:

a) Lack of granularity - in Group based users typically have access to and see more options than they need, so problems of lack of control and problems of seeing too many options that often are not relevant.

b) Having users in multiple groups makes it harder for a user and for that matter a developer to understand what access rights the user actually has to any cell especially when some groups give READ and others WRITE, etc. I had to put a function into my zTD_TM1_Utils utility to help work out why a user cannot access a particular cell. It is quite complex by the time cube, element and cell security is involved.

c) People tend to forget to remove a user from a group based security group when they move, but if security is user based this is less likely to happen since it is all in one place. - Same issues as per (a)

You may still have some group based security groups for some aspects such as general cube access, but when it comes to things like the cost centre dimension, user based security groups do offer some advantages.

Regards

Paul Simon

User avatar
paulsimon
MVP
Posts: 749
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: User driven security

Post by paulsimon » Thu May 21, 2020 10:07 pm

By the way, the way to avoid the need for a SecurityRefresh is to use ElementSecurityPut. I typically do a CellPutS to the relevant }security cube which keeps the display up to date followed by an ElementSecurityPut which actually makes the security take effect. That way you get edit security for an individual user, without the need for a blanket SecurityRefresh.

Regards

Paul Simon

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: User driven security

Post by EvgenyT » Fri May 22, 2020 12:26 pm

Thanks for a very elaborate response Paul.

Re: SecurityRefresh, as far as I know only rules require it (but not rule driven cell security). Updating with TI propagates internal security structures without the need of SecurityRefresh. I even recall someone mentioning ElementSecurity* functions being redundant, similar to AttrPut*.

Regards

User avatar
paulsimon
MVP
Posts: 749
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: User driven security

Post by paulsimon » Fri May 22, 2020 10:39 pm

Hi

Yes a SecurityRefresh is only needed if you have rule driven security in }ElementSecurity cubes or }CubeSecurity. If you use TI there is no need for a SecurityRefresh.

Yes Rules in }CellSecurity do not need a SecurityRefresh. Changes take effect immediately. However, in my experience, }CellSecurity is best used only to give READ access to override WRITE access given by }ElementSecurity. We typically use it once someone has submitted data for their cost centre, to make the cost centre read only based on a workflow cube, so that no further changes can be made while the figures for the cost centre are being reviewed. With the newer ability to build }CellSecurity cubes with fewer dimensions than the main cube, it is now feasible to CellPutS into the }CellSecurity cube instead of using a rule, and this can bring some performance benefits, but it is harder to manage.

Thanks for the bit about ElementSecurityPut. I hadn't realised that IBM had fixed that problem. The security routines that I use have had both in for some time, but then the security processes only actually write to the cells where security rights have been updated so there is very little overhead anyway.

For our purposes, I prefer }ElementSecurity as it can be used to hide elements from users, while }CellSecurity cannot, and that helps users to actually find the cost centres that they need to deal with. There are always a few edge cases where true cross-dimensional security is needed where }CellSecurity is necessary.

Regards

Paul

Post Reply