by Ali_Khalili

Implement row level security in C# and entity framework

Introduction

In this article we are going to implement row level security, its solution makes our application refactoring based and this feature let us manage project easily.

and this way will reduce your business code and if you use DDD can significantly reduce codes in services.

overall there are various ways for implementing this solution but one of the easiest ways for this task is to write usable interfaces and using its validation on your generic repository.

Background

assume that we have two models, “User” and “Post”. Now, I want to create a generic repository that has professional functionality.

its repository can automatically select Posts records of expected User plus if that user is an admin, should see all posts. 

we can easily implement this feature using row level security in our custom repository.

Using the code

Now, we start coding, I use visual studio ide and I’m going to create Console Application that named “Console1” and it would be something like below

First, we need download entity framework using NuGet packages manager.

after that create a folder named “Models” and inside that create a class named “User”.

and it would be something like this

 

using System;

namespace Console1.Models
{
    public enum UserType
    {
        Admin,
        Ordinary
    }
    public class User
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public int Age { get; set; }

        public UserType Type { get; set; }

    }
}

I think UserType is completely obvious and clear, it’s just an enum that makes a role in this entity.

and another field is rational too.

And now, let’s implement “IUser” interface, we create that in Models folder and it would be like this

 

namespace Console1.Models
{
    public interface IUser
    {
        int UserId { get; set; }

        User User { get; set; }
    }
}

each entity has a relation to User must implement IUser interface.

now create another class named “Post” in this folder too and codes there…

 

using System.ComponentModel.DataAnnotations.Schema;

namespace Console1.Models
{
    public class Post : IUser
    {
        public int Id { get; set; }

        public string Context { get; set; }

        public int UserId { get; set; }

        [ForeignKey(nameof(UserId))]
        public User User { get; set; }

    }
}

its relation is one to many that means each user can have N posts.

it’s enough and now we want to introduce these classes to EF context, So we create a class named “Context” in Models folder.

 

using System.Data.Entity;

namespace Console1.Models
{
    public class Context : DbContext
    {
        public Context() : base("Context")
        {
        }

        public DbSet Users { get; set; }

        public DbSet Posts { get; set; }
    }
}    

we specified clearly that we have two Dbset from Type Post and User so, it means we would have two tables in our database, and of course, our database name will be “Context”.

Till here we have finished out the first step in this project, and now we can create a database using Migration and add some initial records to it.

So, in Package Manager Console write “Enable-migrations”

it will automatically create a folder named “Migrations” and a class named “Configuration.cs” inside that.

now change Configuration.cs like below

 

namespace Console1.Migrations
{
    using Models;
    using System.Data.Entity.Migrations;

    internal sealed class Configuration : DbMigrationsConfiguration
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

        protected override void Seed(Console1.Models.Context context)
        {

            context.Users.AddOrUpdate(x => x.Id,
              new User { Id = 1, Name = "aaa", Age = 30, Type = UserType.Admin },
              new User { Id = 2, Name = "bbb", Age = 20, Type = UserType.Ordinary },
              new User { Id = 3, Name = "ccc", Age = 25, Type = UserType.Ordinary }
            );

            context.Posts.AddOrUpdate(x => x.Id,
                new Post { Context = "ccc 1", UserId = 3 },
                new Post { Context = "bbb 1", UserId = 2 },
                new Post { Context = "bbb 2", UserId = 2 },
                new Post { Context = "aaa 1", UserId = 1 },
                new Post { Context = "bbb 3", UserId = 2 },
                new Post { Context = "ccc 2", UserId = 3 },
                new Post { Context = "ccc 3", UserId = 3 }
            );

            context.SaveChanges();
        }
    }
}

in “Seed” method we tell that these are our initial records (it’s just for instance)

in your console type “Update-database” and send this command, a database will create automatically with these initial records.

absolutely above items are clear and writing that has caused of we need that in the remainder of this article.

when we create Repository we need all above items.

and now I wanna start the main part of this article, that is a supported row level security repository.

in the root of the project create a folder named “Repository” and inside that create a class named “GenericRepository”.

your project should have structural like below

and implement Generic Repository like this

 

using Console1.Models;
using System;
using System.Linq;
using System.Linq.Dynamic;
using System.Linq.Expressions;

namespace Console1.Repository
{
    public interface IGenericRepository<T>
    {
        IQueryable<T> CustomizeGet(Expression<Func<T, bool>> predicate);
        void Add(T entity);
        IQueryable<T> GetAll();
    }

