Implementing Row-Level Security (RLS) and Column-Level Security (CLS) on a Mirrored database in Microsoft Fabric

When working with Mirrored Databases in Microsoft Fabric, you may notice that the security pane for Row-Level Security (RLS) and Column-Level Security (CLS) is grayed out and read-only.

Why is the OneLake Security UI Disabled?

This is not a bug or a missing feature; it is centered on Schema Integrity:

• Source Control: For Mirrored Databases, the source system is the "Source of Truth." The schema, column definitions, and overall structure are defined at the origin.
• Preventing Drift: Fabric is engineered to prevent source and security drift. By locking the UI, Fabric ensures that the mirrored item remains a faithful reflection of the source without conflicting security logic being applied mid-stream.

The "Permission" Misconception

A common question I get is around the role of Workspace permissions. The critical point here is, having Fabric Admin or Contributor permissions will not enable the RLS/CLS UI. The interface is disabled based on the Item Type (Mirrored Database), not the user's permission level.

Can You Still Implement RLS and CLS?

Yes, you can do that, and I will walk you through the process in this blog post.

Instead of using the UI designer, you must use alternative mechanisms (T-SQL Security) to enforce security boundaries while maintaining the integrity of the mirrored link.

Security Type	Fabric OneLake Security UI editable?	How to implement?
Row Level Security (RLS)	No	T-SQL pattern Security policy
Column Level Security (CLS)	No	T-SQL GRANT/DENY

 

Where does the Security live?

The most important takeaway is that security for mirrored data is decentralized from the main Fabric UI. Because you cannot use the OneLake Security designer, you must use the SQL Analytics Endpoint and the security logic is stored within the Fabric SQL Database layer itself.

 

Applying Column Level Security:

Column Level Security can be applied via T-SQL GRANT/DENY statements. If you want to DENY access to a specific columns lets says COLUMN A, for all Developers, here is what you need to do.

1. Create a DEVELOPER Role (DEVROLE) and Add Members (individual Email ids) to that Role.
2. Run the SQL DENY – DENY SELECT ON SCHEMA_NAME.TABLE_A (COLUMN A) to DEVROLE
3.  SQL DENY ensure that all members in the role DEVROLE will be denied access to the column “COLUMN A” in the table TABLE_A.

  

Applying Row Level Security:

Step 1 – Mirror a Database in Fabric. As of this writing date, Fabric supports database mirroring for SQL Server, Snowflake, Azure Cosmos DB and many more including Oracle, Google Big Query in public preview.

Step 2 – For this blog, lets have a Security requirement: Users must not see rows in the CUSTOMER table where StatusCode = 1

Tip: Fabric does not support SHOW TABLE or DESC TABLE. Use the T-SQL construct instead. If you want to list all columns and their data types, use

SELECT

    COLUMN_NAME,

    DATA_TYPE,

    CHARACTER_MAXIMUM_LENGTH,

    NUMERIC_PRECISION,

    NUMERIC_SCALE,

    IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'MIRRORSCHEMA'

  AND TABLE_NAME   = 'CUSTOMER '

ORDER BY ORDINAL_POSITION;

 

Step 3: Open the Mirrored database and select the SQL Analytics Endpoint. Next open a New Query. This is where you will define the RLS for mirrored data.

Step 4: Create Roles and add members to that role –

• CREATE ROLE RLSTEST;
• ALTER ROLE RLSTEST
• ADD MEMBER [someone@example.com];

Step 5: Create a function with the definition to apply the RLS –

CREATE FUNCTION dbo.fn_BlockSensitiveRows (@STATUSCODE decimal)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(

    SELECT 1 AS fn_result

    WHERE @STATUSCODE <> 1

);

 

Step 6: Create a Security Policy and then attach this function to the table that was mirrored into Fabric.

CREATE SECURITY POLICY SensitiveDataRLS

ADD FILTER PREDICATE dbo.fn_BlockSensitiveRows(STATUSCODE)

ON MIRRORSCHEMA.CUSTOMER

WITH (STATE = ON);

Step 7: Grant access to your table to your users. 

            GRANT SELECT ON MIRRORSCHEMA.CUSTOMER TO RLSTEST

Once you have run these steps, if you query the table, you should ONLY see rows with where STATUSCODE is not equal to 1.  

Run a test –

SELECT * FROM MIRRORSCHEMA.CUSTOMER  with STATUSCODE = 1. Does not show anything even though there is data for STATUSCODE = 1 in the table.

 

Remember –

·             Object Level Security is for Table and/or Columns while RLS is for Rows.

·             Both RLS and OLS, only applies to viewers in a workspace. Workspace members assigned as Admin, Member, or Contributor have edit permissions for the semantic model, and therefore, OLS doesn’t apply to them.  This means that admin members will still have access to the secured tables or columns, as they have the necessary permissions to edit the model.

·             You cannot apply OLS to a TABLE which is in middle of a relationship. If you have 3 tables Product -> Category -> SubCategory. You cannot apply OLS only to Category table. However, you can apply OLS to columns in tables which are in middle of relationship but NOT for Key columns. Only Non-Key columns in tables middle of relationship.

 

Step 8: Additional information: Steps to Change/Alter an existing RLS condition 

If you want to alter the RLS condition (Instead of STATUSCODE <> 1, make it STATUSCODE = 1), you must first DISABLE or DROP the security policy.

 

Step 8A:  ALTER SECURITY POLICY SensitiveDataRLS WITH (STATE = OFF); OR

DROP SECURITY POLICY SensitiveDataRLS

Step 8B: Now ALTER or CREATE (if you dropped) function –

CREATE OR ALTER FUNCTION dbo.fn_BlockSensitiveRows (@STATUSCODE decimal)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(

    SELECT 1 AS fn_result

    WHERE @STATUSCODE =1

);

Step 8C: If you dropped the Security policy earlier, create a new security policy and attach it to the table as explained in Step 6. 

~Narendra V Joshi