I need to implement a dynamic security model where access isn't based on a full email match. Instead, I want to filter rows by checking if a substring of the USERPRINCIPALNAME() (like a department code or prefix) exists within a specific column in my data. For example, if the user is IT_admin@company.com, I want to match the IT part. Is CONTAINSSTRING or SEARCH compatible with RLS rules, and what is the best syntax to avoid performance lag on large datasets?
3 answers
Yes, you can use CONTAINSSTRING or SEARCH within your RLS DAX expressions, but the syntax is critical. For your example, you could use a rule like: CONTAINSSTRING(USERPRINCIPALNAME(), [DepartmentCode]). This returns TRUE if the department code in that row is found anywhere in the logged-in user's string. However, I highly recommend using SEARCH if you need to handle specific positions, as it allows for a "not found" result that won't break the query. One major performance tip: if you are working with a large fact table, do not apply this logic directly to it. Instead, apply the substring RLS to a smaller dimension table (like a User or Org table) and let the filter propagate through a relationship. This keeps the DAX engine from evaluating a string comparison for every single row in your billions of sales records.
That approach sounds great for simple matches, but how do you handle security if the substring is common? If I have a department code 'IT' and a user named 'Britney', wouldn't CONTAINSSTRING accidentally grant her access because 'it' is in her name
Using LEFT or MID combined with SEARCH is often more robust. For instance, LEFT(USERPRINCIPALNAME(), SEARCH("_", USERPRINCIPALNAME()) - 1) = [Dept] works perfectly for prefixes.
Spot on, Jennifer. I’ve implemented this for a global firm where the first three letters of the UPN represent the country code. By using LEFT(USERPRINCIPALNAME(), 3) = [CountryCode], the RLS is extremely fast. It’s also much easier to test in Desktop by using the "View As" feature and entering a manual UPN string to see exactly what rows remain.
You’ve touched on a classic security pitfall, Brandon! To prevent those accidental matches, you should always include delimiters in your logic. Instead of just searching for the raw code, try searching for a pattern like @ or _. For example: CONTAINSSTRING(USERPRINCIPALNAME(), [DepartmentCode] & "_"). This ensures that 'IT_' only matches 'IT_admin' and not 'Britney'. Also, ensure your data is standardized to uppercase or lowercase using UPPER() or LOWER() on both sides of the comparison to prevent case-sensitivity issues from causing unexpected "Access Denied" errors for your users.