Securtity Rights not working in Excel tables

Post Reply
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Securtity Rights not working in Excel tables

Post by harrytm1 »

Hi all,

Here is the scenario. I have created a Company dimension based on the group structure. I also created several User Groups based on geographical regions e.g. UK, HK.

I then created some charts and tables in Excel. User can select the elements in the dimension drop-down lists to toggle the figures and charts accordingly. The drop-down lists are created using Excel Form > Combo Box which refers to a range of cells in another worksheet. In the case of Company dimension, the range contains all companies in the group. The linked cell then does a VLOOKUP based on the selection and the returned value is used in the DBRW.

I created a test ID which was assigned to the User Group "UK". At the Security Assignment for Company dimension, UK user group are configured to WRITE access ONLY for companies based in UK. All other companies are set to NONE.

I then login using this UK test ID and open the Excel file with charts and tables. I deliberately selected a non-UK company in the drop-down list and TM1 actually returned the values! I'm expecting TM1 to block this read attempt as it should check the security rights of the selected element in the Company dimension.

Did I miss out some other settings? Please advise. Thanks!
Planning Analytics latest version, including Cloud
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Securtity Rights not working in Excel tables

Post by lotsaram »

I assume you are talking about element security in the company dimension?

1/ How is the region which populates the dropdown lists filled? You would need to fill it with SUBNMs otherwise the TM1 security isn't going to get picked up or have any effect.

2/ Assuming you have done this have you done a full recalculate? If the region to fill the dropdowns is on another sheet then you would need to do a full recalc not just a sheet recalc in order to see the change in security after loging in as another user.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Securtity Rights not working in Excel tables

Post by harrytm1 »

Thanks for the reply.
1) The company dimension list is "hardcoded" with the full list of elements instead of SUBNM. So I guess this is the flaw of TM1 then. Just wondering why TM1 does not check against the element security of the user when the DBRW is reading the data points? It is not very sightly if I use SUBNM as the drop-down list will be filled with blanks for a user who is restricted to a small list of elements.

any other ways to do this besides SUBNM?

2) This question gives rise to anothe problem that i faced. I always switch off Auto Calculate in Excel. But for this Excel workbook with charts and tables, I wish to turn Auto Calculate on so that, with every change in the dimensions, the values will be updated. however, Excel seems to "forget" the switch to Auto Calculate despite me saving it. Everytime i open this file, it always reverts back to Manual. So is this setting tied to client or to a file?

lotsaram wrote:I assume you are talking about element security in the company dimension?

1/ How is the region which populates the dropdown lists filled? You would need to fill it with SUBNMs otherwise the TM1 security isn't going to get picked up or have any effect.

2/ Assuming you have done this have you done a full recalculate? If the region to fill the dropdowns is on another sheet then you would need to do a full recalc not just a sheet recalc in order to see the change in security after loging in as another user.
Planning Analytics latest version, including Cloud
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Securtity Rights not working in Excel tables

Post by lotsaram »

SUBNM is definitely the way to go to fill the list. The list will not be interspersed with blanks for each user as the SUBNM will read only what they are able to see (in effect the index is different for different user groups depending on what security rights they have to the element security.) I am pretty sure this will deliver the functionality you are looking for.

As for calculation, auto vs. manual will depend on the calc state of the workbook when it was last saved and the state of the Excel application when the workbook in question is opened (that is has if the state is different from the saved state with other open workbooks then this will take precedence.) If VBA is an option I would suggest that rather than having auto calculate on you will get much better performance with manual calculation on and calling the TM1RECALC (F9) or TM1RECALC1 (Shift+F9) macros on specific events, such as a dropdown selection change, sheet activation, etc.
Last edited by lotsaram on Mon Apr 27, 2009 12:02 am, edited 1 time in total.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Securtity Rights not working in Excel tables

Post by harrytm1 »

hi lotsaram,

I have created a combo box that refers to a list of SUBNMs in another sheet. Each SUBNM is in turn referred to Row A which contains a running sequence of numbers. This is to be used as index for the elements in the dimension. The SUBNM looks like this:
=SUBNM("server:Company", "", $A3, "Description")

Thus, $A3 refers to the row that contains the running numbers that will be used as index. I'm not using any subset, hence " ".

To my horror, when I tested an ID that should only have access to 3 companies out of 50, each SUBNM actually returns the corresponding company successfully! This ID in the end is able to access all 50 companies' data. The weird thing is, despite me stating in SUBNM to display "Description" alias, those companies that are NOT supposed to appear are displayed in their codes, while those 3 companies are displayed in their aliases "Description".

