Dynamic Secury Filter

When I say dynamically apply the security filter, I am not talking about having multiple securty filters and dynamically choosing the filter on user login.  Lets look at this example. I have ten region, and each region has one manager. I want to allow each manager to view the data for his region. Only his region. 

You can either make use of database level security feature, by mapping user connections. If data for all the regions are in the same table, you can still apply row level security. How do we apply this security filter from MSTR ?

You can create 10 different security filters, and add it to each region manager. In case if you are going to have new manager for these region, you can create 10 user group and apply security filter to these user groups. This is a failry easy example. Now my region grew and I have now 100 regions defined. How we apply the security filter ? Create 100 different security filters and groups. God, I am going home.. 

Its not practical to handle this situation like this.  You can make use of system prompt, [User Login] and database tables, to make this work easy. You will need to create a table. You need to be careful with the columns. Since in this case you are going to create a filter on Region for each region manager, you need to create a table with Columns for Region attribute ID and a new attribute for User (USER_ID, USER_DESC).

If your requirement is to create a filter based on country, in the new table should contain the  ID column for Country attribute and user attribute.  If your region id is mapping to REGION_ID, you creare the column name as REGION_ID. This way you do not want yo remap the ID form of Region attribute to the column in the new table.

We want this table to me joined in the SQL and for a correct sql join tree, it is important to define the relationship between User and Region. You create the attribute on wchich you want to filter on as a child of user. So in this case the relationship between user and region is one to many children. One user can have one or more region.

Now you can create an security filter as User@DESC = ?[User Login] . Note that you have to filter user attribute on DESC and match exactly to a custom value as "?[User Login]" . You might not be able to drag drop the system prompt. Dont worry about it.

Assign this security filter on the user groups. Now update the table that you created in the database with values for Region manager, based on his user login and the region to which he has access.  Compared to creating n number of security filters and assigning it to user, updating the DB is easy.

Example security table.

USER_ID USER_DESC REGION_ID

1 sam 1
2 nam 2
3 Administrator 3
1 sam 3
3 Administrator 1

Look at the SQL when you execute an report. You will see the security filter applied on the WHERE clause.

truncated..................
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and

a12.[REGION_ID] = a13.[REGION_ID] and --> selects only the region available in the security table
a12.[REGION_ID] = a14.[REGION_ID]
and a13.[USER_DESC] = 'sam'  --> Gets the value from the system prompt,

Lets add some complexity to this.  

I want each Regional manager to see the data for his region and also the total for the Country to which his region belongs.

You want the metric at the country level, how do you do this ? Using a level metric we can calculate the metric at other level that that of Report Level. In this case it is Country.  How to we include all the regions in this country ?  By making the filtering as absolute.  So you create a metric with level country and filtering to absolute and grouping as standard.  This level metric will give you the total for the county in which the region belongs

If you look at the SQL you will see the below statement selecting the country for which the logged in user's region belongs to.

select distinct s22.[COUNTRY_ID] AS COUNTRY_ID

from [LU_REGION] s22,
[SECURITY] s23
where s22.[REGION_ID] = s23.[REGION_ID]
and s23.[USER_DESC] = 'sam'