Prepstellar
Get the app

DP-600 · Analytics Security and Governance

19 cards

Granular Data Access Controls

Swipe, scroll or use ← →

  1. Know where the granular check happens

    Fabric evaluates access in three stages, in order: Microsoft Entra ID authentication, then Fabric access, then data security. The first two establish who you are and that you may use Fabric at all; only the third looks at the data itself.

    At the data-security stage, Fabric checks whether a user can perform the requested action on a table. The same stage checks whether a user can perform the requested action on a file.

    Within a lakehouse, the correct granular mechanism depends on the path used to reach the data.

    1 / 19

  2. Quick check

    Fabric has already authenticated a user and confirmed Fabric access. What does the data-security stage decide?

    1. AWhich workspace the user belongs to

      Workspace membership is settled earlier and says nothing about a specific table or file.

    2. BWhether the user's Microsoft Entra ID sign-in has succeeded and can be trusted

      Sign-in is the first stage, Microsoft Entra ID authentication, and it has already happened by this point.

    3. CWhether the user can perform the requested action on a table or file

      Right. This stage checks whether the requested action is allowed on the table or file in question.

    2 / 19

  3. Pick the route by how far it must reach

    Two granular routes exist, and they differ in which engines respect them.

    Route Where it applies
    T-SQL permissions Compute permissions for the SQL analytics endpoint
    OneLake security roles Consistently across Spark, SQL, and OneLake APIs

    T-SQL permissions are compute permissions for the SQL analytics endpoint. Use Data Control Language commands such as GRANT, DENY, and REVOKE when users query lakehouse data with T-SQL. OneLake security roles apply consistently across Spark, SQL, and OneLake APIs.

    This distinction matters when a restriction must work through one SQL endpoint or across the Fabric compute engines.

    3 / 19

  4. Quick check

    Users query lakehouse data with T-SQL through the SQL analytics endpoint, and granular permissions must be applied there. What do you use?

    1. AA OneLake security role, enforced across every engine

      OneLake roles are the cross-engine route; here the restriction belongs specifically to the SQL analytics endpoint.

    2. BT-SQL permissions, with GRANT and DENY

      Right. T-SQL permissions are the compute permissions for the SQL analytics endpoint, applied with Data Control Language commands.

    3. CA workspace role that covers the whole workspace

      A workspace role applies to every item in the workspace, which is far broader than endpoint granularity.

    4 / 19

  5. Filter rows and columns with T-SQL

    Inside the SQL route, two controls answer two different questions.

    T-SQL supports row-level security for SQL analytics endpoint access. Choose this control when the decision is about which records a SQL user can access.

    T-SQL also supports column-level security for SQL analytics endpoint access. Choose this control when the decision is about which fields a SQL user can access.

    5 / 19

  6. Filter rows and columns with T-SQL

    The vocabulary is worth fixing before an exam question uses it against you: records means rows, fields means columns. A requirement that says "only their own region's orders" is a row question; one that says "everything except salary" is a column question.

    Dynamic data masking is another granular T-SQL capability, but it is distinct from the row-level and column-level controls above.

    6 / 19

  7. Quick check

    An analyst querying through the SQL analytics endpoint must see only the patient records assigned to their unit. Which control fits?

    1. ARow-level security, applied through T-SQL for that endpoint's access

      Right. Restricting which records a SQL user can access is exactly what row-level security decides.

    2. BColumn-level security, applied through T-SQL

      Column-level security restricts which fields are visible, not which records are returned.

    3. CRead item permission on the lakehouse

      Read item permission exposes the item and its reports, and grants no underlying data access by default.

    7 / 19

  8. Keep your progress in the app

    That’s 3 of 8 quick checks. In the app they stay answered, and every lesson remembers where you left off.

    Get the app Keep reading here

  9. Express the same filters in a OneLake role

    OneLake security can express the same two dimensions in its role model. A OneLake security role can include optional row filters as constraints. A OneLake security role can also include optional column filters as constraints. The role's Constraints component is where both of those optional filters live.

    Because OneLake enforces its roles across Spark, SQL, and OneLake APIs, use this route when the row or column restriction must remain consistent across those engines. A filter written only in T-SQL stops at the endpoint; a notebook reading the same table through Spark would never see it.

    8 / 19

  10. Quick check

    A Viewer must be blocked from confidential columns through Spark, SQL, and the OneLake APIs alike. What do you configure?

    1. AA OneLake security role with a row filter

      A row filter decides which records are returned; it does not hide the confidential columns.

    2. BA OneLake security role with a column filter in its constraints

      Right. A column filter in the role's constraints is enforced across all three of the engines named in the requirement.

    3. CA Member workspace role, whose full data access covers every engine

      Member already holds full read and write access to OneLake data and is not restricted by a OneLake role.

    9 / 19

  11. Scope a role to tables or folders

    A OneLake security role is assembled from four components, and each one answers a separate question.

    Component Question
    Data Which tables or folders are covered
    Members Which users or groups are assigned
    Permission Read, or ReadWrite
    Constraints Which optional row or column filters apply

    A OneLake security role defines which tables its members can access. The role's Data component records the selected tables, while its Members component records the assigned users or groups. A OneLake security role defines which folders its members can access.

    10 / 19

  12. Scope a role to tables or folders

    The Permission component deserves a second look. The role's Permission component is either Read, for viewing data, or ReadWrite, for viewing and editing data in the selected tables or folders without granting a workspace write role.

    That last clause is the useful part: a contributor-style capability on one folder no longer forces a workspace-wide write role.

    11 / 19

  13. Quick check

    A Viewer must read the files in one folder through Spark and the OneLake APIs, without being able to edit them. How do you define the role?

    1. ASelect the folder, and set Permission to ReadWrite

      ReadWrite allows viewing and editing, which goes beyond the view-only requirement.

    2. BSelect a table instead, and set Permission to Read

      A table selection grants the wrong data scope when the requirement names a folder of files.

    3. CSelect the folder, with Permission Read

      Right. The Data component selects the folder and the Permission component is Read for view-only access.

    12 / 19

  14. Know who a OneLake role cannot restrict

    OneLake security roles are intended to grant specific data access to Viewers or users who have only Read item permission, because those users have no OneLake data access by default. The role is a way of adding narrow access, not of subtracting broad access.

    Admin, Member, and Contributor workspace roles already have full read and write access to OneLake data, so OneLake security roles do not restrict their access. Only workspace Admins and Members can create or modify OneLake security roles.

    13 / 19

  15. Quick check

    A OneLake role carries a row constraint, yet a user who is a workspace Contributor still reads every row. Why?

    1. AAdmin, Member, and Contributor already hold full read and write access to OneLake data, which a OneLake role does not restrict

      Right. Those three workspace roles already have full OneLake data access, and a custom role cannot narrow it.

    2. BRow constraints are enforced only through Spark

      OneLake roles are enforced across Spark, SQL, and OneLake APIs, so the engine is not the explanation.

    3. CThe role should have used ReadWrite

      ReadWrite would widen the role's permission to editing; it would not make the constraint bite.

    14 / 19

  16. Create a role and check what it inherits

    To create a role, open the lakehouse lake view and select Manage OneLake security. Select New role, name it, and choose Read or ReadWrite. Select the tables or folders to grant, save the role, and then edit it to add members and optional row or column constraints.

    Note the order: the role is saved first, then reopened to add its members and constraints.

    15 / 19

  17. Create a role and check what it inherits

    Then check inherited access before assuming a custom role is restrictive. Every new lakehouse includes a DefaultReader role. Sharing a lakehouse with Read all Apache Spark and subscribe to events adds the recipient to DefaultReader and grants read access to all data.

    When a user should be limited to a custom role, remove that user from DefaultReader or the user retains full read access through the default role. A narrow role sitting next to a wide one grants the union of the two, not the intersection.

    16 / 19

  18. Quick check

    A Viewer was shared a lakehouse with Read all Apache Spark and subscribe to events, then added to a narrow custom folder role. The user still reads every file. What do you do?

    1. AChange the custom role to ReadWrite

      ReadWrite widens the custom role rather than closing the wider path the user already has.

    2. BReplace the folder selection in the custom role with a table selection

      Changing the selected data scope narrows the custom role but leaves DefaultReader untouched.

    3. CRemove the user from the DefaultReader role

      Right. That sharing permission added the user to DefaultReader, which keeps read access to all data until the membership is removed.

    17 / 19

  19. Key takeaways

    • Use T-SQL row-level or column-level security for granular SQL analytics endpoint access.
    • Use a OneLake security role to select tables or folders and enforce its access across Spark, SQL, and OneLake APIs.
    • A custom OneLake role cannot restrict Admin, Member, or Contributor access, and DefaultReader membership can preserve full read access.
    18 / 19

  20. Quick check

    Which summary keeps the two granular routes and their limits straight?

    1. AUse OneLake roles for the SQL endpoint and T-SQL for Spark, and trust a custom role to restrict everyone

      The two routes are swapped, and a custom role cannot restrict Admin, Member, or Contributor access.

    2. BUse T-SQL row-level and column-level security for the SQL analytics endpoint, use a OneLake role when the restriction must hold across Spark, SQL, and the OneLake APIs, and remember that Admins, Members, and Contributors are not restricted by it

      Right. T-SQL for the endpoint, a OneLake role for cross-engine consistency, and higher workspace roles outside the reach of both.

    3. CUse T-SQL for every engine, and rely on DefaultReader to narrow a Viewer's access

      T-SQL permissions are compute permissions for the SQL endpoint, and DefaultReader widens access rather than narrowing it.

    19 / 19

  21. 8 quick checks · then the test

    In the app, finishing the quick checks opens this lesson’s 10-question test, and the ones you miss come back exactly when you’re about to forget them.

    Take this lesson’s test → Next lesson: Sensitivity Labels and EndorsementGet the app
← Previous Keep your progress · Get the app Next card →

The whole course, on your phone

Lessons you can read, audio you can listen to on the way to work, and practice that remembers what you got wrong.

Get the app