In this blog post, I will show you how to configure OneLake security for a Microsoft Fabric Lakehouse. We will remove the default broad-access role, grant the required Lakehouse access, configure object-, row-, and column-level security, and test the results. The goal is to create a secure-by-default setup in which users can access only the data they need.

Prerequisites

  • You must be a workspace Admin or Member, or have Write and Reshare permissions on the item.
  • The Lakehouse should be schema-enabled, which is required for full OneLake security support.
  • Plan the security model before you begin.
    • Decide whether access will be managed at the table, folder, schema, row, or column level, or through a combination of these options.
  • Create the required Microsoft Entra ID groups before completing the OneLake security configuration.
    • NOTE: You can add supported Microsoft Entra ID group types as members of OneLake security roles.

Remove the DefaultReader role

When implementing OneLake security, I recommend removing the DefaultReader role from the Lakehouse permissions first.

Users who have a workspace role may also receive access through the DefaultReader role, which includes ReadAll permission by default.

If you leave this role in place, your custom roles may appear ineffective because users can still receive broad access through DefaultReader.

  • In the workspace, select the ellipsis (…) next to the Lakehouse, and then select Manage permissions.
  • Follow these steps to remove the DefaultReader role:
    • 1. Select OneLake security.
    • 2. Select the DefaultReader role.
    • 3. Select Delete.
  • When prompted, select Delete again to confirm.

Grant Read permission to the Lakehouse

Next, grant Read permission on the Lakehouse to the appropriate Microsoft Entra ID groups. This item-level permission allows users to connect, while the OneLake roles determine which data they can access.

NOTE: If you skip this step, users may be unable to connect to the Lakehouse.

  • In the workspace, select the ellipsis (…) next to the Lakehouse, and then select Manage permissions.
  • Make sure Direct access is selected.
  • Select Add user.
  • In the Grant people access dialog, add the required Microsoft Entra ID groups.
    • NOTE: Do not select any additional permissions.
    • Select Grant.
  • Confirm that the groups have Read permission, as shown below.

Configure the SQL analytics endpoint to use OneLake security

This setting is required for the SQL analytics endpoint, including applicable Power BI Direct Lake scenarios, to respect OneLake security roles.

Until you change the data access mode, the endpoint can continue using its existing identity behavior rather than enforcing the OneLake roles for table access.

NOTE: In the scenarios demonstrated in this post, connect through the SQL analytics endpoint so that the configured OneLake security rules are enforced.

  • You can also use capabilities such as views and dynamic data masking through the Lakehouse SQL analytics endpoint.
  1. In the workspace, open the SQL analytics endpoint associated with the Lakehouse.
  2. Open the Security tab.
    1. Select Security.
    2. Select View data access mode.
    3. Select Data access mode settings.
  4. In the Data access mode dialog, select “Use OneLake security for tables,” as shown below.
    2. Select Apply.
  5. A warning is displayed before the setting is changed. Where possible, configure this option when you create the Lakehouse to reduce the risk of disrupting existing connections.
    2. Select Continue.

Configure object-level security

In this example, we will create a role that can read only the table named “publicholidays.”

  • Open the Lakehouse, and then select Manage OneLake security.
  • When the OneLake security window opens, select Create role.
  • Enter the role name “RolePublicHolidays.”
    • Leave the role with Read permission only.
    • Select Next.
  • Under Selected data, select Edit to choose the data that members of the role can read.
  • In the Edit data for the role dialog, expand the hierarchy and select only the required table.
    • Select Add data.
  • Review the data selection shown for the role.
    • Select Next.
  • On the final screen, add the members who should belong to the role.
    • NOTE: You can add members later if required.
    • Select Create to create the role.

Configure row-level security

Next, create a role and configure row-level security (RLS).

In this example, we will use the same table and limit members to rows where Country = “Australia.”

  • Create the role by following the object-level security steps above.
    • For this example, the role is named “RlsAustralia.”
  • To configure row-level security, follow these steps:
    • 1. Select the role, and then select the ellipsis (…).
    • 2. Select Permissions.
    • 3. Select Row security.
  • Select the schema and table to which the row-level security rule should apply.
  • Under “Show data to members of your role if the following rules apply,” enter the row filter as a SQL expression, as shown below.
    • Select Save.
    • TIP: Test the filter in the SQL analytics endpoint first to confirm that it returns the expected rows.
  • A confirmation message indicates that the rule has been applied.
  • After the rule is saved, the role displays a Row security indicator under Data access.

Configure column-level security

Next, create a role and configure column-level security (CLS).

In this example, we will use the same table and allow members to access only three columns.

  • Create the role by following the object-level security steps above.
    • Use a clear role name that reflects the intended access. In this example, the existing “RlsAustralia” role is used.
  • To configure column-level security, follow these steps:
    • 1. Select the role, and then select the ellipsis (…).
    • 2. Select Permissions.
    • 3. Select Column security.
  • Select the schema and table to which the column-level security rule should apply.
  • Select New rule or Enable CLS.
  • Select the columns that members of the role should not be able to access. In this example, the selected columns are removed from the role’s available column set.
    • Select Remove.
  • Review the result and confirm that only the three intended columns remain available.
    • Select Save.
  • A confirmation message indicates that the rule has been applied.
  • After the rule is saved, the role displays a Column security indicator under Data access.

Test OneLake security

Test each security layer with a user who belongs only to the intended Microsoft Entra ID group and OneLake role.

NOTE: Use the same access path that your consumers will use. For the SQL scenarios in this post, connect through the SQL analytics endpoint.

Test object-level security

First, sign in as a user who is a member of the object-level security role and verify which objects are visible.

  • In this example, I signed in as the test user and browsed the Lakehouse while creating a shortcut.
  • As shown below, the user can see only the single permitted table.

Test row-level security

Next, sign in as a user who belongs to the “RlsAustralia” role.

  • When the user connects to the SQL analytics endpoint, the query returns only rows for Australia, as shown below.

Test column-level security

Finally, test the column-level security configuration.

NOTE: When column-level security prevents access to one or more columns, a query such as SELECT * FROM TableName can fail because it requests every column. Specify only the columns that the role is allowed to access.

  • Connect to the SQL analytics endpoint.
  • Select New Query.
  • Enter a query that explicitly lists the columns available to the user. The query should return only the permitted information, as shown below.

Summary

Thanks for reading.

The key principle is simple: provide the minimum access required at the Lakehouse level and then use OneLake roles to narrow access to the appropriate objects, rows, and columns.

I hope this gave you some insights how to configure and use OneLake Security.

Any comments or suggestions are always welcome.