Dynamic Row Level Security (RLS) on Power BI

What is our Goal?

Restrict access to the user at the dataset level.

For example, If A, B and C are the Employee of XXX Company. All the employee’s data available in company data base. When A login to HR Application Then A can see only it’s related data. A cannot see other colleague data. This is the actual concept of Row Level Security.

Today we are going to implement it through POWER BI. To do that we have taken 2 Tables named ‘EmployeeHierarchy’ and ‘Fact_IncrementAmt’.

Now we have to apply security based on employee’s username in PowerBI window.

Step1: Go to Relationship window and create relation between ‘EmployeeHierarchy’ and ‘Fact_IncrementAmt’ based on EmpName column.

Step2: Go to Report window and create a table visualization using both column in ‘Fact_IncrementAmt’

Step3: Click on Modeling tab, from there click on ManageRole Options. Now click on create button to create a role and give a name of the role.

Step4: Click EmployeeHierarchy table in ManageRole window and write below DAX query into it.

[ReportsToMan] & "@sqltopossible.com" = UserPrincipalName()  ||

[Level1] & "@sqltopossible.com" =UserPrincipalName() ||

[Level2] & "@sqltopossible.com" =UserPrincipalName() ||

[Level3] & "@sqltopossible.com" =UserPrincipalName() ||

[Level4] & "@sqltopossible.com" =UserPrincipalName()

Step5: Save the entire solution and publish the Power BI report.

Note: Here I have created EmpEmail as EmpName+’@sqltopossible.com’ to make the security filter easy. First I extract username from PowerBI Login page, then compare it with ReportsToMan column with added string @sqltopossible.com.

Test case

Before publishing the report, we can test the report by giving Username in ViewasRole Window.

Let’s test the report.

Here ‘Subhdip’ is the BOSS of this table, so he can see all the Employee’s data.

Here ‘Pritam’ can’t see ‘Subhdip’ data, but he can see ‘Sourav’ and ‘Anurag’ data because of he is the manager of ‘Sourav’ and senior manager of ‘Anurag’.

Since ‘Anurag’ is lowest level employee, here he see only his data.

 

Type 2 Slowly Changing Dimensions (SCD) with T-SQL using Merge statement

Changing value of Attribute in dimension table is called Slowly Changing dimensions in Data ware house (DWH).
To know it more please go to some web site in Internet. Today we are going to Implement SCD 2 by T-SQL.
We have taken a dimension table named ‘SCD_Emp’ and data source table named ‘Table_Dump’

CREATE TABLE [dbo].[SCD_Emp](

       [Id] [int] NULL,

       [Name] [varchar](200) NULL,

       [Dept] [varchar](50) NULL,

       [StartDate] [date] NULL,

       [EndDate] [date] NULL,

       [IsActive] [bit] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Table_Dump](

       [Id] [int] NULL,

       [EmpName] [varchar](200) NULL,

       [Dept] [varchar](50) NULL

) ON [PRIMARY]

Now we have inserted 1 Row in SCD_Emp and 2 Rows in Table_Dump.

From the above picture we can see that We have 1 row for ‘Pritam’ where Department is ‘AIM’, but in source table there are 2 rows. One for ‘Pritam’ with new department ‘DOD’ and other row is not exist in Dimension table ‘SCD_Emp’.

Now we have 3 Action point.

First: Insert the row with EmpName ‘Dinesh’ into SCD_Emp from Table_Dump because it is new Entry (Id not exist in SCD_Emp).

Second: Update the existing row for ‘Pritam’ with EndDate as Yesterday’s date and IsActive as ‘False’, because this record is no more valid since new department came for ‘Pritam’.

Third: Insert new Row for ‘Pritam’ with New department ‘DOD’, StartDate as today’s date and IsActive as ‘True’.

Let’s implement it using T-SQL Script:

Insert into [dbo].[SCD_Emp]

select SCDImplementation.Id,

SCDImplementation.EmpName,

SCDImplementation.Dept,

SCDImplementation.Startdate,

SCDImplementation.EndDate,

SCDImplementation.Isactive

from (

MERGE SCD_Emp DEST

                    USING Table_Dump SRC

                    ON ( DEST.Id = SRC.Id )

                    WHEN NOT MATCHED

                        THEN

              INSERT  VALUES

                ( SRC.Id ,

                  SRC.EmpName ,

                  SRC.Dept ,

                  Getdate() ,

                  '2099-12-31' ,

                  'True'

                )

                    WHEN MATCHED AND DEST.IsActive = 'True'

                        AND ( DEST.Dept <> SRC.Dept )

                        THEN

               UPDATE   SET

                DEST.IsActive = 'False' ,

                DEST.EndDate =Getdate()-1

                    OUTPUT

                        $Action Action_Out ,

                        SRC.Id ,

                        SRC.EmpName ,

                        SRC.Dept ,

                        Getdate() StartDate,

                        '12/31/2199' EndDate ,

                        'True' Isactive

) as SCDImplementation

where SCDImplementation.Action_Out='UPDATE';

Output:

How Does it work?

Here MERGE statement’s output option captured all the changes it makes and tags them in an output table.

This feature captures all Rows from Source table based on Marching ID in both tables.

