Row Level Security

Row Level Security in SQL Server without using View

     
 How nice if you can provide the security on row level even without creating many views. This is also called RLS in sort form. Especially when you are designing multi-tenant application and all tenant data resides in same Database and data is getting distinguished by Tenant identification. But still all data are together inside. Suppose you are admin and you have separate development team who is taking care of separate tenant data (Especially data issue), then how you will manage or restrict the developer to not to access or disturb another tenant data? Developer feel very tuff to query data because for every query he has to remind himself that must put the tenant id at the end, that’s not easy, here the chance of accidental data loss is much more, because it’s introducing extra overhead to the developer. Just think how blunder if he keep everything perfectly and just miss to mansion tenant id at the end (because this is new habit he has to make), then it will burn the data of another tenant silently.

 Let’s look this Row-Level-Security of SQL server 2014. This is not available in older versions. To implement this you just have to go through below steps:

Note : To know the existing security policy and meta data you can use below query
select * from sys.security_policies
select object_name(object_id),OBJECT_NAME(target_object_id),predicate_definition from sys.security_predicates

Create database RLSDB
go
USE RLSDB


/* Creating LOGIN/user */ GO
CREATE LOGIN TLGStateAdmin WITH PASSWORD='admin#123'

CREATE LOGIN RDYDistAdmin WITH PASSWORD='admin#123'

CREATE USER TLGStateAdmin FOR LOGIN TLGStateAdmin

CREATE USER RDYDistAdmin FOR LOGIN RDYDistAdmin

GO

/*Permission */

EXEC sp_addrolemember N'db_datareader', N'TLGStateAdmin'

EXEC sp_addrolemember N'db_datareader', N'RDYDistAdmin'

create table Tenant (tntid int identity primary key , tntname varchar(100))

go
Create table Person(id int identity , name varchar(100)
, tenanatid int , foreign key(tenanatid) references Tenant(tntid) )

go
Insert into Tenant (tntname)
select 'NorthZone'

union
select 'SouthZone'

go

Insert into Person (name,tenanatid) select 'vikas',1
union
select 'amit',1
union select 'sachin',2
union
select 'TLGStateAdmin',2
union
select 'RDYDistAdmin',2

/* Creating security schema to place security function separate*/

CREATE SCHEMA SECURITY;

CREATE FUNCTION SECURITY.fn_SecurityCheck(@personName AS varchar(100))
RETURNS TABLE
WITH SCHEMABINDING

AS
RETURN SELECT 1 AS fn_TenantSecurityCheck_result
WHERE @personName=user_name()

go

CREATE SECURITY POLICY personfilter
ADD FILTER PREDICATE Security.fn_SecurityCheck(name)
ON dbo.person--,dbo.sales1
WITH (STATE = ON);

go
Now Let' s test it :
EXECUTE AS USER = 'TLGStateAdmin';
SELECT * FROM person;
REVERT;

EXECUTE AS USER = 'RDYDistAdmin';
SELECT * FROM person;
REVERT;