topic MDX for authorization in a cube in Administration and Deployment

MDX for authorization in a cube

Criptic — Wed, 25 Jan 2017 09:33:56 GMT

I have an MDX statement which works on a hierachie and is supposed to show certain groups ony the data they are allowed to see. Some Users are also in groups that are not allowed to see any data in this cube but have some other groups that are allowed to see data.

My statement works finde as long as the user only has groups that are allowed to see at least some data. If the user also has a group that isn't allowed to see any data I get an data set is empty error.

I need the user to still be able to see the data he is allowed to see, even though he has groups that aren't allowed to see data. Is there way to achieve this?

Thanks in advance.

Re: MDX for authorization in a cube

anja — Thu, 26 Jan 2017 13:35:27 GMT

Hi,

I believe what you might "hit" here are conflicting permissions where deny simply takes precedence.

When you have denies and grants at the same time, the deny will always take precedence.

Maybe someone else has some ideas on it, but I am thinking that you might have to restructure and rethink the way your groups are being set up.

You cannot have one user in two different groups where one group has a grant and the other a deny.

The option to grant would be to assign permissions to this user directly, as a direct ACE will take precedence over group permissions.

To give an example:

Dataset A

User X is in group A … DENY on data set A for group A

User X is in group B … GRANT on data set A for group B

Assign user X explicitly to data set A and grant permissions. With the explicit ACE on the data set, all permissions for user X in groups are overwritten as the explicit Grant takes precedence.

You might be familiar with this, but if not, you might find this helpful:

http://support.sas.com/documentation/cdl/en/bisecag/69827/HTML/default/viewer.htm#n0pt0r7u55rqu2n1cdu2wvt47j78.htm

Would it makes sense to maybe restructure your groups and the members of the groups?

Best

Anja

Re: MDX for authorization in a cube

PaulHomes — Fri, 27 Jan 2017 02:46:15 GMT

Because these can be quite tricky to troubleshoot, and there are a number of different ways this can be done, perhaps you can post a concrete example (changing names/values as appropriate to protect privacy) for an individual where it is failing including:

1) The identity heirarchy for the individual showing which groups they are a member of an how they are a member - this is used to prioritize access controls.

2) All relevent permission conditions that have been applied to the dimension for any of the groups in the individuals identity hierarchy (including SASUSERS and PUBLIC)

Additionally, we have a (commercial) Metacoda Permissions Tracer plug-in that can show all of the relevant (and irrelevant) permissions (and permission conditions) for a user's access to a cube dimension including precedance info based on access control type and identity hierarchy levels. I'd be happy to walk you through it via a web meeting if you want to try it out.

Re: MDX for authorization in a cube

JuanS_OCS — Sat, 28 Jan 2017 15:23:13 GMT

Hello @Criptic,

yours is a good question that any SAS Administrator should be aware of.

Full documentation of SAS Administration: security http://support.sas.com/documentation/cdl/en/bisecag/69827/PDF/default/bisecag.pdf

As explained by both @PaulHomes and @anja, indeed, when there is a conflic on metadata permissions at the same level of security, for security reasons the deny setting takes precedence.

To easen your read task, I reccommend you some basics:

A good start on Security: http://support.sas.com/resources/papers/proceedings16/10962-2016.pdf

One security model that will help you to avoid those situations in the future, the Danish model: http://support.sas.com/resources/papers/proceedings11/376-2011.pdf

All in all, if you cannot get used to the security, I would take the advise from @PaulHomes about the Metacoda tool (a great one), or ask for consulting services to help you.

PS. did you had the opportunity to google a bit or even search in the communities before posting? Here is a similar question answered already, and there are many more. https://communities.sas.com/t5/General-SAS-Programming/Metadata-permissions-conflict/td-p/195482

Best,

Juan

Re: MDX for authorization in a cube

Criptic — Mon, 30 Jan 2017 07:27:19 GMT

You got me on the right track. Thank you!

Re: MDX for authorization in a cube

Criptic — Mon, 30 Jan 2017 07:28:20 GMT

Thank you for your answer. The Plug-In sounds interesting but I was able to solve my problem, so right now I'm not interested but I'll keep it mind.

Re: MDX for authorization in a cube

Criptic — Mon, 30 Jan 2017 07:28:55 GMT

Thank you for the guide, it will be helpful on reading up on the matter!