    public class GenericRepository<TEntity, DbContext> : IGenericRepository<TEntity>
        where TEntity : class, new() where DbContext : Models.Context, new()
    {

        private DbContext _entities = new DbContext();

        public IQueryable<TEntity> CustomizeGet(Expression<Func<TEntity, bool>> predicate)
        {
            IQueryable<TEntity> query = _entities.Set<TEntity>().Where(predicate);
            return query;
        }

        public void Add(TEntity entity)
        {
            int userId = Program.UserId; // fake UserId

            if (typeof(IUser).IsAssignableFrom(typeof(TEntity)))
            {
                ((IUser)entity).UserId = userId;
            }

            _entities.Set<TEntity>().Add(entity);
        }

        public IQueryable<TEntity> GetAll()
        {
            IQueryable<TEntity> result = _entities.Set<TEntity>();

            int userId = Program.UserId; // fake UserId


            if (typeof(IUser).IsAssignableFrom(typeof(TEntity)))
            {
                User me = _entities.Users.Single(c => c.Id == userId);
                if (me.Type == UserType.Admin)
                {
                    return result;
                }
                else if (me.Type == UserType.Ordinary)
                {
                    string query = $"{nameof(IUser.UserId).ToString()}={userId}";

                    return result.Where(query);
                }
            }
            return result;
        }
        public void Commit()
        {
            _entities.SaveChanges();
        }
    }
}

Description of above codes:

1) we have a generic interface named IGenericRepository, GenericRepository class will implement that.

2) interface includes a method such as CustomizeGet that has a predicate in this parameter and only return it’s queryable (it’s just for instance and not include in this article) and “Add” plus “GetAll” method that should be done our row level security target directly.

3) GenericRepositoryClass has taken two generic type and it’s class implements IGenericRepository, and obviously have declared TEntity is a class and DbContext is a type of Context that we have implemented before.

4) you see CustomizeGet implementation that has created a related query and return that.

5) “Add” method has implemented with a parameter with type TEntity(a model should save), after that you see I assigned a number to UserId with hard code way, of course you know for this job you should use something like Asp.Net Identity if you develop web application and return an authenticated userId that has claimed before .

and with IsAssignableFrom we have specified that whether TEntity is an IUser or not! if the condition is true we will add UserId to TEntity and for instance, in your services you don’t need to add this repetitive field and in next level we will add this to TEntity.

6) Arrived at the most interesting is the method that named “GetAll”, First we create a query of that entity and in the next level specify that whether is an IUser or not! if the condition goes true, it will find intended user and if it’s type be an admin, the method return all collection(Admin access all posts) and if user’s type be “Ordinary”, by using dynamic linq will create intended query and make a condition that UserId should equal userId. In this scenario, an ordinary user will see just all his/her posts.

Note: for downloading dynamic linq you can search “Dynamic.linq” in nuget package manager.

And if TEntity wouldn’t IUser we will return the result, So that means we don’t want to make row level security in a slightly model and created query has the ability to return all records.

7) obviously do save operation in the database.

We have created some initial records in “Seed”, now we want to run and test all these processes. we should open “Program.cs” in the root of the project and implement this like this:

 

using System;
using Console1.Models;
using Console1.Repository;
using System.Collections.Generic;
using System.Linq;

namespace Console1
{
    public class Program
    {
        public static int UserId = 1; //fake userId
        static void Main()
        {
            GenericRepository repo = new GenericRepository();

            List posts = repo.GetAll().ToList();

            foreach (Post item in posts)
                Console.WriteLine(item.Context);

            Console.ReadKey();
        }
    }
}

As you see UserId is hard coded and we can change that in statically. and something that has created in the database is for instance UserId = 1 equals Admin and other users have an ordinary type.

In the Main method we have created an instance of GenericRepository and by using “GetAll” method and list that, return all intended records and print that.

we expect a userId equals one, return all posts record cause the user is an admin.

And now is sufficient to change UserId statically, and for example change it to 2 and run the project, the operation will change and you will see an ordinary user just can see it’s posted only.

and you should see something like this

you see just with changing UserId behavior of our application completely will change and would return  just user’s posts.

Conclusion

The same way you can implement and design complex repository that can handle and manage a variety of validations.

You can download all codes in my Github account.