How to implement row-level security in Lakehouse: step by step

João Barros 25 de October de 2026 6 min read

We will implement row-level security in Lakehouse to control which rows each user or team can see. This technique is useful to meet security and privacy requirements without duplicating data or creating multiple tables per customer. By filtering at the row level, you keep a single source of truth (for example, a Delta table) and apply access policies centrally and auditable.

Prerequisites

Account with access to Microsoft Fabric and permission to create tables in the Lakehouse.
An existing Delta table with a security column (e.g.: tenant_id, region or owner_id). Ideally the column has normalized values (e.g.: T001, T002) and the table contains hundreds to millions of rows.
Access to the SQL analytics endpoint or a notebook with PySpark/SQL.
Basic concepts of SQL and permissions in Fabric. If you have a small team, review the list of roles and identify who will have role_admins.

Step 1: Choose the control column and create a mapping table

Identify the column that defines visibility (for example tenant_id). This column should be stable and efficient for joins (indexable or partitionable). Create a mapping table that relates users/groups to allowed values. This table acts as the access control list (ACL) and allows managing permissions without changing the main table.

CREATE TABLE IF NOT EXISTS security.mapping_users_tenants (
  user_id STRING,
  tenant_id STRING
);

-- Exemplo de inserção
INSERT INTO security.mapping_users_tenants VALUES
('alice@contoso.com','T001'),
('bob@contoso.com','T002'),
('group_sales','T001');

Practical example: if your organization has 50 tenants and 200 users, the mapping table will typically have a few hundred rows. Keep timestamps or audit columns if you need to track when a mapping was changed.

Step 2: Create a secure view that filters by user

Create a view that uses the user context (CURRENT_USER or equivalent function) to filter the main Delta table by tenant_id. This way, users query the view instead of the table directly. Views make the policy explicit and easy to audit.

CREATE OR REPLACE VIEW secure.v_orders AS
SELECT o.*
FROM lakehouse.orders o
JOIN security.mapping_users_tenants m
  ON o.tenant_id = m.tenant_id
WHERE m.user_id = CURRENT_USER();

Notes: CURRENT_USER() usually returns the UPN (e.g.: alice@contoso.com). If you use groups, adapt the logic to check group membership (via a table_groups or a function that expands groups). In environments with thousands of queries, confirm the view has acceptable performance — a join on tenant_id is typically O(N) over the relevant partition and tends to be fast when tables are properly partitioned.

Step 3: Test the view with simulated users

If you cannot change identities in the test environment, you can replace CURRENT_USER() with a literal value or create test views. Test several scenarios: a user with access to one tenant, another with access to multiple tenants, and one with no access. Count the expected rows: for example, if orders has 10,000 rows and tenant T001 has 1,250 rows, alice should see exactly those 1,250 rows.

-- Test by replacing CURRENT_USER()
SELECT * FROM secure.v_orders WHERE 1=1; -- as alice you will configure CURRENT_USER() in the environment

-- Alternative for testing
CREATE OR REPLACE VIEW secure.v_orders_test AS
SELECT o.*
FROM lakehouse.orders o
JOIN security.mapping_users_tenants m
  ON o.tenant_id = m.tenant_id
WHERE m.user_id = 'alice@contoso.com';

Run automated tests: for example, a set of 10 queries that validate counts and samples (SELECT COUNT(*) and SELECT TOP 10). Document expected results for regression.

Step 4: Restrict direct access to the base table

It is important to prevent users from reading the Delta table directly. Revoke SELECT permissions on the base table and grant them only to the admin team; grant SELECT only on the secure view. Without this step, a privileged user could bypass the view.

-- Revogar acesso geral
REVOKE SELECT ON TABLE lakehouse.orders FROM PUBLIC;
-- Conceder apenas a admins
GRANT SELECT ON TABLE lakehouse.orders TO role_admins;
-- Conceder acesso à view a utilizadores
GRANT SELECT ON VIEW secure.v_orders TO PUBLIC;

Always verify permissions with administrative queries (for example, SHOW GRANTS). For auditing, record who has the role_admins role; ideally limit that role to fewer than 5 administrators.

Step 5: Automate mapping updates (optional)

For dynamic environments, create an ETL/ELT process that updates security.mapping_users_tenants from Azure AD or another identity system. A simple notebook with PySpark can synchronize changes daily at 01:00, typically taking 1–5 minutes for tables with a few thousand rows.

# Exemplo PySpark para atualizar mapping (esquema mínimo)
mapping_df = spark.read.format('delta').load('/mnt/security/mapping_users_tenants')
# Código para obter dados de identidade e escrever de volta
new_df.write.format('delta').mode('overwrite').save('/mnt/security/mapping_users_tenants')

Consider using incremental updates instead of overwrite if you have many records. Log changes and send alerts when a critical mapping changes (for example, removal of an administrator's access).

Verify the result

To confirm that row-level security works: 1) Sign in as user A and run SELECT on the view secure.v_orders — you should only see rows for A's tenant; validate with COUNT(*). 2) Try SELECT directly on lakehouse.orders with the same user — it should be denied. 3) Test with another user/group to ensure isolation. Perform basic load tests (for example 50 concurrent queries) to ensure the view does not become a bottleneck.

Conclusion

With a control column, a mapping table and filtered views you can implement row-level security in Lakehouse in a simple, auditable and scalable way. Next steps: integrate with Azure AD for automatic group management, use dynamic policies based on roles, and include automated tests with test accounts. Tip: document the mappings, log changes and test regularly to prevent data leaks.