Row-level security (RLS) within Indexima & PowerBI Gateway

As of now, PowerBI Gateway doesn't allow sending the current user to the database (i.e. Indexima in this context). This means, without any further actions, only the user filled in the Gateway configuration panel would actually execute queries.

Using RLS implemented by PowerBI (link), Indexima developed a workaround that allows to get the current user and then apply actual RLS within indexima.

Implementing a workaround for impersonation within the Indexima engine

Activate the Configuration

In galactica.conf configuration file, Add the parameter powerbi.impersonate.field = idx_powerbi_impersonate

idx_powerbi_impersonate can be any field name provided it doesn't already exist as an actual field of any table.

Add a virtual field in a table

Add the field in any table you need to apply RLS with the command:

SQL

ALTER TABLE mytable ADD COLUMNS (idx_powerbi_impersonate as 'X');

Go to the ADD columns for more information
This command can be replaced by directly adding the definition of this column in the create table statement.

Check the usage of the virtual field

SQL

SELECT * FROM mytable WHERE idx_powerbi_impersonate ='test';

As a result, you should see that user "test" sent the query and thus becomes the current user, instead of the real user that sent the query.

Note: Use simple quotes, double quotes won't work out.

Use a workaround for impersonation in PowerBI

Within PowerBI, you will use the RLS implemented by PowerBI (link) as follow.

Create a measure (Optional)

Create a measure that contains the UserName. This Measure will allow you to see, mostly for debug process the current user.

CODE

UPN = USERPRINCIPALNAME()

Case 1: USERPRINCIPALNAME (i.e Emails) have rights on the fact table

Remember the filter would be like (JOIN User_Rights_Table on fact_table.entity=User_Rights_Table.entity AND Allowed_User=current_user)
With the usage of the field, this will result in the fact that current_user would execute (instead of the Gateway user). If you apply user rights on tables (and schemas), the user current_user needs to have access to the table.

Create a Role in PowerBI

Create a Role on the fact table and use USERPRINCIPALNAME as a filter.

CODE

[idx_powerbi_impersonate] = USERPRINCIPALNAME()

Case 2: USERPRINCIPALNAME (i.e Emails) does not have any rights on the fact table, but the related "tech" user does

Remember the filter would be like (JOIN User_Rights_Table on fact_table.entity=User_Rights_Table.entity AND Allowed_User=current_user).

Create a "Conversion" table in Indexima

Create a table that will convert Emails into "tech" users

SQL

create dimension TABLE Conv_Email_UserTech(email STRING, tech_user STRING);
INSERT INTO TABLE Conv_Email_UserTech VALUES ("john.doe@indexima.com","jdoe");

Include the "Conversion" table in PowerBI

Include the "Conversion" table in PowerBI. There is no join between the "conversion" table and the fact table to add in this model.

Create a Role in PowerBI

Create a Role on the fact table and use a DAX formula that will convert the USERPRINCIPALNAME into the "tech" user as a filter.

CODE

[idx_powerbi_impersonate] = MAXX(filter(conv_email_usertech,conv_email_usertech[email]=USERPRINCIPALNAME()),conv_email_usertech[tech_user])

Validate the roles within Power BI Desktop

Follow the PowerBI documentation here

Using RLS with workspaces in Power BI

The RLS roles are applied to members who are assigned to the Viewer role in the workspace.

Workspace members assigned Admin, Member, or Contributor have edit permission for the dataset and, therefore, RLS doesn’t apply to them.