Mitigation of Known Fabric Issue which results in loss of permissions after Schema Change

[kdbuser]

Hi All,

Is there a way to take down the sql endpoint of a lakehouse to mitigate the risk of this issue
We cant have a scenario where permissions are lost and users can access any row in certain tables even for a short period of time when doing maintenance on weekends

If this is not possible are there any other solutions that people have to mirigate this risk?
Known issue - SQL analytics endpoint tables lose permissions - Microsoft Fabric | Microsoft Learn

[kdbuser]

Hi Xiaoxin,

My only query there is if we expose a table with no row level permissions to a SQL endpoint a user can query the entire table. Are you saying in this instance if permissions are dropped, the expected behaviour would now be different?

[v-shex-msft]

HI @kdbuser,

In fact, there are a lot of level of security settings that existed.
Currently the known issue is occurred on the model level, so it should firstly affect the semantic model and endpoint accessibility.
For the detailed 'granular security' level (e.g. row, column, object, etc...), they also not works due to currently users currently can't access the model.
Notice: these 'granular security' only applied on read permission users, for the workspace admin and fabric item owner, they still can access to these contents.

In short, please not assign high-level of permissions to users. You can give them access and read permission, and use T-SQL to accurately control and manage the range of contents user can viewed.

Security for data warehousing - Microsoft Fabric | Microsoft Learn

Regards,

Xiaoxin Sheng

[kdbuser]

Hi Xiaoxin,

The scenario Im concerned about is when users connect via Power BI to the sql endpoint. In the scenario above, if a schema change is applied and the row level security is lost on the SQL tables. The issue is there would be a period where permissions are dropped and we would need to rerun T-SQL to apply permissions as you suggested.

Please correct me if Im wrong but if the security policy is dropped as the bug suggests that it would be
https://learn.microsoft.com/en-us/fabric/get-started/known-issues/known-issue-909-sql-analytics-endp...
Anyone connecting via the SQL endpoint would be able to access all the data?
Currently we have endpoints with no security policy on the SQL tables and users can read all rows.

However now that we are looking to apply a security policy to the SQL tables we want to ensure that no one can access the data via Power BI when permissions are lost. That is why Im wondering if the SQL endpoint can be taken offline?

[v-shex-msft · Accepted solution]

HI @kdbuser,

In fact, I mention they are lots of level security existed.
When you tried to apply the detailed 'granular security' level (e.g. row, column, object, etc...) security, you need to have the access permissions at first.
The known issue has appeared on the access level, so the users should firstly lose the accessibility of the SQL endpoint. Since users already can't view records from the endpoint, so you do not need consider the detail level security.

Notice: processed from left to right
Data access(X) -> Read -> Row/Column level security -> Limited records
                        -> Read/Write -> All Records.

If you still confused about these, you can also submit an ticket to contact to the Dev team to get further support about this known issue.

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @kdbuser,

I think it means current permissions has been dropped and not available, and users not able to access the endpoint until the admin re-assign the permissions. 

For lakehouse sharing and security, you can check the following document:

Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn
Regards,

Xiaoxin Sheng