用C#和SQL设计权限系统 [英] Designing a Permissions System in C# and SQL
问题描述
我目前正在一个数据存储项目中，在该项目中，报表文档被分为树视图控件上的文件夹.
我需要设计一种方法，以便不同的用户组只能查看或访问他们拥有权限的树视图控制节点.
例如，假设我们有3个用户组和4个文件夹.
用户组1有权访问所有文件夹.
用户组2有权访问文件夹2和3
用户组3有权访问文件夹4.
我将如何设计SQL端来反映这样的系统?
我本来以为它可以通过具有如下所示字符串的Folder权限表来工作:
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
当树视图从文件夹中选择节点时，它将用，"分隔权限字符串，并检查用户A是否在该组中(如果显示)，否则显示不可见.
有没有更好的办法?最好还具有读取，写入，复制和删除权限?
另外，我将如何将用户分为多个组?解决方案的规模应尽可能小，因为将有大量的组，每个建筑物4个，建筑物100个，等等，并可能包含无限的文件夹.
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?
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.
推荐答案
我的建议将是:创建一个具有单个用户的表[User]，创建一个包含组的表[Group]，创建一个表[GroupUser]，这是一个定义了哪些用户属于哪些组的联结表.这样，两列[GroupId]和[UserId]都是主键的一部分.
创建一个表[FolderPermissions]，该表具有详细说明文件夹的列，详细说明组的列以及表示读取，写入，复制和删除权限的四个[位]列.
我假设文件夹位于层次结构中，以便具有根文件夹"/"或"\"权限的组对所有子文件夹均具有权限，除非在[FolderPermissions]中用另一行明确删除了该文件夹表.
这就是架构，然后您需要编写一些proc.除非文件夹层次结构保留在数据库中，否则您可能需要在向上/向下递归树时进行多个数据库调用.
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.
UserGroupId | UserGroupName
1 |第1组
2 |第2组
3 |第3组
UserGroupId | UserGroupName
1| Group1
2| Group2
3| Group3
FolderId | FolderName
1 |文件夹1
2 | Folder2
3 | Folder3
4 | Folder4
FolderId | FolderName
1| Folder1
2| Folder2
3| Folder3
4| Folder4
SetAccessID | UserGroupID |文件夹ID | IsReadable | IsWritable
1 | 1 | 1 | TRUE | TRUE
2 | 1 | 2 | TRUE |假
3 | 1 | 3 | TRUE | TRUE
4 | 1 | 4 | TRUE |假
5 | 2 | 2 | TRUE | TRUE
6 | 2 | 3 | TRUE |假
7 | 3 | 4 | TRUE | TRUE
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
如上所述创建3个表.设置相应的主键和外键约束.
使用第三张表授予读，写，复制，删除之类的权限.
Create 3 tables as above..Set Respective Primary and Foreign Key Constraints.
Use 3rd table to give permission like read,write,copy,delete..
这篇关于用C#和SQL设计权限系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
