Column level security with SQL analytics endpoint

[yongshao]

We have a Fabric lakehouse to host data in delta tables, we want to add column level security to its SQL endpoint.

not working when executing grant query in its SQL query window "GRANT SELECT ON ..."  not supported

not working when excuting grant query by notebook spark.sql(grant_query)  not supported

https://learn.microsoft.com/en-us/fabric/data-warehouse/column-level-security mentioned SQL analytics endpoint supports column level security feature

appreciated for any help

[v-csrikanth]

Hi @yongshao 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @yongshao 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @yongshao 
Sorry for the late response.
After Granting the access to the columns ask the user to query which was not granted the access for that column.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @yongshao 
Implementing CLS in Microsoft Fabric's Lakehouse via the SQL analytics endpoint involves a series of steps to ensure that users have access only to the specific columns they're permitted to view.
Here's a detailed, step-by-step guide to help you set up CLS effectively:

• Open your Lakehouse in Microsoft Fabric and switch to the SQL analytics endpoint.

• Optionally, create a role using CREATE ROLE [RoleName]; and add users with ALTER ROLE [RoleName] ADD MEMBER [UserEmail];

• Grant access to specific columns using GRANT SELECT ([Column1], [Column2]) ON dbo.YourTable TO [RoleName];

• Test with a user account to confirm only permitted columns are visible using a simple SELECT query.

• Reapply permissions after syncing tables, as security settings may be reset.

• Ensure users are authenticated through Microsoft Entra ID for CLS to be enforced.

• Be aware that Power BI may switch to Direct Query mode to honor CLS settings.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[yongshao]

@v-csrikanth Thanks for your response - it's helpful

• Test with a user account to confirm only permitted columns are visible using a simple SELECT query.

How to test? grant the user to access the lakehouse first? otherwise, how the user connects the sql endpoint and test?

if granting the user as lakehouse viewer, such CLS doesn't take any effect - tested

[yongshao]

@Srisakthi  

can you confirm if CLS feature is supported with Fabric Lakehouse delta table data via its SQL analytical endpoint?

I know CLS and dynamic data masking are supported in Fabric Warehouse, but confused with Fabric lakehouse SQL endpoint (Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric - https://learn.microsoft.com/en-us/fabric/data-warehouse/column-level-security)

[Srisakthi]

Can you please paste the error message that you are getting. Also try with the below command. 

Column-level security only applies to queries on a Warehouse or SQL analytics endpoint in Fabric. 

Implement column-level security with the GRANT T-SQL statement. For simplicity of management, assigning permissions to roles is preferred to using individuals. 

GRANT SELECT ([EmployeeName],[EmailId]) on dbo.[DimEmployees] TO "[email protected]"

Regards,

Srisakthi

[yongshao]

Error message "com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'GRANT'"

query like 

GRANT SELECT (id,tenant_id) on dbo.person TO '[email protected]'

select query works fine -- select * from dbo.person