• If Id matched both the table and IsActive column is ‘True’ but other columns value having mismatch then, it will come under ‘Update’ Action Output.

• If Id is not match in both tables, then the entire record will come under ‘Insert’ Action Output.

Let’s run Only MERGE statement:

MERGE SCD_Emp DEST

                    USING Table_Dump SRC

                    ON ( DEST.Id = SRC.Id )

                    WHEN NOT MATCHED

                        THEN

              INSERT  VALUES

                ( SRC.Id ,

                  SRC.EmpName ,

                  SRC.Dept ,

                  Getdate() ,

                  '2099-12-31' ,

                  'True'

                )

                    WHEN MATCHED AND DEST.IsActive = 'True'

                        AND ( DEST.Dept <> SRC.Dept )

                        THEN

               UPDATE   SET

                DEST.IsActive = 'False' ,

                DEST.EndDate =Getdate()-1

                    OUTPUT

                        $Action Action_Out ,

                        SRC.Id ,

                        SRC.EmpName ,

                        SRC.Dept ,

                        Getdate() StartDate,

                        '12/31/2199' EndDate ,

                        'True' Isactive;

Note: To run MERGE statement we have to give semi colon ‘;’ at the end of statement.

Output:

Now we have to insert those row, which only comes under ‘Update’ Action Output from MERGE statement into Dimension table (SCD_Emp).

Insert into [dbo].[SCD_Emp]

Select SCDImplementation.Id,

SCDImplementation.EmpName,

SCDImplementation.Dept,

SCDImplementation.Startdate,

SCDImplementation.EndDate,

SCDImplementation.Isactive

from (/***MARGE Statement***/) as SCDImplementation

where SCDImplementation.Action_Out='UPDATE'

We should not Insert row which comes under ‘Insert’ Action Output because we already written Insert query for that in 1^st step of marge statement.

MERGE SCD_Emp DEST

                    USING Table_Dump SRC

                    ON ( DEST.Id = SRC.Id )

                    WHEN NOT MATCHED

                        THEN

              INSERT  VALUES

                ( SRC.Id ,

                  SRC.EmpName ,

                  SRC.Dept ,

                  Getdate() ,

                  '2099-12-31' ,

                  'True'

                )

Row Level Security (RLS) on SSMS 2016

Today we are going to apply RLS security in SSMS 2016. Lower version of SSMS would not support RLS security.

To implement RLS we have under gone 4 steps given below.

Step 1: Schema Creation

We have to Create a separate Schema on database for this implementation.

Step 2: Table Creation

We have to create table, on which we are going to create RLS. Here we have created a table named 'ManagerHierarchy'. Table structure given below.

Step 3: Create predicate function:

We have to create a predicate function inside the schema.

IF EXISTS (SELECT * FROM sys.objects WHERE  object_id = OBJECT_ID

(N'[Security].fn_SecurityPredicateManagerHierarchy')

AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [Security].fn_SecurityPredicateManagerHierarchy

GO

CREATE FUNCTION [Security].fn_SecurityPredicateManagerHierarchy (

@EmpName AS SYSNAME,@Level1 AS SYSNAME,

@Level2 AS SYSNAME,@Level3 AS SYSNAME,@Level4 AS SYSNAME

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS SecurityPredicateManagerHierarchy_Result

FROM [dbo].[ManagerHierarchy]

WHERE @EmpName       =USER_NAME()

OR @Level1           = USER_NAME()

OR @Level2           = USER_NAME()

OR @Level3           = USER_NAME()

OR @Level4           = USER_NAME()

Step 4: Create security policy:

This is the last step to create RLS. Here we have to create a policy for RLS. This is new addition in SSMS 2016. Here we apply filter on ManagerHierarchy table. To activate the RLS we have to make 'STATE' option 'ON'.

Along with FILTER, we can restrict Insert or update data by unauthorized user through BLOCK PREDICATE. (It is required on OLTP Database)

CREATE SECURITY POLICY [Security].ManagerHierarchyFilter

ADD FILTER PREDICATE

[Security].fn_SecurityPredicateManagerHierarchy(EmpName,Level1,Level2,Level3,Level4)

ON [dbo].[ManagerHierarchy] WITH (STATE = ON)

GO

Now we have to test the RLS.

Create Sample Users without login and Grant Permission to them for this table

CREATE USER Subhadip WITHOUT LOGIN;

GO

CREATE USER Pritam WITHOUT LOGIN;

go

CREATE USER tanmoy WITHOUT LOGIN;

go

CREATE USER Sourav WITHOUT LOGIN;

GO

CREATE USER Titas WITHOUT LOGIN;

go

CREATE USER Anurag WITHOUT LOGIN;

go

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Subhadip;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Pritam;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO tanmoy;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Sourav;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Titas;

GO

GRANT SELECT ON [dbo].[ManagerHierarchy] TO Anurag;

GO

Test Cases:

EXECUTE AS USER = 'Subhadip';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'Pritam';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'tanmoy';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'Sourav';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'Anurag';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO

EXECUTE AS USER = 'tanmoy';

SELECT * FROM [dbo].[ManagerHierarchy];

REVERT;

GO