Row Level security using TVF SQL Server 2005

In applications Row level security is very important. So what we need to do that user will see only rows of tables which he has permission.
Here are 3 Easy steps

1) Add DataAccess Column to each and every table in your Database.

2) Create TVF for each Table
e.g. Departments
So create TVF :

Create function [dbo].[fnDepartments](@DataAccess nvarchar(50))
returns table
as
return(
Select * from Departments where @DataAccess in ( Select * from dbo.fnsplit(DataAccess,',')) or @DataAccess = 'Any'
)
3) Create dbo.split function in database
(Split Function is not created by me I found it while googling, I mean only split function :) )
Create FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
And now you are ready to use this in your application.
So How you will use this?
When ever you insert a record insert UserID of users separated by comma into DataAccess Column.
eg. 232,111,234
Now when you will access this data using select dont use tables instead use TVF
e.g. Select * from fnDepartments('234')
This way user 234 will access only rows which he has permission. Rest according to your user heirarchy you can add UserIDs to DataAccess.
If you wish to access all rows just pass 'Any' instead of UserID. e.g. fnDepartments('Any')
As TVF returns Table you can use it like table and perform all operations using select like where condition, having, group by,order by etc.
Only thing is you can not perform Insert, Update on TVF. So you will have to use tables directly to insert records( with DataAccess )
For using Stored procedure you can have common parameter for all procedures like @DataAccessID where you will pass UserIDs separated with comma for users you want to allow access Records.

This is very simple and effective way. Though many people have argument over this that TVF performs slower. But for this reason I inserted 6 lac records to table and checked execution time of procedure created on Tables, Views and TVF there was not much of difference or I can say there is no difference. This might be enhanced in SQL Server 2005 so I dont know about SQL server 2000.
Hope this article helps some one to make applications easier and secured.
Please comment on this article and let me know if it helps you. Or you have any suggestions over this.

Regards,
Upendra Kolte

http://www.ingenioustech.co.in/