I then did another test, this time defining a subset "Default" in every SUBNM. And it works! Only three companies appear in the list.

I think this bug can compromise element security setup. Anyone encounter this before?

9.4MR1 and Excel 2003.
Planning Analytics latest version, including Cloud
lotsaram
MVP
Posts: 3648
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Securtity Rights not working in Excel tables

Post by lotsaram »

If you want to refer to the whole dimension then what you want is subset ALL. Rather than a blank argument for subset your SUBNM formula should be:
=SUBNM("server:Company", "ALL", $A3, "Description")
Subset ALL is implicit and exists for every dimension. I would not use the default subset as this could easily lead to unexpected (and difficult to trace) errors if users define their own default subsets as a private default overrides the public default. It would be better to use a named public (non default) subset for this purpose.

Depending on the structure/order of the dimension you could also use DIMNM, your formula to fill the list would then look like this if you wanted to return the Description alias:
=DBRA("server:Company", DIMNM("server:Company", $A3), "Description")

However, the problem with DIMNM and subset ALL is that they will return the full dimension list and not just what the user has read/write access to. If you experience the SUBNM returning elements to which the user should have no access for a named public subset then this is definitely a security bug. If you see this when leaving the subset blank (or subset ALL) or for a DIMNM then this is normal (but undesirable) behaviour. For the former you can and should report this as a bug, for the later you could submit an enhancement request - you wouldn't be the first to want this.
Last edited by lotsaram on Mon Apr 27, 2009 12:08 am, edited 1 time in total.
User avatar
Steve Rowe
Site Admin
Posts: 2407
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

Re: Securtity Rights not working in Excel tables

Post by Steve Rowe »

Hi Harry,

Sounds like a possible bug to me too, though I would be surprised if security was this broken in 9.4 MR1 that it had not been raised on the forum yet as it has been out quite a while now.

If you are just on the server explorer logged in with your test user is the security still broken or do you see only the companies and data you are supposed to?
In your excel environment if go straight in as your test user is the security still broken? Just wondering if the bug some how relates to the TM1 client remembering the security of the admin user if you first log in as admin and then later switch to the test user.
What other security is in the system? Is there any cell level security on the cube?

Something to check as well, which is going to sound like I'm insulting your intelligence but we have all done it! :D Is the entity dimension that you have set the security on the same one that is used in the cube?

Like I said if the security was this broken it would be odd if no one had noticed it yet so you may be doing something unusual to generate the behaviour you are seeing. That said as most people are developers on the forum security tends to be one of the least interesting things to us and the last thing that gets checked so who knows!

I'd suggest raising direct with IBM too if you think you have found a bug, the sooner they know about it the sooner they can fix it.

Cheers,
Technical Director
www.infocat.co.uk
nhavis
Posts: 62
Joined: Mon Jan 05, 2009 12:47 am

Re: Securtity Rights not working in Excel tables

Post by nhavis »

From the TM1 Developers Guide:

TM1 Developers Guide > 6 Controlling Access to TM1 Objects > Assigning Security Rights to Groups
Assigning Security Rights to Groups
Write
Element Members of the group can read and update the cells identified by the element and edit attributes of the element.

None
Element Members of the group cannot see the element in the Subset Editor or Dimension Editor, and cannot see the cells identified by the element when browsing a cube.

-----------------------------------------------------------------
In other words - users that can write can also read.
Additionally - users of groups with security right 'None' will be able to see the elements in excel using DIMNM, SUBNM etc. (or using the API, or any other method). However will not be able to using the subset or dimension editor (this explains why users could not see the elements when you specified a subset).

Edit: I'll also note that since TM1 is behaving as specified - this is not a bug (that isn't to say that security/functionality should or should not be a particular way).
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Securtity Rights not working in Excel tables

Post by Steve Vincent »

well i'll be...i learnt something today!

Its the same in 9.1. subnm ignores the security access the user has and will always provide a full list, but won't be able to see it in the subset editor or the cube. The only way your file would have allowed users to "see" the data that they didn't have access to was if the sheet was previously calculated by someone that did have access, and on the change of selection it wasn't recalculated. It would then return the stored values Excel had from last time, much the same way it keeps values from linked files.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Securtity Rights not working in Excel tables

Post by harrytm1 »

