A poor man’s implementation of row level security in SQL

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.

Scenario:

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.

CREATE
FUNCTION GetPermissibleLevel()

RETURNS
INT

AS

BEGIN

    DECLARE @minLevel INT

    IF IS_RoleMember(‘Administrators’) = 1

        SET @minLevel = 0

    ELSE

        SET @minLevel = 3

    RETURN @minLevel

END;

GO

CREATE
VIEW vEmployeePayHistory

AS

SELECT HumanResources.EmployeePayHistory.*

    FROM HumanResources.EmployeePayHistory

    INNER
JOIN HumanResources.Employee ON HumanResources.Employee.OrganizationLevel >=
(SELECT [dbo].GetPermissibleLevel())

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.