cube security based on a security table

Question

Hi Gurus,

I have designed a cube with one main table role playing as 1 fact and few  dimensions. and Deployed it is working fine. but now I have a security table with userId column and col1 col2 . col1 and col2 are in main table too but not user Id field.

Now I want security to be enfored on the cube such that the user who is running the cube can only see the data based on col1 and col2 values from security table. How can I do this. Please help me with this.

Answer 1

Assuming your User ID column contains the Windows AD username of your users, you can apply dynamic security as per below...

1. Add your security table to the Cube DSV, joining it to the main table using Col1 and Col2
2. Create a security dimension based on the security table you've added in the DSV, including the User ID field as an attribute
3. Add the dimension to existing cube, you'll probably also want to set visible to false on this dimension
4. Create a new role containing the users you want to give access to the cube, you'll need to set the appropriate permissions
5. In the newly create role, select dimension data tab
6. From the dimension drop down select your security dimension
7. Under the dimension drop down, click the Advanced tab
8. Select the User ID attribute from the Attribute drop down
9. Assuming your attribute is called User ID, paste the following MDX into the allowed member set box...
   

   StrToSet ( '[Security].[User ID].&[' + UserName() + ']' )
   

10. Save and deploy the cube

What the above is doing is getting the current users username, and converting it to the relevant User ID member in the security dimension. This will then restrict the records returned by the fact table to only those joining to the selected User ID.

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 2

Assuming your User ID column contains the Windows AD username of your users, you can apply dynamic security as per below...

1. Add your security table to the Cube DSV, joining it to the main table using Col1 and Col2
2. Create a security dimension based on the security table you've added in the DSV, including the User ID field as an attribute
3. Add the dimension to existing cube, you'll probably also want to set visible to false on this dimension

So here, how can I map the fact and dimension in the Dimension Usage tab. (I meant what type of relation and between what?).

Answer 3

You could concatenate columns Col1 and Col2 in the DSV (something like Col1|Col2) in both the main and security table, then join on this in the DSV and dimension usage tab.

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 4

Hi

Here we could also use Reference_Relation from Security DIM to FACT via Original DIM, so when the user access the data, the FACTs will be sliced by inner join the Original DIM.

Consider the Default member set and Allowed member set while adding the StrToSet in the role.

---

Prav

Answer 5

Hi

Here is an interesting thread

http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/1390367e-f5a3-4e4b-82f3-7e5f5293e4fa

 

---

Prav

Answer 6

You could concatenate columns Col1 and Col2 in the DSV (something like Col1|Col2) in both the main and security table, then join on this in the DSV and dimension usage tab.

The datatype of these columns are nvarchar so when I tried to implement (col1 | Col2 ) It says it could not be completed due to the datatypes.

Please let me know If I am doing correct??

Answer 7

Hi

Here we could also use Reference_Relation from Security DIM to FACT via Original DIM, so when the user access the data, the FACTs will be sliced by inner join the Original DIM.

Consider the Default member set and Allowed member set while adding the StrToSet in the role.

---

Prav

Hi Prav_SQL,

 

I did not understnad what do u mean by Orignial DIM . Here, I only have security DIM and my original fact table . The remaining DIMS are derived from this fact table so I cannot see them in the DSV or in the cube designer window.

I am wrong can you please explain me, !

Answer 8

Can anyone help me with this please ???? thanks