In reply to nhavis and Sam Vincent's posts, my personal experience is that if I'm using index in a SUBNM AND defining a subset, a user who has security access to, say 3 out of 10 elements, will only see those 3 elements. However, if i omit the subset by using "" in SUBNM, it will then show the 3 elements correctly with their aliases (assuming I have set the alias to be displayed). For the other 7 that the user is not supposed to see, the code name will be displayed instead. So I think this is what nhavis is referring to. The key is to define the subset.
Planning Analytics latest version, including Cloud
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: Securtity Rights not working in Excel tables

Post by Alan Kirk »

I haven't had a chance to follow this thread closely (not even the posts from my esteemed colleage "Sam" Vincent :lol: ), but SubNm / DimNm have always returned all elements whether the user has access to them or not. This isn't a bug; were it otherwise the return value of a SubNm formula which used the Index number argument would be a moveable feast depending on the security access of the individual recalculating it.

The security restricts access to the DATA that the elements store, rather than to the elements themselves. (Subset Editor being the noteable exception, but that's just a GUI design issue.) This includes the attribute (including alias) data as has been noted earlier in this thread.

Although this isn't germane to the original question, I find that characteristic useful; on some of my dimensions I have an attribute called ReadAccess which is set to Y for all elements. When I need to populate a combo box in an Excel application, I use SubNm or DimNm to loop through all of the elements (knowing that that will return every element), and populate the combo box only with those elements where the attribute returns Y. In that way the combo box is only populated with the elements that the user can see.

(Why do I use a combo box rather than E_Pick? Because for some applications the user needs to specify multiple elements. Of course I did put in a request for a multi-select version of E_Pick to Applix one day after I got back from hunting dinosaurs with "Sam"... don't think it'll ever see the light of day, though. )
"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.
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Securtity Rights not working in Excel tables

Post by Steve Vincent »

harrytm1 wrote:In reply to nhavis and Sam Vincent's posts, my personal experience is that if I'm using index in a SUBNM AND defining a subset, a user who has security access to, say 3 out of 10 elements, will only see those 3 elements. However, if i omit the subset by using "" in SUBNM, it will then show the 3 elements correctly with their aliases (assuming I have set the alias to be displayed). For the other 7 that the user is not supposed to see, the code name will be displayed instead. So I think this is what nhavis is referring to. The key is to define the subset.
Hi Harry, this is NOT the case, certainly not in 9.0 or 9.1. I defined the subnm using my subset names (tried several too, including blank) and all of them returned the full element list, not just those they were allowed to see. As Alan pointed out, it would be a nightmare to control lists in Excel if it didn't do it this way.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Securtity Rights not working in Excel tables

Post by harrytm1 »

To Steve, sorry for calling you Sam. :P

To all, thanks for your contribution to this post. Appreciate it!

I used a dynamic subset in the SUBNM together with index, and it works the way I want it i.e. only shows the elements that the user is supposed to see in the Excel cells. The range of cells is then used as list in the combo box.

Actually, the main question that I hope to find some answers concerns Security access. The scenario is this:
User has security access WRITE to Company dimension for element "ABC". For the remaining 9 elements, it is NONE.

He goes to the Profit & Loss cube and slices a cube view for ABC to Excel. So now in SUBNM for Company dimension, it is pointing to "ABC". The user then types "XYZ" into that Excel cell, thus overwriting the SUBNM. XYZ is a Company element that he has NO access to.

He recalculates the worksheet and the data for XYZ is returned successfully, which he has no READ or WRITE access to.

Is this related to a known bug mentioned by some fellas here that, if someone such as Admin logins to TM1 using Perspective and then logouts, and another user with another set of credentials logins using the same client, the client may retain the credentials of previous ID and thus granting the 2nd user access to restricted data.

I tested the scenario where only one user uses the client and that ID still manages to access data that he has NONE rights to.
Planning Analytics latest version, including Cloud
User avatar
Steve Rowe
Site Admin
Posts: 2407
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

Re: Securtity Rights not working in Excel tables

Post by Steve Rowe »

William,
In the scenario you describe I would definitely say this is a bug and I'd raise with IBM.
Cheers
Technical Director
www.infocat.co.uk
Bert
Posts: 9
Joined: Wed Jun 04, 2008 7:37 pm

Re: Securtity Rights not working in Excel tables

Post by Bert »

Have you used cell level security for the cube in question? If so, where defined, this will override the dimension element security.

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

Re: Securtity Rights not working in Excel tables

Post by lotsaram »

Bert - I wouldn't recommend cell security. It's not a path you generally want to go down as it almost inevitably leads to performance degradation.
Alan Kirk wrote:SubNm / DimNm have always returned all elements whether the user has access to them or not. This isn't a bug; were it otherwise the return value of a SubNm formula which used the Index number argument would be a moveable feast depending on the security access of the individual recalculating it ... on some of my dimensions I have an attribute called ReadAccess ... When I need to populate a combo box in an Excel application, I use SubNm or DimNm to loop through all of the elements (knowing that that will return every element), and populate the combo box only with those elements where the attribute returns Y. In that way the combo box is only populated with the elements that the user can see.
Alan - sorry old chap but you are definitely wrong on this one for SUBNMs. In fact the indexes of (named) public subsets are a moveable feast depending on the security level of the individual. This should be true whether the user is browsing the dimension in the subset editor or calling subset members in Excel/TM1 web with a SUBNM formula. (This is actually a very handy feature for Excel or web applications to default a title element to a member that the user has access to.) This is quite easy to test on any dimension that has element security and some named public subsets. It also makes me wonder whether Alan's method is necessary to fill list boxes to pick from (if using SUBNM not DIMNM) as the same effect could be achieved by just filling from a range of SUBNM formulas.

Alan is correct that DIMNM will return all dimension elements regardless of security access of the user and this is also true for SUBNM if the subset name argument is blank or "ALL." I can understand why DIMNM does what it does but IMO this inconsistency in SUBNM behaviour between a named public subset and the implicit "subset all" is quite undesirable and should be fixed.
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: Securtity Rights not working in Excel tables

Post by Alan Kirk »

lotsaram wrote:Bert - I wouldn't recommend cell security. It's not a path you generally want to go down as it almost inevitably leads to performance degradation.
Alan Kirk wrote:SubNm / DimNm have always returned all elements whether the user has access to them or not. This isn't a bug; were it otherwise the return value of a SubNm formula which used the Index number argument would be a moveable feast depending on the security access of the individual recalculating it ... on some of my dimensions I have an attribute called ReadAccess ... When I need to populate a combo box in an Excel application, I use SubNm or DimNm to loop through all of the elements (knowing that that will return every element), and populate the combo box only with those elements where the attribute returns Y. In that way the combo box is only populated with the elements that the user can see.
Alan - sorry old chap but you are definitely wrong on this one for SUBNMs. In fact the indexes of (named) public subsets are a moveable feast depending on the security level of the individual. This should be true whether the user is browsing the dimension in the subset editor or calling subset members in Excel/TM1 web with a SUBNM formula. (This is actually a very handy feature for Excel or web applications to default a title element to a member that the user has access to.) This is quite easy to test on any dimension that has element security and some named public subsets. It also makes me wonder whether Alan's method is necessary to fill list boxes to pick from (if using SUBNM not DIMNM) as the same effect could be achieved by just filling from a range of SUBNM formulas.

Alan is correct that DIMNM will return all dimension elements regardless of security access of the user and this is also true for SUBNM if the subset name argument is blank or "ALL." I can understand why DIMNM does what it does but IMO this inconsistency in SUBNM behaviour between a named public subset and the implicit "subset all" is quite undesirable and should be fixed.
Interesting... you're quite right for subsets which AREN'T the "All" or "" subset, which is the only type that I've used for this purpose. (Silly me in assuming that all subsets work the same way.)

As far as using that as a substitute for the combo box is concerned, it would be necessary for the subset concerned to be created by MDX otherwise the maintenance (on any dimension which has a regular change of elements, which our ones do) would be nightmarish.

However this DOES open up a world of opportunity for default views, which presumably some people are already leveraging. That being the case, "fixing" it may cause more problems than it solves unless the fix is to change the behaviour of the "ALL" or "" subsets to match that of any other public subset, and leaving the DimNM function as the one to use if you want a specific index to return a specific value.

Even then the change would need to be well advertised in advance since some people are apt to be using the existing functionality of the All subset.
"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.
Bert
Posts: 9
Joined: Wed Jun 04, 2008 7:37 pm

Re: Securtity Rights not working in Excel tables

Post by Bert »

I wasn't suggesting to use cell level security. The reason for asking if it has been used was that it could explain why a user can see data for elements that they have no access to. If cell security has been defined as Read or Write then this will override the None set for the dimension element security. The user may not be able to see the data in a cube view because they can't select the elements, but they could see the data in a spreadsheet using a correctly constructed DBR \ DBRW formula.

Bert.
Post Reply