How to implement content based authorization in ASP.net and SQL Server?

Question

I am developing data application for governments, and I have a situation in which I need to make data shared to all users in one page but with different privileges levels that can control authorization based on locations, not just simple admins and viewers or editors roles.For Example, I have Locations Table that contains regions, cities, and districts in a hierarchal pattern, and all data will be displayed on the page will be affected by this location changes then the user who is authorized for a city can see only data related to this city and users can be authorized for multiple cities and multiple datasets within the page, If we maintained user inside a record then we need records number multiplied by authorized locations all multiplied by authorized datasets which can be infinity.So what's the best practice to store those user roles for each single data record related to specific location?

Answer 1

I'd have a look at row level security in the first instance. It allows you to set up security policies that allow different rows to be available to different groups/users.

Microsoft's thread for this starts here: https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security

And there's also a good tutorial on Plural Site.

I'd post some examples but it's too broad a subject to answer here.

One point to note - there are performance limitations on row level security.

If security isn't as much of a concern then a mapping table and some clever joins is a popular way to go, and also much faster than row level security - depending on your data size.