Role based security as input paramenter, data read from oracle db on demand

[metalray]

Hi guys,

I am thinking about security in the moment.
We have about 3000+ end users that all have access to their data.
Setting all those permissions in the management studio will take forever so
I need to have some kind of general rule that all users (single sign on/LDAP)
have access to their own OLAP cube member "myname" on the dimension "dim_names" and the other dimensions
do not matter. how would you do it?

some of those users are team leads and they actually can access the data of other people.
the whole security model is maintained in an oracle table. my plan was
to populate some prompts with only those member that are actually accessible to the end user in some cases
It will be a very small prompt with just "myname" but some actually show a couple of names
or a whole team.

my idea is that:

web report - > stored process - > sql pass through get user accessible members based on user session role ->
populate prompt

Is that possible? can the user role (login) be passed to a stored process.
How would that work if a thousand people do it at the same time? would the
sql pass through collapse?
I can not put all "mynames" in one group because then they could access each other data and I cant put all 3000+ "mynames" in 3000+ different groups that I all have to assign - hence I think reading it from the oracle db and having a little logic in a STP for the prompts will do it.

[Vince_SAS]

Do you already have credentials for your 3,000+ users loaded into SAS metadata, and can they successfully log into the system?

Vince DelGobbo
SAS R&D

[metalray]

Hi Vince,

yes, its imported from the LDAP. so they can login
but how do I do member level security for 3000+users on 3000+ OLAP dimension members. maintaining
that manually will take forever so I have this plan:

1. dont show them anythink in the selection prompts that they are not allowed to
select (the selection is retrieved with a sql pass through in a stored process that has
somehow taken the user rolee or ID as an input)

[PaulHomes]

Hi,

Have you considered using an Identity Driven Property in your MDX filter expression so that you have 1 or a few permission conditions rather than 3000+?

