处理多个权限类型的最佳方式是什么?

I often encounter the following scenario where I need to offer many different types of permissions. I primarily use ASP.NET / VB.NET with SQL Server 2000.

我经常遇到以下场景，需要提供许多不同类型的权限。我主要使用ASP。净/ VB。NET与SQL Server 2000。

Scenario

场景

I want to offer a dynamic permission system that can work on different parameters. Let's say that I want to give either a department or just a specific person access to an application. And pretend that we have a number of applications that keeps growing.

我想提供一个动态权限系统，可以处理不同的参数。假设我想给一个部门或者一个特定的人访问一个应用程序。假设我们有很多不断增长的应用。

In the past, I have chosen one of the following two ways that I know to do this.

在过去，我选择了以下两种方法中的一种。

1) Use a single permission table with special columns that are used for determining a how to apply the parameters. The special columns in this example are TypeID and TypeAuxID. The SQL would look something like this.

1)使用单独的权限表，其中包含用于确定如何应用参数的特殊列。本例中的特殊列是TypeID和TypeAuxID。SQL应该是这样的。

SELECT COUNT(PermissionID)
FROM application_permissions
WHERE
(TypeID = 1 AND TypeAuxID = @UserID) OR
(TypeID = 2 AND TypeAuxID = @DepartmentID)
AND ApplicationID = 1

2) Use a mapping table for each type of permission, then joining them all together.

2)为每种类型的权限使用一个映射表，然后将它们连接在一起。

SELECT COUNT(perm.PermissionID)
FROM application_permissions perm
LEFT JOIN application_UserPermissions emp
ON perm.ApplicationID = emp.ApplicationID
LEFT JOIN application_DepartmentPermissions dept
ON perm.ApplicationID = dept.ApplicationID
WHERE q.SectionID=@SectionID
  AND (emp.UserID=@UserID OR dept.DeptID=@DeptID OR
 (emp.UserID IS NULL AND dept.DeptID IS NULL)) AND ApplicationID = 1
ORDER BY q.QID ASC

My Thoughts

我的思想

I hope that the examples make sense. I cobbled them together.

我希望这些例子有意义。我拼凑在一起。

The first example requires less work, but neither of them feel like the best answer. Is there a better way to handle this?

第一个例子需要较少的工作量，但他们都不认为这是最好的答案。有更好的方法来处理这个问题吗?

5 个解决方案

#1

I agree with John Downey.

我同意约翰·唐尼的观点。

Personally, I sometimes use a flagged enumeration of permissions. This way you can use AND, OR, NOT and XOR bitwise operations on the enumeration's items.

就我个人而言，我有时使用一个标记的权限枚举。通过这种方式，您可以对枚举的项使用AND、NOT和XOR位操作。

"[Flags]
public enum Permission
{
    VIEWUSERS = 1, // 2^0 // 0000 0001
    EDITUSERS = 2, // 2^1 // 0000 0010
    VIEWPRODUCTS = 4, // 2^2 // 0000 0100
    EDITPRODUCTS = 8, // 2^3 // 0000 1000
    VIEWCLIENTS = 16, // 2^4 // 0001 0000
    EDITCLIENTS = 32, // 2^5 // 0010 0000
    DELETECLIENTS = 64, // 2^6 // 0100 0000
}"

Then, you can combine several permissions using the AND bitwise operator.

For example, if a user can view & edit users, the binary result of the operation is 0000 0011 which converted to decimal is 3.
You can then store the permission of one user into a single column of your DataBase (in our case it would be 3).

Inside your application, you just need another bitwise operation (OR) to verify if a user has a particular permission or not.

然后，可以使用和位运算符合并几个权限。例如，如果用户可以查看和编辑用户，操作的二进制结果是00000011，转换为decimal是3。然后，您可以将一个用户的权限存储到数据库的单个列中(在我们的例子中是3)。在应用程序中，您只需要另一个位操作(或)来验证用户是否具有特定的权限。

#2

The way I typically go about coding permission systems is having 6 tables.

