Hi folks hope you can help
I have just created an info map which has a column named "company". I have created a simple report on WRS which views a table with all companies.
there are about 50 different companies and we have now set up user ids for each one.
Is it possible to use row level security on SAS Information Map Studio 3.1 to conditionally show only the companies for certain user IDs?
Thanks in advance
Jamie
Hi Jamie,
For OLAP cubes you need to do the filtering at the level of the OLAP cube itself using OLAP Member Level Security. This is preferable anyway since the security is also applied when you open the cube directly without using an information map (such as from SAS Enterprise Guide). The documentation for this is also in the SAS 9.1.3 Intelligence Platform Security Administration Guide, Second Edition under OLAP Member-Level Permissions.
I know in SAS 9.2 you can also use identity-driven substitutions in the MDX filter expression, but I am not sure about SAS 9.1.3. I did a blog post on this topic for SAS 9.2 a while back: SAS 9.2 OLAP Cube Identity Driven Member Level Security. The post contains a few MDX examples and there are some useful comments by others too. It has been a while since I have done OLAP Member Level Security in SAS 9.1.3 so I am not sure if identity-driven substitutions were available in SAS 9.1.3 or if they were new in SAS 9.2. I can't find any references to them for OLAP member level security in the SAS 9.1.3 docs, but who knows... it might be worth giving it a try. Perhaps someone else might be able to confirm whether SAS 9.1.3 supports this or not.
Cheers
Paul
further info: If I create a filter to say show only "Company A" how do I then associate that with a User ID?
Thanks again
Hi Jamie,
If you have a small number of relatively static companies, and have those companies users in company groups in metadata, then you could add a number of company filters in the information map and use access controls to force the use of each for its respective company group (authorization-based prefilter).
If you have a larger number of companies that might also change regularly and the mapping of users to companies is readily available as a data source, then another alternative is to apply a userid based identity-driven general prefilter to a userid-company mapping table as a mandatory table joined to your main table(s) via the appropriate company key. The resulting data returned by the information will be subsetted to the company (or companies) that the logged in userid is associated with.
You can find more information about this (for SAS 9.1.3) in the SAS 9.1.3 Intelligence Platform Security Administration Guide, Second Edition, Chapter 10 BI Row-Level Permissions
Depending on your security requirements you may also want/need to consider the possibility of mediated data access. Whilst you might not actually need it, its still worth reading at least to determine whether it is required for your environment or not.
Cheers
Paul
Hi Paul,
First of all thanks very much for taking the time to reply. Much appreciated.
I was able to set up a working test using your second suggestion of "userid basedidentity-driven general prefilter " however I could only apply this to a data table and not a cube. The edit data item option doesnt seem to be there when using a cube. It worked nicely when using a table though, and I also set up a test security associations table and linked it which also works.
I was reading on the sas support site about your first option. I created the filter but when I went into the authorization properties and granted explicit read access, the "Add condition" button is also missing. Again, when usign a data table this option is available, but with the cube it isnt.
I was reading into the system and evironment requirements and I noticed that Service Pack 4 is required. I currently am using Service Pack 2. Do you know if its possible that these optiions became available for cubes with the Service pack update?
Thanks again for any help, it makes the difference.
Hi Jamie,
For OLAP cubes you need to do the filtering at the level of the OLAP cube itself using OLAP Member Level Security. This is preferable anyway since the security is also applied when you open the cube directly without using an information map (such as from SAS Enterprise Guide). The documentation for this is also in the SAS 9.1.3 Intelligence Platform Security Administration Guide, Second Edition under OLAP Member-Level Permissions.
I know in SAS 9.2 you can also use identity-driven substitutions in the MDX filter expression, but I am not sure about SAS 9.1.3. I did a blog post on this topic for SAS 9.2 a while back: SAS 9.2 OLAP Cube Identity Driven Member Level Security. The post contains a few MDX examples and there are some useful comments by others too. It has been a while since I have done OLAP Member Level Security in SAS 9.1.3 so I am not sure if identity-driven substitutions were available in SAS 9.1.3 or if they were new in SAS 9.2. I can't find any references to them for OLAP member level security in the SAS 9.1.3 docs, but who knows... it might be worth giving it a try. Perhaps someone else might be able to confirm whether SAS 9.1.3 supports this or not.
Cheers
Paul
Thanks Paul, thats very helpful indeed.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: