Often I have come across situations where the enterprise customers want to restrict data availability to their employees / IT not only from the application but also at the database level. Government / Public sectors have a common requirement that they don’t want everybody at their IT team to be able to query everybody’s data.
As of today there is no out of the box support for this feature in on-premise SQL. The implementation we are going to talk about is not something new but a pretty commonly used logic in Dynamics CRM. I call it “filtered views”.
Most of the SQL developer / DBA would agree to the fact that never expose a table directly to a Stored Procedure / application / dev instead, create views. The idea is similar here. We will use views to filter out the records the user / dev is seeing.
The row filtering logic is governed by the requirement and therefore there is no right or wrong way of implementation.
Row filtering based on user roles.
The approach here is that rows will be filtered based on the SQL Server Roles the end-user has.
In AdventureWorks2012 database I created 2 database roles created “Developers” and “Administrators”. Developers can see the “EmployeePayHistory” for the employees who are of level 3 and greater and Administrators can see everybody’s. Below is how I would write the view for this.
DECLARE @minLevel INT
IF IS_RoleMember(‘Administrators’) = 1
SET @minLevel = 0
SET @minLevel = 3
JOIN HumanResources.Employee ON HumanResources.Employee.OrganizationLevel >=
Row filtering based on CLR function
Replace the above function with a CLR function and that’s it. So where can you put them into use?
- If you had custom business logic for authorization of the user, then a CLR function can leverage and re-use this.
- If you had a ADFS / ACS / even Azure based authorization logic, those can be ported here.
With great power, comes greater responsibility; therefore ensure that whatever you write in that logic returns the result within a micro second or else you will be introducing performance nightmare for every single SQL call. Ensure appropriate caching is in place for every reused authorization data.