Automated check of Row-Level Security policy at SQL Endpoint

[hominid]

We will be implementing Row-Level Security at the SQL endpoint for a data product and there is some concern about the potential, even if unlikely, risk of the RLS policy getting dropped either through someone accidentally dropping it or a bug.  For instance there seems to be a known bug Known issue - SQL analytics endpoint tables lose permissions - Microsoft Fabric | Microsoft Learn where permissions can be impacted at the SQL endpoint.

I'm wondering is there a known way to set up an automated check of the status of the RLS policy, so that the policy can be re-applied or at least someone alerted if the policy gets dropped?  In particular it would be nice to have an automated post-deployment check.

Any insights or pointers would be much appreciated.

[v-veshwara-msft · Accepted solution]

Hi @hominid ,
Thanks for posting in Microsoft Fabric Community.
To make sure your Row-Level Security (RLS) policy is working correctly, here’s what you can do:

1. Check if the RLS Policy is Active: Run this SQL query to see if your RLS policy is enabled:

   sql:

   SELECT 
   name AS PolicyName,
   object_id AS PolicyID,
   is_enabled AS IsEnabled
   FROM 
   sys.security_policies
   WHERE 
   name = 'Your_RLS_Policy_Name';
   
   

   If the policy is active, it will show IsEnabled = 1. If it’s missing or turned off, it will show 0 or no results.

2. Set Up Alerts to Monitor the Policy: You can set up automatic checks to monitor your policy and get alerts:

   Using Power Automate: Here’s how to create a flow to automatically check your RLS policy status:

   1. Create a New Flow:In Power Automate, select Create and choose Scheduled flow.

   2. Set Flow Schedule:Set a schedule for how often you want to check the RLS policy status (e.g., hourly or daily or weekly).

   3. Add SQL Server Action:Add a SQL Server action and select Execute a SQL query V2.
      Connect your SQL database (where your policy is stored) and paste the RLS policy check query.

   4. Add Condition to Check Policy Status:After running the query, use a Condition to check if the policy’s IsEnabled value is 1. If not, proceed to send an alert.

   5. Send an Alert:If the policy is not enabled, use an action like Send an email (V2) to notify you (or the appropriate person) that the RLS policy is missing or inactive.
      
      Using Data Activator: If Data Activator is available in your capacity, you can monitor changes to the RLS policy and automatically trigger alerts when the policy is modified or disabled.

      
      Please let us know if you need assistance with any step or need guidance for Data Activator.
      
      If this post helps, please consider accepting as solution to help others find easily and a kudos would be appreciated.

      Best regards,
      Vinay

[lbendlin]

Just run a query against the RLS source with a user that is part of a "Deny" role. If they see data then your setup is borked.