Forum Discussion
Row Level Security Breaks Copy Data Overwrite
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...
9 Replies
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...
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
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:
- Create a connection to the SQL endpoint of the lakehouse using the cloud URL and Organizational credentials
- In a pipeline string together 3 activities after the copy data is successful:
- Stored procedure that drops the security policy
- Wait 60 seconds
- 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...
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.
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.
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?
