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;

How MERGE statement works

Generating new ID without Identity

           Let’s see how Merge statement works, either it will read all the data from the source and start doing operation on the target or will it take row by row from the source and join with target and then update /insert the records in the target table.

  Here we are trying to address small misconception about merge, suppose if you have one table test1 in that one column is there called id we want to increase the values of the ID, but this column is not Identity. Let’s see this practically.

USE TestDatabase
GO
-- drop table test2
-- drop table test1

CREATE TABLE test1 (id INT, name VARCHAR(100))
GO
CREATE TABLE test2 (id INT identity, name VARCHAR(100))
GO
INSERT INTO test2(name) values ('amit'),('sumit'),('abhi'),('sachine'),('subhash')

INSERT INTO test1 VALUES(1, 'vikas');
INSERT INTO test1 VALUES(2, 'pnm');
SELECT * FROM test1;

-- drop table #t
CREATE TABLE #T (ID INT ,ACTION1 VARCHAR(100))

--SET SHOWPLAN_TEXT on
; MERGE test1 AS TARGET
using( SELECT id AS ID,NAME FROM test2 )
AS SOURCE
ON TARGET.ID=SOURCE.ID
WHEN MATCHED THEN
UPDATE
SET NAME=SOURCE.NAME
WHEN NOT MATCHED THEN
INSERT
VALUES((SELECT MAX(ID)+1 FROM test1),SOURCE.NAME)
OUTPUT INSERTED.ID,$ACTION INTO #T
;

--SET SHOWPLAN_TEXT OFF 
 SELECT * FROM test1

Now we are getting same values for the id this should 3, 4, 5 but it is displaying 3 for all three records. Let’s understand the execution process. In below execution plan we clearly see that’s it is reading data from target table and calculating Max after that it starting merge statement. So there is no chance to get the new max after every insert. This is the default behavior of the SQL server.

|--Stream Aggregate(DEFINE:([Expr1012]=MAX([TestDatabase].[dbo].[test1].[id])))
                                          |--Table Scan(OBJECT:([TestDatabase].[dbo].[test1]))

-------------------------


  |--Table Merge(OBJECT:([TestDatabase].[dbo].[test1] AS [TARGET]), SET:(Insert, [TestDatabase].[dbo].[test1].[name]
  as [TARGET].[name] = [Expr1008],[TestDatabase].[dbo].[test1].[id] as [TARGET].[id] = [Expr1014]), SET
 :(Update, [TestDatabase].[dbo].[test1].[name] as [TARGET].[name] = [Expr1008]) ACTION:([Action1007]))
       |--Assert(WHERE:(CASE WHEN [Expr1020]>(1) THEN (0) ELSE NULL END))
            |--Sequence Project(DEFINE:([Expr1020]=conditional_row_number))
                 |--Segment
                      |--Sort(ORDER BY:([Bmk1003] ASC))
                           |--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Action1007]=(4) THEN [Expr1012]+(1)
    ELSE [TestDatabase].[dbo].[test1].[id] as [TARGET].[id] END))
                                |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Action1007]=(4)))
                                     |--Compute Scalar(DEFINE:([Expr1008]=[TestDatabase].[dbo].[test2].[name]))
                                     |    |--Compute Scalar(DEFINE:([Action1007]=ForceOrder(CASE WHEN [TrgPrb1005] IS NOT NULL
      THEN (1) ELSE (4) END)))
                                     |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TestDatabase].[dbo].[test2].[id]))
                                     |              |--Table Scan(OBJECT:([TestDatabase].[dbo].[test2]))
                                     |              |--Compute Scalar(DEFINE:([TrgPrb1005]=(1)))
                                     |                   |--Table Scan(OBJECT:([TestDatabase].[dbo].[test1] AS [TARGET]),
         WHERE:([TestDatabase].[dbo].[test1].[id]
        as [TARGET].[id]=[TestDatabase].[dbo].[test2].[id]) ORDERED)
                                     |--Stream Aggregate(DEFINE:([Expr1012]=MAX([TestDatabase].[dbo].[test1].[id])))
                                          |--Table Scan(OBJECT:([TestDatabase].[dbo].[test1]))

  As a solution you can use the SEQUENCE , CREATE SEQUENCE Test.CountBy1     START WITH 1     INCREMENT BY 1 ;

GO