Advanced Row-Level Security: Multi-Role Intersections & Hierarchy Security

In enterprise deployments, security requirements rarely consist of simple static filters. Global organizations require matrix security models:

• A user can only see data for their assigned region AND their specific product line.
• A regional manager must see data for their direct subordinates and all downstream employees across an organizational hierarchy tree.

1. Multi-Dimensional RLS: The Intersection Dilemma

When an employee has multiple access constraints (e.g., Region = North, Department = Healthcare):

The Common Enterprise Mistake:

Creating two separate roles:

• Role 1: North_Role (Filter: [Region] = "North")
• Role 2: Healthcare_Role (Filter: [Department] = "Healthcare")

The Secure Solution: Single Compound Role

2. Manager Hierarchy Security with PATHCONTAINS

To allow managers to automatically see data for themselves and everyone reporting up through their chain of command:

How It Works:

1. 1
   USERPRINCIPALNAME() retrieves the login email of the user viewing the report.
2. 2
   LOOKUPVALUE identifies their internal EmployeeID.
3. 3
   PATHCONTAINS checks if their EmployeeID appears anywhere in the EmployeePath of any row!
4. 4
   Result: The manager automatically sees data for their entire organization without creating thousands of individual static roles!