Row-Level security in Power BI

Row -Level Security or managing roles in Power BI is not something new. But environments, where there is a need for securing read access for end-users based on their account name, are very frequent. Row Level Security is omitting and controlling access to a user or group (or distribution group in active directory) to rows on a single dataset (or table in SQL Server) and all the relationships to this dataset.

 

Creating a simple data-set using SQL Server database.  This data-set is using three tables:

•  RLS_Sales (typical sales tables containing purchase orders with Sales representative name)
• RLS_Users (shortened version of Active directory with connection to Sales representative)
• RLS__manager (relationship table between Manager and Sales representatives)

DROP TABLE IF EXISTS dbo.rls_sales
DROP TABLE IF EXISTS dbo.rls_users
DROP TABLE IF EXISTS dbo.RLS_Manager

CREATE TABLE [dbo].[RLS_Sales](
ID INT IDENTITY(1,1) NOT NULL,
OrderID INT NULL,
SalesRep VARCHAR(20) NOT NULL,
Product VARCHAR(100) NULL,
Qty INT NULL,
Price INT NOT NULL
)

INSERT INTO dbo.RLS_Sales (OrderID,SalesRep,Product,Qty, price)
SELECT 1023, 'sntk\tomaz', 'lamp', 1,12
UNION ALL SELECT 1024, 'sntk\john', 'Key', 2,24
UNION ALL SELECT 1025, 'sntk\john', 'knife', 4,42
UNION ALL SELECT 1026, 'sntk\tomaz', 'pear', 3,2
UNION ALL SELECT 1026, 'sntk\tomaz', 'Key', 1,12
UNION ALL SELECT 1027, 'sntk\jeff', 'bag', 3,42
UNION ALL SELECT 1027, 'sntk\mike', 'paper', 10,19
UNION ALL SELECT 1027, 'sntk\mike', 'phone', 1,62
UNION ALL SELECT 1027, 'sntk\stone', 'bottle', 10,23

CREATE TABLE dbo.RLS_Users
(
ID INT IDENTITY(1,1) NOT NULL
,SamAccountName VARCHAR(20) NOT NULL
,Email VARCHAR(200) NULL)


INSERT INTO dbo.RLS_Users
SELECT 'sntk\tomaz','tomaz@gmail.com'
UNION ALL SELECT 'sntk\john','john@gmail.com'
UNION ALL SELECT 'sntk\jeff','jeff@gmail.com'
UNION ALL SELECT 'sntk\mike','mike@gmail.com'
UNION ALL SELECT 'sntk\stone','stone@gmail.com'
UNION ALL SELECT 'sntk\anna','anna@gmail.com'


CREATE TABLE dbo.RLS_Manager
(
ID INT IDENTITY(1,1) NOT NULL
,SamAccountName VARCHAR(20) NOT NULL
,ManagerSamAccountName VARCHAR(20) NULL)


INSERT INTO dbo.RLS_Manager
SELECT 'sntk\tomaz','sntk\tomaz'
UNION ALL SELECT 'sntk\john','sntk\tomaz'
UNION ALL SELECT 'sntk\jeff','sntk\tomaz'
UNION ALL SELECT 'sntk\mike','sntk\stone'
UNION ALL SELECT 'sntk\stone','sntk\tomaz'
UNION ALL SELECT 'sntk\anna','sntk\tomaz'

Please note, that primary and foreign key can be added, I deliberate left them out, to do data integrity in Power BI.

Once we have the data set, we can fire up the Power BI. In Power BI, simply import all three tables. In addition, I have set the relationship between the tables as following:

Reason behind is, mainly due to the fact, that I wanted to cover two use-cases in one example:

•  single user can see own sales
•  manages can see sales from all the employees

In this way, I can have a manager see all the employees and own sales statistics, where as, employees (that are not managers) can only see their own sales statistics.

Once the relationships are finished, go to Manage Roles:

And create a new role; mine is called simply “Security”.

Navigate to RLS_Manager and add DAX expression:

[ManagerSamAccountName] = USERNAME() || [SamAccountName] = USERNAME()

Why I am putting security on Manager table and not User table? In this way, I don’t need to to Lookup to Manager table or I don’t need to have a bridge table between manager and sales; this Manager table is itself already “kind-of” a bridge table. In order to have both scenarios covered (single user can see own sales and manager his employees + own), I am doing OR logical operator, so If I enter Manager name or Sales Representative name, any given scenario will return the sales.

Save the DAX code and now click on “View As” (next to Manage Role) and select “Security policy” (this is the one, we created beforehand) and “Other user” policy and type in one of the user names (user names are in RLS_Manager table)

This will access and view the Power BI document as if the security policy is taking place. Since we have selected “sntk\tomaz”, complete document is converted to show only relevant sales statistics for this particular user.

 

Use it wisely. In this blogpost, I have used DAX function USERNAME(), but if you are using Active Directory directly, I recommend you using USERPRINCIPALNAME().

 

As always, code and Power BI to this example, is available at Github.

 

Happy Power BI-ing! 🙂

‹ Custom Power BI visual for Line chart with two Y-Axis

Essential list of useful R packages for data scientists ›

Tagged with: access, Power BI, Power BI Desktop, row level access, row mode, row-level, security, T-SQL
Posted in Uncategorized