How to write the the access control

Question

How can I write the access control algorithm or helper classfor below conditions.

I have following design:

Table Customer (Id, Name...)
Table User (Id, Username, Name, Manager (UserId - from this User Table))
Table UserCustomer (UserId, CustomerId)

Only User or User's Manager assigned to Customers can see the Customer.

It should be like this.

User1 has Manager UserM1
User2 has Manager UserM2
User3 has Manager UserM3
UserM1 has Manager UserMM1
UserM2,M3 has Manager UserMM2
UserMM1, UserMM2 has Manager UserMMM
UserMMM can have its Manager as well and so and so.

Note: Recursive relationship of User and User's Manager could be deep like 10 levels.

So technically, any customers assigned to the User under the User Manager can see the client.

How can I write this dynamic condition in C#.

======= Additonal explaination ========

I have 10 clients = I can only view 10 clients
Pogba have 5 clients = He can view 5 clients.
Paul is my manager and he also has 5 clients = He can view 15 clients
Logan is Pual Manager and he also has 10 clients = He can view 25 clients (Me, Paul, and Logan clients)
Henry is Pogba and Logan Manager = He can view 30 clients (25 client from Logan and Logan's staff + 5 clients from Pogba )

so and so.

My attempted alogoritm but it could only accomulate 2 level deep.

Im going to create helper class. (CustomerAccessControlHelper.cs)

  public static class CustomerAccessControlHelper.cs
    {
    public static List<Customer> GetAccessClients(int userId)
{

var userListsUnderCurrentUser = new List<User>();

 var listOfAssignedStaffsLevel1 = _context.Users.Where(x => x.ManagerId == userId);
userListsUnderCurrentUser.AddRange(listOfAssignedStaffsLevel1);

foreach(var user in listOfAssignedStaffs)
{
   var listOfAssignedStaffsLevel2 =  _context.Users.Where(x => x.ManagerId == user.Id);
   userListsUnderCurrentUser.AddRange(listOfAssignedStaffsLevel1);

}

}
    
    }

Please [edit] your question to include what research you've done and any attempts you've made to solve the issue yourself. — Heretic Monkey, Jul 07 '21 at 14:26
@HereticMonkey. If I could sovle it myself. why do i need to post on stackoverflow. — Sras, Jul 07 '21 at 14:45
I didn't say, "post your solution". I said "any **atempts** you've made to solve the issue". — Heretic Monkey, Jul 07 '21 at 14:47
@MrMoeinM there would be around 500 users. the level could be up to 10 hiarical level — Sras, Jul 08 '21 at 01:21

MrMoeinM · Accepted Answer · 2021-07-08T10:12:37.620

2

Of course you can recursively query database and find all users under a manager and then find all clients he can access. but it's not optimized solution.

Another way is to load every users in singleton or static list and create tree in memory.

But I suggest you use another column to your table and keep hierarchy of managers. This is called path enumeration.

Id	Username	ManagerId	ManagerHierarchy
1	User1	4	/0/9/7/4/
2	User2	5	/0/9/8/5/
3	User3	6	/0/9/8/6/
4	UserM1	7	/0/9/7/
5	UserM2	8	/0/9/8/
6	UserM3	8	/0/9/8/
7	UserMM1	9	/0/9/
8	UserMM2	9	/0/9/
9	UserMMM	NULL	/0/

now imagine we want list of clients that UserMMM can see.

var userId = 9;
dbContext.Customers.Where(item => item.UserCustomers.Any(uc => uc.User.ManagerHierarchy.Contains($"/{userId}/") || uc.UserId == userId))

I encourage you to take a look at this link https://www.databasestar.com/hierarchical-data-sql/

For keeping ManagerHierarchy updated you need to calculate it's value when you add, edit or delete a user.

Add

var managerId = 1;
var manager = dbContext.Users.First(item => item.id == managerId);
var user = new User()
{
    ManagerHierarchy = $"{manager.ManagerHierarchy}{managerId}/",
    ManagerId = managerId
};

Delete

var userIdToDelete = 1;
var usersHasThisUserAsManager = dbContext.Users.Where(item => item.ManagerHierarchy.Contains($"/{userIdToDelete}/").ToList();
foreach(var u in usersHasThisUserAsManager)
    u.ManagerHierarchy = u.ManagerHierarchy.Replace($"/{userIdToDelete}/","/");

Edit ManagerId

var user =; // Some user you get from database and need to update it's managerId column
var newManagerId = 1;
var newManager = dbContext.Users.First(item => item.id == managerId);

var usersNeedToUpdateManagerHierarchy = dbContext.Users.Where(item => item.ManagerHierarchy.StartsWith($"{user.ManagerHierarchy}{user.Id}/").ToList();

foreach(var u in usersNeedToUpdateManagerHierarchy)
    u.ManagerHierarchy = u.ManagerHierarchy.Replace($"{user.ManagerHierarchy}{user.Id}/",$"{newManager.ManagerHierarchy}{user.Id}/")

edited Jul 08 '21 at 10:12

answered Jul 08 '21 at 08:37

MrMoeinM

2,080
1
11
16

how can we keep the ManagerHierachy in sync and up to date. – Sras Jul 08 '21 at 09:05
@Sras You need to update ManagerHierarchy when you add/delete/change managerId of a user. I updated my answer. – MrMoeinM Jul 08 '21 at 10:14
so. every time, we update the manager of individual user. all users hierarchy will be updated as well – Sras Jul 09 '21 at 06:03
could you please how do we define class and navigation property for User class – Sras Jul 09 '21 at 06:08
public class User { public int Id { get; set; } public string Name { get; set; } public User Manager { get; set; } public ICollection ManagerHierachy { get; set; } } – Sras Jul 09 '21 at 06:20
@Sras ManagerHierachy is simple string (In database varchar). Unfortunately we can not have foreign key here. e.g. according to table ManagerHierarchy for User1 is "/0/9/7/4/". So public string ManagerHierarchy {get; set;} – MrMoeinM Jul 09 '21 at 07:39
@Sras Not exactly. e.g. if you update the manager of UserM1 in the table above only ManagerHierarchy of UserM1 and User1 will be updated. – MrMoeinM Jul 09 '21 at 07:47
@as default Idnetity of Asp.net is string. should i change to int for better performance. or no – Sras Jul 09 '21 at 07:53
@Sras https://stackoverflow.com/questions/24923470/identity-2-0-string-or-int-primary-key – MrMoeinM Jul 09 '21 at 08:24
this is good article. im changing to int data type now. and my issue is migraiton cant be applied. maybe i need to remove all migrations i had – Sras Jul 09 '21 at 08:27
@Sras Unfortunately I'm not very familiar with ef migration. If you search and didn't find any thing I suggest you open another question. – MrMoeinM Jul 09 '21 at 08:35
i will try and test your answer – Sras Jul 09 '21 at 08:44
what happend, if user A under user B, user B under user C, user C under user A. it should not be like this. but how to prevent it – Sras Jul 09 '21 at 14:08
@Sras you have hierarchy of managers for each user. Just check if $"/{C.Id}/" exist as part of ManagerHierarchy of user A then do not accept A as manager of C. – MrMoeinM Jul 09 '21 at 14:32
can you elaborate how it works . im a bit confused – Sras Jul 09 '21 at 14:36
Imagine , user A is manager , user B is under user A , user C is under user B, somehow , user C is promoted above user A - then user A is under user C. – Sras Jul 09 '21 at 16:33
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234701/discussion-between-mrmoeinm-and-sras). – MrMoeinM Jul 09 '21 at 17:24

How to write the the access control

1 Answers1