Pseudo Row Level Security (RLS) for Publish to Web Power BI Reports

This post is an update for

Simple authorization based on a slicer and DAX measures

What is new?

One of the users has found a critical issue in the old version of the report: the Text Filter visual allows right-clicking “Show as a table,” and then you can see the list of all passwords. I’ve tried a workaround, but it failed.

Then I recalled that the Deneb custom visual and Vega visualization grammar support the HTML input elements (such as “text” and “password”) and cross-filtering. This new post describes a new approach based on the Deneb custom visual.

---

New version of the report, shared using “Publish to web” feature:

---

Data model

_DataSrc – data table that contains dummy data (2023 Jan – 2023 Jun sales for 10 salespersons)

Data – fact table, same as _DataSrc, excluding columns that have been moved to the dimension Month Year table

Month Year – dimension table, year and month

User – dimension table, list of usernames and their passwords

‘User'[User Name] and ‘User'[Password] columns added to the Deneb visual:

The Deneb visual works as a slicer and is intended to filter the User table down to a single row. Therefore, the Data table will be filtered to show only data that belongs to the authorized user.

Deneb visual

The Vega code filters the “dataset” by username and password entered via HTML “text” and “password” input elements bound with Vega signals. Until the correct username and password are entered, the “dataset” is empty. Once the correct credentials are entered, there is a single record in the “dataset.”

Once there is a record in the “dataset,” Vega uses “rect” and “text” marks to render the “Submit” button. Clicking on the button cross-filters other visuals on the page by the username in the “dataset.”

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "description": "Custom slicer / password protection. Filters data table by username and password entered via HTML input field. Cross-filters over visuals when Submit clicked.",
  "usermeta": {
    "Copyright": "Andrzej Leszkiewicz",
    "License": "MIT Expat License (https://en.wikipedia.org/wiki/MIT_License)"
  },
  "width": 120,
  "height": 30,
  "padding": 5,
  "background": "#e6e6e6",
  "config": {},
  "signals": [
// this signal creates Username input field
    {
      "name": "Username",
      "description": "creates HTML test input field",
      "value": "",
      "bind": {"input": "text"}
    },
// this signal creates Password input field
    {
      "name": "Password",
      "description": "creates HTML password input field",
      "value": "",
      "bind": {"input": "password"}
    }
  ],
  "data": [
// "dataset" is filtered by Username and Password. If both correct, there will be one row in the dataset. Otherwise - none.
    {
      "name": "dataset",
      "transform": [
        {
          "type": "filter",
          "expr": "datum['User Name']==Username && datum['Password']==Password"
        }
      ]
    }
  ],
  "marks": [
    {
      "comment": "Creates Submit button. Visible only when there is data in the dataset (correct password entered). Click cross-filters other visuals.",
      "name": "rect-submit",
      "type": "rect",
      "from": {"data": "dataset"},
      "encode": {
        "update": {
          "x": {"value": 0},
          "y": {"value": 5},
          "width": {"value": 80},
          "height": {"value": 30},
          "fill": {"value": "#FFFF99"}
        }
      }
    },

    {
"comment": "Creates 'Enter username and password' label. Invisible when there is a single row in the dataset.",
      "name": "text-enter",
      "type": "text",
      "encode": {
        "update": {
          "text": {
            "signal": "length(data('dataset')) == 1 ? '' : 'Enter username and password'"
          },
          "x": {"value": 5},
          "y": {"value": 20},
          "fontSize": {"value": 14},
          "baseline": {
            "value": "middle"
          },
          "align": {"value": "left"}
        }
      }
    },
    {
      "comment": "'Submit' label for the button. Visible only when there is data in the dataset (correct password entered). Click cross-filters other visuals.",
      "name": "text-submit",
      "type": "text",
      "from": {"data": "dataset"},
      "encode": {
        "update": {
          "text": {
            "signal": "'Submit'"
          },
          "x": {"value": 5},
          "y": {"value": 20},
          "fontSize": {"value": 20},
          "baseline": {
            "value": "middle"
          },
          "align": {"value": "left"}
        }
      }
    }
  ]
}

Challenge

There is sales data for Liam Davis in the report. Can you “hack” the Publish to Web version and extract the data? Liam Davis sales data and password in the online version has been changed. It’s not the same one, that you can find in the .PBIX file.

*** Warning! ***

This is not real Row Level Security (RLS). This is pseudo-RLS or security through obscurity.

The report has access to the data of all users. The pseudo-RLS filters data within the report page, whereas real RLS filters data before it gets into the report. It can’t be 100% secure. There is or may be a way to access the data without knowing the username and password. The pseudo-RLS likely won’t stop an expert, but it may protect your data from a curious user.

I take no responsibility for your losses, and I do not recommend using the ‘Publish to web’ feature for sharing reports that contain sensitive and confidential information. However, if all parties involved understand the pros and cons and accept the risks, then you are free to do whatever you want if it’s technically possible.

Use this method only if all stakeholders understand and accept all risks involved.

I highly recommend using secure embedding (https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-embed-secure) and real RLS (https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls) if you’re not ready to accept the risks.