about c#, vb.net, asp.net, sql, code, tips, bugs, solutions...
Search This Blog
Row level security for a table in SQL Server
This post is a question I asked in SO while ago, but I did not got any answer. So because of deletion warning I posted it here.
I've implemented a claim base application by MVC, C#, EF6 and SQL Server 2014. Now there is a new requirement to add row level security on it.
The scenario is:
We have a table (Product) with nearly 10000 row.
User table has n..n relation with Product.
All of actions have security attributes to controls users access according to claims.
When a user wants to fetch data from db, I want to filter result based on user access. Every user must view the related data.
There is a solution for this matter in this, this but my problem is the rows count in the table (Product). If I create a new table (Product_ACL) to persist users permission to Product records per capita, managing the permission is so hard and full potential of mistakes for the end user to managing users permissions.
The normal way to solve my issue is:
create table Product(Id INT PRIMARY KEY,Name nvarchar(100)...)
create table Product_ACL(ProductId INT REFERENCES Product(Id),
but it is painful for users who want to manage the permissions. Then I'm seeking a better design for it.
In brief I'd like to add Row Level Security in an application like this tutorial but the difference between this sample and my application is in user access to rows. In my application a record which has security tag can be accessed by more than on user then I want to change the design of my tables to achieve RLS(I have to upgrade to SQL Server 2016)
To summarize my thoughts, I think I can achieve my desired by making some changes in my entities. I have a User table to store users information. A Product table for storing product information with specifying a product's region, province and city info(geography information). Then with an extra table for storing users permissions. for example: user_1 has access to province_1 then user_1 has access to all products in province_1. If I set city field also then user_1 access only all products in province_1 and city_1.
create table User(Id INT PRIMARY KEY,Name nvarchar(100)...)
create table Product(Id INT PRIMARY KEY,Name nvarchar(100)RegionId INT REFERENCES Region(Id) NULL,ProvinceId INT REFERENCES Province(Id) NULL,CityId INT REFERENCES City(Id) NULL
create table User_ACL(UserId INT REFERENCES User(Id),RegionId INT REFERENCES Region(Id) NULL,ProvinceId INT REFERENCES Province(Id) NULL,CityId INT REFERENCES City(Id) NULL
I think with this model if I add a new table that needs security, I can cover its requirement.
I finally designed a security model to my application.
Is there any suggestion or improvement about my idea?
I deleted Product and other entities from my model.
This post helped me to find a better solution for RLS, finally.
Sometimes maybe you find out your SP (Store Procedure) doesn't response in reasonable time despite Select statement, which you used in that SP, has good performance and result is shown you immediately. What's wrong with your SP? There are different answers for that problem but when I faced on this problem, I realized it's strongly related to Parameter Sniffing.
You can solve Parameter Sniffing easily with follow this example: If you have a SP such as GetOrderForCustomers that has Parameter Sniffing issue:
Create procedure GetOrderForCustomers (@CustID Varchar (20)) As Begin Select * from orders Where customerid = @CustID End
To solve problem you should change your SP similar:
Create procedure GetOrderForCustomers (@CustID Varchar (20)) As Begin Declare@LocCustID Varchar (20) Set @LocCustID = @CustID Select * from orders Where customerid = @LocCustID End
If you want to save a MS Office document such as Word doc in a database, you can do it with the following VB.net code:
Dim vStream As New FileStream("C:\Alex.doc", FileMode.Open)
Dim vLen As Integer = New FileInfo("C:\Alex.doc").Length
Dim vBlob(vLen) As Byte
Dim n As Integer = vStream.Read(vBlob, 0, vLen)
Dim vSql As String = "INSERT INTO tBlobTable (vId,vBlob) VALUES (1,@vBlob)" Dim sql As New SqlCommand(vSql, vConn)
Dim sqlPar As New SqlParameter("@vBlob", SqlDbType.Image)
sqlPar.Value = vBlob
I hope you would find it useful.