locked
Role-based security at data level RRS feed

  • Question

  • User-1043024286 posted

    I have a situation where I want to keep one group of users from having access to certain data in my SQL Database. To explain myself further, suppose I have a database of cars, and there are Toyotas and Fords, but I don't want the Ford people to be able to see information about the Toyotas, and Vice-Versa.

    So I have an Admin Role and a Power User Role that can see all of the cars.

    What I'd like to do is have a Toyota User Role and a Ford User Role, and keep the information in the database from being displayed to their respective users. I have control over the database, so I could, as an example, add a column for controlling this if necessary.

    I'm just looking for a high-level approach to this scenario that others may have used, or some idea of how to implement something that makes sense.

    Thanks!

    ...forgot to mention the important part! I'm using .NET Core 2.2 Razor Pages with Identity Core.

    Wednesday, May 29, 2019 12:22 PM

Answers

  • User-474980206 posted
    You looking for row level security. See

    https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017

    If you just have a couple roles, then a view dedicated to each role would make sense.

    If you are using ef google for rls or tenant examples.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 29, 2019 2:20 PM

All replies

  • User475983607 posted

    I have a situation where I want to keep one group of users from having access to certain data in my SQL Database. To explain myself further, suppose I have a database of cars, and there are Toyotas and Fords, but I don't want the Ford people to be able to see information about the Toyotas, and Vice-Versa.

    So I have an Admin Role and a Power User Role that can see all of the cars.

    What I'd like to do is have a Toyota User Role and a Ford User Role, and keep the information in the database from being displayed to their respective users. I have control over the database, so I could, as an example, add a column for controlling this if necessary.

    I'm just looking for a high-level approach to this scenario that others may have used, or some idea of how to implement something that makes sense.

    Thanks!

    ...forgot to mention the important part! I'm using .NET Core 2.2 Razor Pages with Identity Core.

    I would use policy based authorization.

    https://docs.microsoft.com/en-us/aspnet/core/security/authorization/policies?view=aspnetcore-2.2

     

    Wednesday, May 29, 2019 12:37 PM
  • User-474980206 posted
    You looking for row level security. See

    https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017

    If you just have a couple roles, then a view dedicated to each role would make sense.

    If you are using ef google for rls or tenant examples.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 29, 2019 2:20 PM
  • User-1043024286 posted

    I'm not sure policy-based security is the answer. Let me give a specific example:

    Suppose I'm returning a List<Car> from a method. With a Role Based or a Policy Based Authorization, I can determine only if the method is allowed or not, based on the role or policy...at least that's my understanding.

    I'm sure I could just iterate through the list of cars, and test each one against the role the logged in user, but I was hoping to find a solution that was easier to manage than that. 

    Wednesday, May 29, 2019 2:30 PM
  • User-1043024286 posted

    You looking for row level security. See

    https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017

    If you just have a couple roles, then a view dedicated to each role would make sense.

    If you are using ef google for rls or tenant examples.

    I think this is what I'm looking for. Thanks!

    Wednesday, May 29, 2019 2:36 PM