Monday, January 28, 2013

How to use the same security dimension table to allow users to access their own data and their manager’s data (row level security in framework manager)

Please see sample below for an organization structure

The requirement is user Employee111 can only see his data and his manager (manager11) data, but not his co-worker data.

To provide a good data model in framework manager, this structure is flatted into the table below ( please see old post How to flatten hierarchy and associations for detail)


Given that there is a fact table as below

Employee 111 should see 100 sales.
Employee 111 should see 300 sales for his manager (department)

Case 1: It is easy to get Employee 111 see his own sales by applying data security with user ID level 4 based on active directory Employee111. It is translated into SQL statement like

[Security Dimension Table].[Level4] = ‘Employee111’

Employee 111 will get 100 sales

Case 2: Apply the similar data security, we can assign active directory Manager11 to Level3 . then It is translated into SQL statement like

[Security Dimension Table].[Level3] = ‘Manager11’

Employee 111 will get 300 sales for his manager

The issue here is how to assign this data security with two different cases.


The solution is to create two different security dimension query subjects with the same physical table, then assign security to these two different query subjects, and then create star scheme with these two query subjects

The detail implementation is listed below

Step1: Create two mode query subjects based on same security dimension table:

[Security Dimension Table level4]
[Security Dimension Table level3]

Step 2: Assign [Security Dimension Table level4]  for level4 users in active directory, while assign [Security Dimension Table level3]  for level3 user group in active directory

Step 3: Create scheme using two Security Dimension Tables with id join

Sales ------ [Security Dimension Table level4]  ( = [Security Dimension Table level4] .id)
Sales ------ [Security Dimension Table level3] ( = [Security Dimension Table level3] .id)

Step 4: Use [Security Dimension Table level4]  when generating report based on level4, and use [Security Dimension Table level3]  when generating report based on level3

The report should looks like follows


Please note that data security only apply when dimension query subject with data security is used in report.  In other word, even you assign data security for a query subject, but the query subject is NOT used in report, the data security won’t apply at all. Please see old post for row level security for detail.

The idea above could become generic by applying the similar situation, such as association.  

No comments:

Post a Comment