Row Level Security Breaks Copy Data Overwrite

[ContractRick]

I have a lakehouse that I pull tables into via a Pipeline Copy Data activity. The source is a local SQL server and the destination is a table in the lakehouse set to overwrite. This works great until I apply a Security Policy to the table... Now, despite the pipeline finishing without issue, when I query the table, it still shows the old data and does not appear to have been updated/overwritten.

Is there a different way I should be updating the table to keep the RLS? The RLS appears to work as expected with the current data, but not being able to update the table is obviously a deal breaker...

[frithjof_v]

@ContractRick Where (and how) do you apply the security policy? And where (and how) are you querying the table?

(I.e. are you applying the security policy to the SQL Analytics Endpoint, and querying the table in the SQL Analytics Endpoint?)

At first glance, it sounds to me that you have discovered an issue. 

Sounds like you have done a good job at finding a workaround as well!

However I think this sounds like an issue which should be fixed by the product team. Or at least provide insights into why this behaviour is happening. 

[ContractRick]

The security policy is applied on the SQL Endpoint of the lakehouse. I followed Microsoft's instructions to configure it. I created a new schema, a function that references USER_NAME() against a select statement from a different table (without RLS), and the security policy itself.

[rampie]

HI @ContractRick 

Some update of this issue? i'm implementing RLS in the SQL endpoint of my lakehouse, and still the Security Policy is blocking the table refresh, the data updates via pipeline, if add an step that turns off the policy the table can be refreshed, but it's an unnacesary extra step. Have you found a solution?

[ContractRick]

We gave up on table based RLS. The direct query limitations were too much for us, so we switched to forcing the semantic models to use Direct Lake only, used a fixed identity for the Lakehouse connection, and applied RLS the old fashioned way on the semantic model.

[frithjof_v]

Is this the issue you are encountering?

https://learn.microsoft.com/en-us/fabric/get-started/known-issues/known-issue-767-sql-endpoint-table...

[ContractRick]

Yes, and the "workaround" is what I have already described in one of my own replies on this topic. We gave up on this form of RLS due to the security implications of said workaround as well as the limitations of direct query mode. Forcing Direct Lake mode, using a fixed identity, and applying RLS on the semantic model works well for us.

[ContractRick]

Through further testing I have discovered that as soon as I drop the security policy, a query of the table shows the latest data that was supposed to have been copied to it. It seems the security policy is somehow blocking the update... I guess I could try running a notebook after the copy data activity to drop and readd the security policy... Seems like a weird workaround though...

[Anonymous]

Hi @ContractRick ,

It really is a great way to solve a problem, and all in all I'm glad to hear you've resolved it.

Best Regards,

Ada Wang

[ContractRick]

Well, it's not really resolved... I have been bashing my head against the wall for the last 2 days on this. I finally found a method that works, but it's not pretty. Microsoft really needs to fix this!

Since Spark SQL can't do anything with SECURITY POLICY nor can it EXEC stored procedures (at least that I am aware of), I had to resort to the following:

1. Create a connection to the SQL endpoint of the lakehouse using the cloud URL and Organizational credentials
2. In a pipeline string together 3 activities after the copy data is successful:
   1. Stored procedure that drops the security policy
   2. Wait 60 seconds
   3. Stored procedure that creates the security policy

The stored procedures use parameters for the database name, function, and security policy name, so that they can be used for any tables that need RLS. I tried setting the wait time to be shorter, but found that 1 minute was reliable (I had to do the same thing when dealing with lakehouse tables updated by data flows). I also tried doing it all as 1 stored procedure, but could never get it to work.

So for now, this pipeline appears to get the job done, but it does mean that for 1 minute after the copy, users could potentially bypass the RLS...