RLS

[Leif]

Hi,

I have created a function that I have applied as a row filter function to multiple tables.

The function takes one input parameter (a column value from the table). It then uses session_user() to look up a user in our users table. If the user is found, we look at the users access rights which is specified in the users table. That value is compared to the input parameter to return true or false if the user can see or not see this row.

This does not work now, but I am certain that I had it working before. Why does it not work? This is the function:

EXISTS (
   SELECT 1
   FROM {table} AS users
   WHERE user_name = session_user()
      AND (
         users.{id col} = {param}
         OR users.{id col} IS NULL
         OR users.{id col} = ''
      )
)

thanks in adavance!

[emma_s]

Hi, It's difficult for me to troubleshoot without seeing the actual data. Have you validated that the raw data hasn't changed? There could be some update at your end to the format of session.user and it not matching your table. Is there any error message? If I understand correctly what you're trying to do, then a better way maybe using ABAC and adding users to groups rather than the tabular approach.https://docs.databricks.com/aws/en/data-governance/unity-catalog/abac/

[Leif]

Hi,

Thanks for the reply! Yeah, no the raw data has not changed at all. My thought is, can the row filter function handle reading from another table than the raw data table? I mean, I do a read in the users table to find a value, and want to compare that value to the value from a column in the raw data table. Is that supported? I konw I had this working before so perhaps Databricks changed some rule or something?
I will take a look at that ABAC you suggested. That is setting up policies, right? I tried that, and created a new Tag on a column in my table, but that Tag was then not available to select in the policy, so there was no way I could run the policy to get the parameter for my function that way.

[emma_s]

It should be still supported, the only change I can find is that the table that you're referencing can't have a row filter applied to it. Has the users table got any type of row filter on it? It also can't create any type of circular reference.

[Leif]

Hi! Sorry for the late reply, lots of holidays 🙂
No, the user table does not have a row filter in it. The structure is like this:

• schema1.users: contains a list of users obviously, and it has a column with a customer id for the customer they are allowed to see data from (a user can have multiple entries in this table if they have access to multiple customers).
• schema2.{various_tables}: all of these tables have a column with the customer id in it
• schema1 has a function (permissions): this function looks like below. The idea is that when a user queries the database, either directly in databricks sql, a dashboard, a dash app etc, should ONLY see data from their assigned customer(s), or if the customer id field is empty, they should see all data. I am sure that is already clear 🙂
• Every table with that customer id column has a row filter defined, using the below function.

Problem: the function below does NOT work. If I hardcode the customerid, the function works fine.

EXISTS (
    SELECT 1
    FROM catalog.schema1.users AS users
    WHERE user_name = session_user()
      AND (
        users.customer_id = customer_id
        OR users.customer_id IS NULL
        OR users.customer_id = ''
      )
  )