我通常使用的编码权限系统有6个表。

Users - this is pretty straight forward it is your typical users table
用户——这是非常直接的，它是您的典型用户表。
Groups - this would be synonymous to your departments
团体-这将是你的部门的同义词
Roles - this is a table with all permissions generally also including a human readable name and a description
角色——这是一个具有所有权限的表，通常还包括一个人类可读的名称和描述。
Users_have_Groups - this is a many-to-many table of what groups a user belongs to
Users_have_Groups——这是用户所属的多对多组的表
Users_have_Roles - another many-to-many table of what roles are assigned to an individual user
users_ve _roles—为单个用户分配角色的另一个多对多表。
Groups_have_Roles - the final many-to-many table of what roles each group has
Groups_have_Roles——每个组拥有哪些角色的最终多对多表

At the beginning of a users session you would run some logic that pulls out every role they have assigned, either directory or through a group. Then you code against those roles as your security permissions.

在用户会话的开始，您将运行一些逻辑，提取它们分配的每个角色，无论是目录还是组。然后将这些角色编码为安全权限。

Like I said this is what I typically do but your millage may vary.

就像我说的，这是我通常做的但你的磨耗可能会不同。

#3

In addition to John Downey and jdecuyper's solutions, I've also added an "Explicit Deny" bit at the end/beginning of the bitfield, so that you can perform additive permissions by group, role membership, and then subtract permissions based upon explicit deny entries, much like NTFS works, permission-wise.

除了John Downey和jdecuyper的解决方案之外，我还在位字段的末尾/开头添加了一个“显式拒绝”位，这样您就可以按组、角色成员执行附加的权限，然后根据显式拒绝条目减去权限，就像NTFS工作的权限一样。

#4

Honestly the ASP.NET Membership / Roles features would work perfectly for the scenario you described. Writing your own tables / procs / classes is a great exercise and you can get very nice control over minute details, but after doing this myself I've concluded it's better to just use the built in .NET stuff. A lot of existing code is designed to work around it which is nice at well. Writing from scratch took me about 2 weeks and it was no where near as robust as .NETs. You have to code so much crap (password recovery, auto lockout, encryption, roles, a permission interface, tons of procs, etc) and the time could be better spent elsewhere.

诚实的ASP。NET成员资格/角色特性对于您所描述的场景将非常有效。编写自己的表/ procs /类是一个很好的练习，您可以很好地控制微小的细节，但是在我完成这些之后，我得出结论，最好使用内建的。net内容。许多现有的代码都是围绕它设计的，这是很好的。我花了大约两周的时间从头开始写作，但这远没有。net那么强大。您必须编写如此多的垃圾(密码恢复、自动锁定、加密、角色、权限接口、大量的proc等)，而在其他地方花费的时间可能更好。

Sorry if I didn't answer your question, I'm like the guy who says to learn c# when someone asks a vb question.

对不起，如果我没有回答你的问题，我就像一个人说要学习c#当有人问vb的问题。

#5

An approach I've used in various applications is to have a generic PermissionToken class which has a changeable Value property. Then you query the requested application, it tells you which PermissionTokens are needed in order to use it.

我在各种应用程序中使用的一种方法是拥有具有可变值属性的通用PermissionToken类。然后查询所请求的应用程序，它会告诉您要使用哪个permissiontoken。

For example, the Shipping application might tell you it needs:

例如，航运应用程序可能会告诉您它需要:

new PermissionToken()
{
    Target = PermissionTokenTarget.Application,
    Action = PermissionTokenAction.View,
    Value = "ShippingApp"
};

This can obviously be extended to Create, Edit, Delete etc and, because of the custom Value property, any application, module or widget can define its own required permissions. YMMV, but this has always been an efficient method for me which I have found to scale well.

这显然可以扩展为创建、编辑、删除等等，因为自定义值属性，任何应用程序、模块或小部件都可以定义它自己所需的权限。但对于我来说，这一直是一种有效的方法。

#1