topic Row level security query in SAS Web Report Studio

Row level security query

Jamie — Tue, 17 Jan 2012 11:59:39 GMT

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

Row level security query

Jamie — Tue, 17 Jan 2012 12:02:01 GMT

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

Row level security query

PaulHomes — Wed, 18 Jan 2012 04:07:26 GMT

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

Row level security query

Jamie — Wed, 18 Jan 2012 09:52:14 GMT

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.

Row level security query

PaulHomes — Wed, 18 Jan 2012 12:02:20 GMT

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

Row level security query

Jamie — Wed, 18 Jan 2012 12:19:09 GMT

Thanks Paul, thats very helpful indeed.