Create Security Filters Based on Relational Sources for Live and In-Memory Analytics

Starting in MicroStrategy ONE Update 11, MicroStrategy supports to the ability to create security filters based on relational sources for live and in-memory Analytics in all clients out-of-the-box.

MicroStrategy ONE Update 10 introduces a preview feature where administrators can create security filters (row-based security) based on rules defined in relational sources, applicable to both live and in-memory analytics for all users. This is achieved by creating a security filter based on a Freeform SQL report.

This feature works by reading the data elements permitted to be seen by the user through the Freeform SQL report. These elements are included in the user session’s resolved security filter. The security filter is then applied in the same way as any other normal security filter when a report queries data against live data sources or in-memory cubes.

Many MicroStrategy customers/OEMs have a relational database based security model. This means they maintain one or more tables in the database that remember which attribute elements are visible for a specific user. The example below identifies the region elements that are visible for each User_Login in the User_Region table.

This feature allows you to define a security filter based on a Freeform SQL report. The Freeform SQL report relies on the user login system prompt to filter the corresponding data for a particular user.

Get started by checking out the following topics:

Create a Security Filter Based on a Freeform SQL Report
Enhancements in MicroStrategy ONE Update 11

Create a Security Filter Based on a Freeform SQL Report

Choose File > New Freeform SQL Report to create a Freeform SQL report with a user login system prompt to get user-specific elements from the security table in warehouse. With the USER_REGION table maintained in the warehouse as shown above, the Freeform SQL report can be defined as follows:
Copy
```
select Region_id from User_Region Where user_login = [? User Login]
```
Choose File > New Security Filter to create a security filter and add the Freeform SQL report you created in the previous step. The Freeform SQL report used as a security filter must only have project attributes and a user login system prompt.
Apply the security filter to users. The image below shows the SF01_FFSQL01 security filter applied to the SF3 user.
Log into the assigned project with the specified user. In the image below, only limited Region attribute elements are visible for the SF3 user when executing any report or dashboard in the project.

The security filter fails if the Freeform SQL report you added is invalid. This can occur if the Freeform SQL report contains managed attributes, metrics, security filter expressions, or normal prompts.

Enhancements in MicroStrategy ONE Update 11

A report cache is generated for the Freeform SQL report used in the security filter. Freeform SQL report caches can be monitored and subscribed just like other caches.
Users that get the same resolution results from a Freeform SQL report in the same security filter share the dashboard, document, or report cache.

The SF4 user has the exactly the same elements from the Security table in the warehouse as the SF3 user. The SF01_FFSQL01 security filter is applied to the SF4 user. When only SF3 and SF4 have the same security filter, they hit the same cache when executing the same dashboard, document, or report.