Fine-grained ReadWrite access to data with OneLake security – Row Level Security

[suparnababu8]

Use Case Overview

We will use an India Education sample dataset that captures state‑wise education dropout ratios. The goal is to ensure that:

• State‑level officers can upload and manage data only for their respective state
• They can view only their state’s records in the consolidated table
• The central data engineering team can manage and process all data securely

Let’s dive in.

Workspace and Lakehouse Setup

• Workspace Name: OneLakeSecurity-WS
• Lakehouse Name: ind_edu_LH (Lakehouse schema enabled)

Data Structure

• A state‑wise folder structure where education officers upload yearly CSV files
• Example: AndhraPradesh/2024-25.csv
• A cumulative processed table created by the data engineering team under the Tables section:
• ind-edu-dropout-ratio

Step 1: Add the User to the Workspace

Now let’s add Andhra Pradesh Education Manager (SI Babu) to this workspace as Viewer role.

Step 2: Share the Lakehouse

Now let’s share this Lakehouse with him. Click on Share the enter his email id, don’t select any options under additional permissions and click on Grant. This step provides basic Lakehouse visibility without exposing data

Step 3: Enable OneLake Security (Preview)

Click on Manage One Lake security (preview) and click on Continue. Now we can start defining fine‑grained access rules.

Step 4: Create a Read‑Write Role for State Folder Access

Now click on create role

Now give meaningful name(ReadWrite) to role then select Grant option->Check the ReadWrite option->selected data and click on Browse Lakehouse

Now select Andhra Pradesh folder and click on Add data

Now Andhra Pradesh Folder is added and click on create role

Now Role successfully created and this allows the user to upload and modify files only within the Andhra Pradesh folder.

Step 5: Create a Read‑Only Role for Table Access

Click on New for adding another security role

Now give a meaningful(Read) name to role and select Grant option->Read will be selected by default->Select Data->Click on Browse Lakehouse

Now select ind-edu-dropout-ratio delta table and click on Add data

Now Table is added and click on Create role. This grants read‑only access to the consolidated table.

Step 6: Configure Row‑Level Security (RLS)

Click on ellipsis (⋯)and choose permissions-->Row Security (preview)

Now Write a SQL filter to restrict data to Andhra Pradesh only and click on Save

Row-Level-Security(RLS) is now applied successfully

Step 7: Enable Delegated Identity

Switch to the SQL Analytics Endpoint and click on security à Delegated identity à Select to User’s Identity from  Delegated identity

Now select Yes, use the User’s identity. This ensures that queries run using the logged‑in user’s identity.

Step 8: Validate Row‑Level Security

Now I log in with SI babu credentials (Andhra Pradesh Education officer)

Now SI babu able to see only Andhra Pradesh Rows. Even though the table contains data for all states, RLS ensures restricted visibility.

Step 9: Validate OneLake Folder Access

Now I opened SI babu One Lake explore (login with SI babu credentials) in my local machine.

Uploaded Andhra Pradesh 2024-25 csv file

Now Navigate to SI Babu account and opened the Andhra Pradesh folder, I can able to see recently uploaded file in lakehouse. The uploaded file is visible Other state folders are not accessible

Also I can able to see that file in my parent account

Conclusion

This demonstrates how OneLake Security can be effectively used to:

• Enforce Read and Read‑Write permissions at the folder and table level
• Implement Row‑Level Security for fine‑grained data access
• Secure enterprise‑scale Lakehouse architectures in Microsoft Fabric

By combining OneLake Security with RLS and Delegated Identity, organizations can confidently enable self‑service data access without compromising governance.

I hope you found this blog useful. If you have any questions or would like to discuss Microsoft Fabric security in more detail, feel free to connect with me on LinkedIn.

— Inturi Suparna Babu

[LinkedIn]

[Srividya18]

Thank you suparna babu..its very helpful👌

[krishnakanth240]

Thanks for sharing @suparnababu8 

Step by step guide is very helpful to implement 

[NagaSuresh]

Thanks for Sharing @suparnababu8 

[Jan_DE]

Does this pattern prevent the State‑level officer from Andhra Pradesh to upload csv data for Assam (csv with State_UT=Assam) into the folder of Andhra Pradesh ?

[Olayemi_Awofe]

This is so helpful, thank you for sharing

[suparnababu8]

Hi @Jan_DE - That pattern will wortk only for viewing data from Mastre Data table not for file uploading. I'm wondering Why State‑level officer from Andhra Pradesh will upload Assam state data. It doesn't make any sense. Those all offciers all from respective states. We can apply SQL RLS on table not on Files.

Hope you are clear!!

[Jan_DE]

@suparnababu8 : 

That pattern will wortk only for viewing data from Mastre Data table not for file uploading.

So it is only a pattern for read access, not a pattern for write access, correct ?

Why State‑level officer from Andhra Pradesh will upload Assam state data. 

Security patterns should prevent undesired actions, instead of asking if someone would do it.

PS: I understand the "State‑level officer" as a Sample for an accessing user role in this security pattern.

[suparnababu8]

Hi @Jan_DE 

In this blog I mentioned write access from Files section only not for Table. Those state level officers are no-tech persons, they don't need write access to Table. If you like share write access in the develoepr point of view to table, it's possible, you need to grant ReadWrite access. PFA below screenshot.

Please check this section from this blog

""Now Table is added and click on Create role. This grants read‑only access to the consolidated table."" 

In this sue case I granted read access only. But, use case different you can grant ReadWrite access.

Hope you're clear now.