Skip to main content

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:
  1. We have a table (Product) with nearly 10000 row.
  2. User table has n..n relation with Product.
  3. All of actions have security attributes to controls users access according to claims.
  4. 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 thisthis 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.

Update:
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),
        username varchar(100),
        permission varchar(20)
    )
but it is painful for users who want to manage the permissions. Then I'm seeking a better design for it.

Update:
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)

Update:
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.

Final Update:
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.
ERD diagram
This post helped me to find a better solution for RLS, finally.
Post a Comment

Popular posts from this blog

Parameter Sniffing

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

Good luck.

How can you save a MS Office file into a database?

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     sql.Parameters.Add(sqlPar)
I hope you would find it useful.