Dynamic Column-Level Security (CLS) Filtering for OneLake Security in Microsoft Fabric SQL Endpoints

Dynamic Column Filtering for OneLake Security CLS in Lakehouse SQL Endpoints

We would like to propose an idea regarding the current behavior of Column-Level Security (CLS) in Microsoft Fabric OneLake Security when accessing data through Lakehouse SQL Endpoints.

During troubleshooting and validation sessions with Microsoft Support, we confirmed that CLS is functioning as currently designed. However, we observed a limitation in how CLS behaves during schema validation and query execution from SQL Endpoints and Power BI semantic models.

Current Behavior

After creating a OneLake Security role with CLS restrictions:

Restricted columns are still exposed within the Lakehouse SQL Endpoint schema metadata.

The SQL Endpoint appears to scan or validate the complete table schema before executing the query.

If restricted columns exist in the semantic model, query definition, or metadata discovery process, queries fail with permission errors such as:

“SELECT permission was denied on the column…”

This happens even when users only intend to access permitted columns.

Because of this behavior:

1. Power BI semantic models may fail during refresh or schema discovery.
2. Developers must manually exclude restricted columns.
3. Separate materialized lake views must be created for different security personas.

Additional maintenance and governance overhead is introduced.

Example Scenario

Table:

1. EmployeeID
2. EmployeeName
3. Salary
4. SSN

CLS Restriction:

1. Salary
2. SSN

Expected Experience:

Users should automatically see only:

1. EmployeeID
2. EmployeeName

Current Experience:

Restricted columns remain discoverable through schema validation, and queries may fail instead of dynamically filtering unauthorized columns.

Proposed Idea

We propose that the Lakehouse SQL Endpoint dynamically recognize the authenticated user principal and automatically apply CLS filtering during schema discovery and query execution.

In this approach:

Restricted columns would automatically be hidden from the schema presented to the user.

Only authorized columns would appear during Power BI schema discovery, SSMS access, and SQL query execution.

Queries and semantic models would execute successfully without requiring developers to redesign datasets or manually remove restricted columns.

Benefits

This approach would:

1. Simplify Power BI and SQL development.
2. Reduce dependency on materialized lake views.
3. Improve enterprise scalability for self-service BI.
4. Reduce maintenance overhead.
5. Provide a more intuitive CLS experience for Lakehouse SQL Endpoints.

Currently, the materialized lake view workaround is functional, but it introduces operational complexity for enterprise environments managing multiple datasets, users, and security roles.

We believe dynamic CLS-aware schema filtering would significantly improve the usability and enterprise adoption of OneLake Security in Microsoft Fabric Lakehouses and SQL Endpoints.

Thank you for considering this idea.