Data Permissions (Also known as Mandatory User Filters) allow project developers to filter content that is delivered to individual users. For more information, read the article for beginners.
The article above describes you the “classic” approach for the data permissions. Anyway, you can also use advanced OVER … TO clause to specify the data access by data itself. You can limit the data that each user can see from a single mapping dataset. If the mapping changes, you do not have to update existing data permissions; you just add new data permission objects for new users. In addition, the OVER…TO syntax can reduce the number and complexity of data permissions in your logical data model.
Use Case: data permissions in a M:N relationship model
Suppose you have a project that tracks performance of athletes across events. Since an athlete can appear in multiple events and a single event can have multiple athletes, it is a typical M:N mapping.
The simplified data model contains four fact tables, one of which is a many-to-many relation:
Suppose that you want to control data access based on the unique row identifiers in the Athletes Events M:N dataset.
There are two paths that end at the Athletes M:N dataset:
- First path: Athlete > Athletes Events M:N
- Second path: Sponsor > Event > Athletes Events M:N
Here’s how you can filter data on the three datasets along the second path by using only one data permission object per user.
Configure data mappings
We will add a mapping dataset called User Mapping to our example data model. This dataset allows a single user to be mapped to any number of records in the Athletes Events M:N dataset. This is possible because each record in the Athletes Events M:N contains a unique identifier to the combination of identifiers from the Athlete dataset and from the Event dataset:
The User Mapping dataset must be connected to the other datasets through the Athletes Events M:N dataset, so that all other datasets in the model are parents of the new dataset and are therefore on the same path.
The following table shows the content of the User Mapping dataset:
|User Access ID (ConnectionPoint)||User ID||User Login||Reference to Athletes Events M:N dataset|
Create the data permission expression
We will use the User Mapping dataset to filter all parent datasets along the same path:
Sponsor → Event → Athletes Events M:N → User Mapping
This can be done by the following data permission expression:
([URI_1] = [URI_2]) OVER [URI_3] TO [URI_4]
To be more specific:
([User_Attribute_URI] = [User_Attribute_Value]) OVER [Mapping_ID_Column] TO [Parent_Attribute_In_Hierarchy]
- URI_1 is `User ID` field (connection point of User dataset)
- URI_2 is specific user (attribute element id)
- URI_3 is the connection point of the User Access dataset
- URI_4 is the connection point of Sponsor (top parent in the hierarchy)
Here’s an example of the entire data permission expression in our test project (the parentheses are required):
OVER [/gdc/md/vcs6tmt5prilun25l5chz9q0qc5rpd2x/obj/368479] TO
To demonstrate what will happen, let’s populate the Athletes Events M:N dataset with some records:
|Athletes Events M:N Connection point||Event Reference||Reference to Athlete Connection|
As a result, based on the mapping defined in the User Mapping dataset, User 1 (firstname.lastname@example.org) can only see data associated with records acceaafcabbe4959bb13e1d73b2f99a9 and 4a52e256a68b4c3893c10c2961ba6ccd. This means that a report with an attribute from the Athletes Events M:N dataset will show only data for those 2 records.
It works in the same way for both the Event and the Sponsor datasets. In the first case, only records A1 and A2 will be shown. In the latter case, only records associated with A1 and A2 will be shown. In other words, the values are propagated from child dataset to parent datasets (from the User Mapping dataset to the Sponsor dataset).
The limit on the length of a filter expression is 100,000 characters.
Assign the data permissions to user
The last step is to assign the data permissions to the user. You can read more in the related tutorial.
It is important to note that this data permission does not filter data in the Athlete dataset because it is not on the data path defined in the data permissions. If you created a report with an attribute (from the Athlete dataset) and without any facts, all the data would be viewable to all users. To filter the Athlete dataset as well, you may use one of the following options:
- Create a new data permission with the OVER … TO clause (for the path Athlete → Athletes Events M:N → User Mapping)
- Include the data from the Athlete dataset in the Athletes Events M:N dataset