There is more info at:
* How to Assign an OLAP Permission Condition (http://support.sas.com/documentation/cdl/en/bisecag/61133/HTML/default/a003139257.htm)
* Identity-Driven Properties (http://support.sas.com/documentation/cdl/en/bisecag/61133/HTML/default/a003250936.htm#a003250949)

Cheers
Paul

[metalray]

Hi Paul,
that sounds very promising! I give it ago. Thanks a lot.

Cheers,
Bob

[metalray]

Hello Paul,
I read on the website provided that I can implement
it with SAS OLAP Server using MDX expressions/query. the
link to the document "How to Assign an OLAP Permission Condition" shows me where to click in the management console as well but I dont know how to write the MDX query that effects all bottom level members and only gets those where the salesid = =&SAS.Userid;

SalesPersonID is the bottom level member.

[SalesPersonID].[All Members].where the salesid = =&SAS.Userid;

Do I have to write an MDX query with a SELECT clause or is an MDX "expression" sufficient? I think an expression is without a select.

[Vince_SAS]

See also:

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212372.htm#a003...

The section "Applying Batch Security with Permission Tables" talks about using a "Permissions Table" that contains the cube access controls. If you can create this SAS table programmatically it could save you some typing.

Vince DelGobbo
SAS R&D

[metalray]

hi Vince, thanks for that. I will keep it as an option. before I move to it I want to see if the MDX idea work but I just noticed that clicking on tools->manage permission tables and providing the information results in OLAP Cube Studio not doing anything when clicking on "next".

back to the query, I can not restrict the query by any selection of the other dimensiosn apart from the sales channel so I hoped that someting like:

[SalesPersonID].[All Members].where the salesid =&SAS.Userid;

would be sufficient but that just gives me an error.

I found this great tutorial but it is not LEVEL specific rather member specific and it does not show how the final query with the member definition and identity driven property looks, it almost seems the author had not clue how the final query would look either 🙂

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212399.htm

[PaulHomes]

Hi Bob,

I tested this on a cube here and it worked for me. In the MDX expression filter window I used the following:

{[DimSalesPerson].[All Sales People].[SUB::SAS.Userid]}

(Note: it complains about invalid syntax but if you keep clicking through the OK buttons it will be saved and it does work)

... and when I logged in as sasdemo I only saw the subset corresponding to:

{[DimSalesPerson].[All Sales People].[SASDEMO@MYDOMAIN]}

Notice that the userid is normalized to the uppercase format with domain suffix.

Applying this to your question it looks like you could use:

{[SalesPersonID].[All Members].[SUB::SAS.Userid]}

This will only work though if your SalesPersonID values are in the normalized form USER@DOMAIN.

Thanks to Vince for pointing out the permission tables - I haven't tried them yet - I suspect they will be a lot more manageable/flexible.

Cheers
Paul
http://platformadmin.com/

[metalray]

Hi Paul,
Thanks for that. I actually did not go any further after I got the "invalid syntax" error.
When I try to add a permission table with a library that uses the SAS ACCESS engine and press Next then
nothing seems to happen. Do permisson tables work with a libraray that uses the SAS Access (Oracle) engine?
Creating a new library did not help either. I use sas olap studio 4.2 M2. - I think there is a hotfix for that

Why do you think permission tables make it more flexible? I am worried that the format of the table and providing the data in that format will be a big job.
Thanks,
Bob Message was edited by: metalray

[PaulHomes]

Hi Bob,

I haven't tried to use a SAS/ACCESS library for a permission table (not as yet anyway). I initially had trouble with a base engine library because it had a relative path. Then I saw Problem Note 40424: Permissions tables for use with member-level security are not supported for UNIX libraries or for libraries that use relative paths (http://support.sas.com/kb/40/424.html). After applying SAS OLAP Cube Studio 4.2 M2 Hotfix D06001 the relative base engine library worked. The hotfix doc doesn't say anything about SAS/ACCESS libraries but if it were me I would give it a try anyway.

I only say that permission tables might be more flexible because they should be easy to manage, can be programmatically generated even including programmatically generated MDX expressions (with the full power of Base SAS behind it). It should be a whole lot easier than point and click when you have more than a handful of permission conditions.

BTW - I will be posting a blog article on the identity driven member level security example if you are interested in taking a look. I am also thinking of doing a post on the format of the permission table. I can't find much documentation for it so have been investigating the table and the generated SAS code that reads/writes the metadata permission conditions to get a better idea of how the table should be populated.

Cheers
Paul
http://platformadmin.com/

[metalray]

Hello Paul,

I used the example you described but this did not work well: {[SalesPersonID].[All Members].[SUB::SAS.Userid]}
I got the syntax error but additionally the web report could not display anything in the cube (so I think the invalid syntax broke it).

n error occurred in processing the report element. Please refresh the page to try again. Contact your administrator if this problem persists.
[a12962322664760.05642803333271551_0_0] Error Rendering the Report
Unable to access the OLAP server for cube DEV_MLS_CUBE_V2.

Returning to fix my syntax and using EG 4.2 as a inspiration I was unable to make out how to apply a filter to a bottom level or any level that is not the top one (whole dimension).

WHERE ([SALESPERSON_ID].[All Members])
WHERE ([DIM_SALES].[SALESPERSON_ID].[All Members])
Both cause the error:
"Invalid Dimension Name was encountered in the MDX statement"

Back to permission tables:
I tried what you did (also using D06001) but failed creating a permission table with the error 76: LINE and COLUMN cannot be determined.
Assuming that my SAS/ACCESS Library has the permission to create an Oracle table in a
Oracle Schema for developers (which is already very unlikely and I dont even know how to grant such a right to create tables in
other schemas) I could still not imagine how this permission table would be represented on the SAS side. Would it be a SAS VIEW
that SAS created by itself to show me in SAS the Oracle talbe it created seconds before?

Having an empty permission table seems like a lot of work to fill manually.
I dont see that it has a benefit over the approach via member level permission in that respect.
I see howerver, the high potential of reusability. is that correct?

Thanks for your reply and your brilliant blog! What you wrote should go in the SAS documentation.

Bob

[PaulHomes]

Hi Bob,

Regarding the MDX error:

With the OLAP cube open in SAS Enterprise Guide did you drag the appropriate dimension to the slicer, drill down on it to a specific user and then view the generated MDX? You shouldn't see an Invalid Dimension Name error if that was the case. Can you show us an example of the MDX generated by SAS Enterprise Guide?

Regarding permission tables:

Do you have the log containing that "LINE and COLUMN cannot be determined" error message? I would be interested in seeing it in context.

If it is possible to use a permission table via a SAS/ACCESS Interface to Oracle library (I don't know for sure it is) then you would need appropriate permissions to create the table in the Oracle schema - your Oracle DBA should be able to help you with that. There would be no view within SAS as the Oracle table would be used directly - just like if you assigned a library in a SAS program pointing to the Oracle database. Is there any specific need for the permission table itself to be kept in Oracle vs keeping it in SAS? Once the table is registered its contents could always be dynamically created from the contents of Oracle tables.

The permissions table seems to have a fixed format with specific columns that enables OLAP Cube Studio to apply all of the permission conditions via the generated batch code. Here is an example permissions table which just has 2 rows to apply geographic based security on the DimGeog dimension. Users in the US group can only see US data and users in the AU group can only see Australian data.

fullname, olapschema, cube, dimension, permission, perm_type, mdx_condition
-----------------------------------------------------------------------------------------------------------------------------------------------------
US Users Group, SASApp - OLAP Schema, MyCube, DimGeog, Read, GD, {[DimGeog].[All Countries].[U.S.A.], Descendants([DimGeog].[All Countries].[U.S.A.])}
AU Users Group, SASApp - OLAP Schema, MyCube, GimGeog, Read, GD, {[DimGeog].[All Countries].[Australia], Descendants ([DimGeog].[All Countries].[Australia])}

I have left out the items and remove_ace columns as they are blank in this example.

If you were going to fill the table out manually then the advantages over manually applying the rules with point and click methods are somewhat lessened but there are still benefits. With a table it is easy to re-apply them if the cube was deleted and recreated for example. I see the biggest benefit from a dynamically generated permissions table - imagine a SAS program that queries an LDAP directory (or an Oracle database) to determine what user groups are available and what those groups have access to, then generates a permissions table containing hundreds of rows - much faster than point and click and much easier to reapply when the rules change.

Hope that helps.

Cheers
Paul
http://platformadmin.com/

[metalray]

Hi Paul,
I agree on your permission table analysis.
Regarding the Member-Level Permission I am still not where I want to be:

If I drill down to the Salesperson_ID, using a filter on the sales person ID, the MDX Query generated looks like this:

SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
Hierarchize(Filter({ [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].Children }, ([DIM_Sales].[DIM_Sales].CurrentMember.Level.Ordinal < [DIM_Sales].[SalesPerson_ID].Ordinal OR Intersect(Ancestors([DIM_Sales].[DIM_Sales].CurrentMember, [DIM_Sales].[SalesPerson_ID]), Filter(Generate({ [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].Children }, Ancestors([DIM_Sales].[DIM_Sales].CurrentMember, [DIM_Sales].[SalesPerson_ID])), kindex(kupcase([DIM_Sales].[DIM_Sales].CurrentMember.Properties("CAPTION")), kupcase("040347788")) <> 0)).Count > 0))) ON ROWS
FROM
[DEV_MLS_CUBE_V2]

If I dont use a filter and do a right click "Isolate 040347788", then the query looks like this:

SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
{ [DIM_Sales.[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].[040347788] } ON ROWS
FROM
[DEV_MLS_CUBE_V2]

In both cases its a valid query but no WHERE clause I can use to specify an Member-Level Permission MDX expression.

--error , invalid member name at SALESPERSON_ID
SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
{ [DIM_SALES].[All DIM_SALES].Children } ON ROWS
FROM
[DEV_MLS_CUBE_V2]
WHERE [DIM_SALES].[SALESTEAM].[SALESPERSON_ID].[All Members].['johnsmith']


--error , invalid dimension name at SALESPERSON_ID
SELECT
CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS,
{ [DIM_SALES].[All DIM_SALES].Children } ON ROWS
FROM
[DEV_MLS_CUBE_V2]
WHERE [SALESPERSON_ID].[All Members].['johnsmith']


Thanks for your help,
Bob

[PaulHomes]

Hi Bob,

It looks like the MDX you posted in the forum thread has been mangled along the way. The forum watch email I received has much more MDX in it than I can see here on this page.

Is the SalesPerson_ID (040347788) the same as the userid? Is your SAS OLAP server on a UNIX or a Windows platform? Watch out for the fact that the user id that will be substituted into the SUB::SAS.Userid placeholder will be normalized - upcased and including a domain suffix on Windows (like 040347788@YOURDOMAIN).

It looks like your userid / SalesPerson_ID level has a number of parent levels above it in the DIM_Sales dimension. One of the difficulties is to express the filter in terms of the lowest level userid member without using/knowing the values of its parent members. You cant use '[DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].[SUB::SAS.Userid] because whilst that might work for 040347788 it probably wont for many other user ids that have different parents members in that dimension - ie. something other than [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ]

One way of expressing the filter - if the userid is a leaf (has no descendants) and it's member name doesn't also appear elsewhere in the tree under different parents is to use something like:

Filter( [DIM_Sales].AllMembers, [DIM_Sales].CurrentMember.Name = '040347788' and [DIM_Sales].CurrentMember.Level.Name = 'SalesPerson_ID' )

.. that results in a set (hopefully of 1). The SAS OLAP server complains about sets in a slicer (WHERE) so you can use the Head() function in an MDX WHERE clause variation to only take the first item in the set (assuming you know there are no duplicates):

WHERE Head( Filter( [DIM_Sales].AllMembers, [DIM_Sales].CurrentMember.Name = '040347788' and [DIM_Sales].CurrentMember.Level.Name = 'SalesPerson_ID') ) )

You can test this out in SAS Enterprise Guide by adding it as a slicer to an existing table that has no slicer - just make sure that table doesn't already include the DIM_Sales dimension in its rows/columns already since you can't use a dimension in the slicer and in rows/columns at the same time. If you want to use the DIM_Sales dimension in the body of the table then you will need to integrate a filter into the rows/columns components instead of using WHERE (just like in the EG generated MDX you posted). I just find working on a filter I can readily cut/paste into a permission condition easier to do from the slicer.

If you get that working then you could try it in a member level security filter expression by replacing 040347788 with SUB::SAS.Userid (assuming 040347788 is actually the normalized form of the user id), so you end up with something like this:

Filter( [DIM_Sales].AllMembers, [DIM_Sales].CurrentMember.Name = 'SUB::SAS.Userid' and [DIM_Sales].CurrentMember.Level.Name = 'SalesPerson_ID' )

Hope this gets you a bit further.

Cheers
Paul
http://platformadmin.com/