Row level security implementation

What is this row level security? 
Suppose you got a requirement saying that there is a table and if a user from America selects that table he should get records only w.r.t America, if a user from Asia selects the table he should get records only w.r.t Asia and so on. So here we will see how do we implement this level of security.

Step 1: Create database role

CREATE ROLE Role_America;

GO

CREATE ROLE Role_Asia;

GO

Step 2: Add users to database role. Add America users in Role_America and Asia users in Role_Asia.

EXEC sp_addrolemember @membername = 'DomainName\UserName', @rolename = 'Role_America';

EXEC sp_addrolemember @membername = 'DomainName\UserName', @rolename = 'Role_Asia';

example :

EXEC sp_addrolemember @membername = 'xyzCompany\John', @rolename = 'Role_America';

EXEC sp_addrolemember @membername = 'xyzCompany\Peter', @rolename = 'Role_Asia';

Step 3: Create a generic security table as shown below. So this table can be used to apply security on any table and column.

IF EXISTS(SELECT * FROM sys.tables WHERE name ='SK_RowLevelSecurity')

DROP TABLE SK_RowLevelSecurity

CREATE TABLE dbo.SK_RowLevelSecurity

(

TableName VARCHAR(500),

ColumnName VARCHAR(500),

SecurityFilterColumn VARCHAR(1000),

RoleName NVARCHAR(255),

CONSTRAINT PK_SecurityMap PRIMARY KEY CLUSTERED (SecurityFilterColumn,TableName, ColumnName,RoleName)

);

GO

INSERT INTO dbo.SK_RowLevelSecurity (TableName,ColumnName,SecurityFilterColumn,RoleName)

VALUES ('[Filter Table Name]','[Filter Column Name]','[Filter Column Value]','[Role Name]')

example

INSERT INTO dbo.SK_RowLevelSecurity  VALUES('TableName1','Region','America','Role_America')

INSERT INTO dbo.SK_RowLevelSecurity  VALUES('TableName1','Region','Asia','Role_Asia')

Step 4:  Now suppose assume that you have below table named TableName1 in which if a user from the respective region selects the table gets data of their region only.

Now create the below function. Actually you can just use IS_MEMBER(RoleName) function, which is a system built, but this dosent work for dbo user i.e. sysadmin user. The IS_MEMBER() function tells if the user is the member of specified role. If yes it returns 1 else 0. But for sysadmin user it return 0. So use the below function instead of system built.

CREATE FUNCTION [dbo].[Is_Member2](@rolename VARCHAR(255))

RETURNS TINYINT

AS

BEGIN

DECLARE @roleExists BIT

SELECT @roleExists = 1 FROM sys.database_principals WHERE name = @roleName

IF @roleExists IS NULL RETURN NULL

IF USER = 'dbo' RETURN 1

RETURN IS_MEMBER(@rolename)

END

GO

Create a view on the top of the table where you want the security to be implemented, as shown below

CREATE VIEW dbo.Vw_TableName1

AS

SELECT *

FROM dbo.TableName1 (NOLOCK)

WHERE Region IN (SELECT DISTINCT SecurityFilterColumn

                 FROM dbo.SK_RowLevelSecurity (NOLOCK)

                 WHERE TableName ='TableName1'

                   AND ColumnName='Region'

                   AND [dbo].[Is_Member2](RoleName) = 1)

Now if the user do a select query on Vw_TableName1 he/she will get the records only with respective to there region.

Now you have given the access to view, the user can open the view definition and see the source table from which the data is being pulled. So that he can take that table and see the data of another region. So to avoid that deny the user with view definition and grant only select. 

DENY VIEW DEFINITION ON [dbo].Vw_TableName1 TO [DomainName\Users]

GRANT SELECT ON [dbo].Vw_TableName1 TO [DomainName\Users]

**********************************************************************************************************************************************************************
                                      Some important SQL scripts related to row level security
**********************************************************************************************************************************************************************

/********** CREATE or MODIFY or DROP ROLE *************/

 

CREATE ROLE [Role Name];

GO

ALTER ROLE [Old Role name] WITH NAME = [New Role Name];

GO

DROP ROLE [Role Name]

GO

/********** Query to get memebers of the Role *************/

SELECT * FROM sys.database_principals WHERE type='R' AND is_fixed_role=0

SELECT rp.name AS database_role,

       mp.name AS database_user

FROM sys.database_role_members drm

JOIN sys.database_principals rp ON (drm.role_principal_id = rp.principal_id)

JOIN sys.database_principals mp ON (drm.member_principal_id = mp.principal_id)

WHERE rp.name='Role_America'

ORDER BY rp.name

/**** DROP a particular member from a role *****/

EXEC sp_droprolemember '[Role Name]', '[Member Name]';

/******** To Drop all the members of the Role **********/

 EXEC usp_SK_Drop_All_Members_Of_Role '[Role Name]'

-------

CREATE PROC [dbo].[usp_SK_Drop_All_Members_Of_Role]

(@UserDefinedRoleName VARCHAR(500))

AS

DECLARE @RoleName VARCHAR(500)

DECLARE @UserName VARCHAR(500)

DECLARE @cnt INT

DECLARE @Incr INT

DECLARE @sql NVARCHAR(MAX)

DECLARE @sql1 NVARCHAR(MAX)

DECLARE @sql2 NVARCHAR(MAX)

DECLARE @sql3 NVARCHAR(MAX)

SET @Incr=1

SET @sql1='SELECT @cnt=COUNT(*)'+CHAR(13)+

'FROM sys.database_principals A'+CHAR(13)+

'JOIN sys.sysmembers B ON A.principal_id=B.groupuid'+CHAR(13)+

'WHERE A.Name  IN ('+dbo.Fn_SK_Split(@UserDefinedRoleName,',')+')'

EXECUTE  sp_executesql  @statement = @sql1,

                        @params = N'@cnt INT OUTPUT',

                                         @cnt=@cnt OUTPUT

SET @sql2 =';WITH CTE'+CHAR(13)+

                     'AS'+CHAR(13)+

                     '('+CHAR(13)+

                           'SELECT A.Name AS RoleName, USER_NAME(memberuid)UserName,ROW_NUMBER() OVER(ORDER BY A.Name ASC)rno'+CHAR(13)+

                           'FROM sys.database_principals A'+CHAR(13)+

                           'JOIN sys.sysmembers B ON A.principal_id=B.groupuid'+CHAR(13)+

                           'WHERE A.Name  IN ('+dbo.Fn_SK_Split(@UserDefinedRoleName,',')+')'+CHAR(13)+

                     ')'+CHAR(13)+

                     'SELECT * FROM CTE'+CHAR(13)

CREATE TABLE #temp (RoleName varchar(500), UserName varchar(500),rno INT)

INSERT INTO #temp

EXEC (@sql2)

WHILE @Incr<=@cnt

BEGIN

                     SET @sql3= 'SELECT @RoleName=RoleName, @UserName =UserName FROM #temp WHERE rno='+CAST(@Incr AS VARCHAR(10))

                     EXECUTE  sp_executesql  @statement = @sql3,

                                    @params = N'@RoleName VARCHAR(500) OUTPUT,@UserName VARCHAR(500) OUTPUT',

                                                     @RoleName=@RoleName OUTPUT,@UserName=@UserName OUTPUT

              SET @sql='sp_droprolemember '''+@RoleName+''','''+@UserName+''''

              EXEC(@sql)

              SET @Incr=@Incr+1

END