Designing a Permissions System in C# and SQL

Question

0.00/5 (No votes)

See more:

I'm currently working on a data storing project where report documents are separated into folders on a treeview control.

I need to design a way so that different user groups can only see or access treeview control nodes that they have permissions for.

For example lets say we have 3 Usergroups and 4 Folders.

Usergroup 1 has access to all folders.
Usergroup 2 has access to folders 2 and 3
Usergroup 3 has access to folder 4.

How would I design the SQL side to reflect a system like this?

I originally thought it would work by having a Folder permissions table with a string like follows:

FolderID         |      Permissions

Folder 1         |      Usergroup1,Usergroup2

When the treeview selects the nodes from the folder it would separate the permissions string by the "," and checking if user A is in that group if so display otherwise not make it visible.

Is there a better way? preferably with Read,Write,Copy and delete privileges too?
[EDIT]
Also how would I have users in multiple groups? the solution needs to be as small as possible since there will be a huge amount of groups, 4 per building, 100 buildings etc with potentially limitless folders.

Posted 16-Nov-11 23:43pm

Phoenix234

Updated 17-Nov-11 2:35am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rob Philpott · Accepted Answer · 2011-11-17T01:14:00

My suggestion would be: create a table [User] which has individual users, create a table [Group] which holds the groups, create a table [GroupUser] which is a junction table defining which users are in which groups. As such, two columns [GroupId] and [UserId], both part of the primary key.

Create a table [FolderPermissions] which has a column detailing the folder, a column detailing the group, and four [bit] columns denoting the Read, Write, Copy and Delete permissions.

I'm presuming the the folders are in a hierarchy so that a group with permission to the root folder '/' or '\' has permission on all subfolders, unless explicity removed with another row in the [FolderPermissions] table.

That's the schema, then you need to write some procs. Unless the folder hierarchy is held in the database you probably need to make multiple db calls as you recurse up/down the tree.

Poobalan4 · Accepted Answer · 2011-11-17T01:43:00

Solution 2

Quote:
UserGroupId | UserGroupName
1| Group1
2| Group2
3| Group3

Quote:
FolderId | FolderName
1| Folder1
2| Folder2
3| Folder3
4| Folder4

Quote:
SetAccessID |UserGroupID |FolderID |IsReadable| IsWritable
1| 1| 1| TRUE| TRUE
2| 1| 2| TRUE| FALSE
3| 1| 3| TRUE| TRUE
4| 1| 4| TRUE| FALSE
5| 2| 2| TRUE| TRUE
6| 2| 3| TRUE| FALSE
7| 3| 4| TRUE| TRUE

Create 3 tables as above..Set Respective Primary and Foreign Key Constraints.
Use 3rd table to give permission like read,write,copy,delete..

Posted 17-Nov-11 1:43am

Poobalan4

Updated 17-Nov-11 2:03am

v2

Comments

Phoenix234 17-Nov-11 8:33am

So when I fill the treeview Id select the username, get the usergroup and run it against the 3rd table? if the usergroup has read access show the folder otherwise skip and move to the next node?

Sounds perfect, Ill attempt to implement it in a bit.

Although why do we need the SetAccessID on the 3rd table? and is it possible to have multiple usergroups on 1 user?

I worry that this method will lead to a huge database, since there will be at least 4 folders for each department and there are 100 buildings as well as folders been limitless potentially.

is there a shorter way of handling it?

Poobalan4 18-Nov-11 3:26am

Its good to have a Primary Key on each table(Must in Entity framework)..Thats why the SetAccessId.. In User table itself relate the usergroup,and you can manage the user to two usergroups based on the usergroup table design..

Phoenix234 18-Nov-11 5:11am

Ah I see, out of curiosity is this possible?

When I load the treeview control I would check a users group and if that group is set to true for permissions to that folder, if that group doesnt have a true value or any value at all for that folder it defaults to false?

This way instead of needing permissions for all tables for each group they all default to false unless I specifically say that the group has permission. so if 1 group has access to 1 folder out of 5 it only needs 1 permission row instead of 5?

Poobalan4 18-Nov-11 6:51am

[This way instead of needing permissions for all tables for each group they all default to false unless I specifically say that the group has permission. so if 1 group has access to 1 folder out of 5 it only needs 1 permission row instead of 5?]
-- Yes..That I mentioned in the beginning itself... Usergroup 3 has access to folder 4 and that is set in 3 rd table..