如何将 .net 成员角色分配给各个数据库记录
我正在开发一个系统，我们希望根据用户的角色限制向用户显示的信息的可用性。
例如，我有一个名为 EventType (ID, EventTypeDescription) 的表格 其中包含以下记录：
1, 'Basic Event'
2, 'Intermediate Event'
3, 'Admin Event'
我需要实现的是根据登录用户的用户名（以及角色）过滤返回的记录。例如，如果高级用户登录，他们将看到所有事件类型，如果标准用户登录，他们将仅看到基本事件类型等。
理想情况下，id 喜欢以可以轻松扩展到其他表的方式执行此操作如有必要。因此，我想避免简单地向每个数据对用户上下文敏感的表添加“角色”字段。
我正在考虑的一个想法是创建某种权限表，例如：
PermissionsTable
(
ID,
Aspnet_RoleId,
TableName,
PrimaryKeyValue
)
这有一个使用它的缺点，显然必须使用表名来切换要加入的表。
编辑： 在没有任何更好的建议的情况下，我将采用我提到的最后一个想法，但不是使用 TableName 字段，而是将 TableName 规范化为它自己的表，如下所示：
TableNames
(
ID,
TableName
)
UserPermissionsTable
(
ID,
Aspnet_UserId,
TableID,
PrimaryKeyValue
)
I'm developing a system where we want to restrict the availability of information displayed to users based on their roles.
e.g. I have a tabled called EventType (ID, EventTypeDescription)
which contains the following records:
1, 'Basic Event'
2, 'Intermediate Event'
3, 'Admin Event'
What I need to achieve is to filter the records returned based on the username (and hence role) of the logged-in user. e.g if an advanced user is logged in they will see all the event types, if the standard user is logged in they will only see the basic event type etc.
Ideally id like to do this in a way which can be easily extended to other tables as necessary. So I'd like to avoid simply adding a 'Roles' field to each table where the data is user context sensitive.
One idea I'm thinking of is to create some kind of permissions table like:
PermissionsTable
(
ID,
Aspnet_RoleId,
TableName,
PrimaryKeyValue
)
this has the drawback of using this is obviously having to use the table name to switch which table to join onto.
Edit:
In the absence of any better suggestions, I'm going to go with the last idea I mentioned, but instead of having a TableName field, I'm going to normalise the TableName out to it's own table as follows:
TableNames
(
ID,
TableName
)
UserPermissionsTable
(
ID,
Aspnet_UserId,
TableID,
PrimaryKeyValue
)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我们做了类似的事情，我们的解决方案是使用表值函数来连接。
IE。
从事件 e 中选择 *
inner join [dbo].fn_AvailableEvents(@User_ID) a on e.id = a.id
该函数仅返回允许用户查看的事件的事件 ID。
We do something similar, Our solution was to use a table valued function to join against.
ie.
select * from events e
inner join [dbo].fn_AvailableEvents(@User_ID) a on e.id = a.id
the function only returns the event id of the events the user is allowed to see.
我在构建 CMS 时做了类似的事情......
本质上我在数据库中创建了一些表：
用户
角色
角色中的用户
对象
ObjectPermissions
好的，它的工作原理是这样的...
前 3 个非常不言自明，即用户、角色以及它们之间的链接。
用户根据其角色成员资格被授予权限。
我要做的下一件事是定义我想要控制权限的“对象”以及我想要分配给它们的权限级别...
因此对象包含对象定义的列表，然后在相关的其他表中扩展这些对象定义对象 id 和 ObjectPermissions 表基本上将对象链接到角色。
现在，可能有必要解释一些有关角色的内容……
我认为角色是一个权限列表，仅此而已。
因此，如果我创建一个名为 guest 的角色并将该角色设置为允许读取权限，然后创建一个名为 admin 的角色，该角色具有执行所有操作的全局权限，然后执行类似这样的操作...
添加用户 1 管理员角色。
将对象 1 添加到管理员角色。
用户 1 现在可以完全访问对象 1，这里重要的是权限是继承的，因此任何子对象（例如文件和文件夹权限）也将位于相同的角色集中，除非递归覆盖。
因此，默认情况下，我将根级别对象分配给每个关键系统角色。
然后，我有选择地将用户添加到树中不同点的各种角色。
这有道理吗？
本质上，我可以选择任何对象和任何用户，并通过将用户添加到与对象相同的角色来向用户授予该对象及其子对象的不同程度的权限。
现在有一些需要注意的事情......
如果我希望用户对深层嵌套对象拥有管理员权限，但不在根级别，我将必须创建一个授予所有权限的新角色，并添加用户和反对它。
原因是，如果我将用户添加到主管理角色，用户将从根级别向上获得这些权限，而不是从我的嵌套对象获得这些权限。
事情远不止这些，但本质上这就是文件系统权限的工作原理。
I did something similar whilst building a CMS ...
Essentially i created a few tables in the db:
Users
Roles
UsersInRoles
Objects
ObjectPermissions
Ok it works something like this ...
The first 3 are pretty self explanitory, users, roles and the links between them.
A user is granted permissions based on their membership to roles.
The next thing i do is define the "objects" i want to control the permissions to and the permission levels i want to assign them ...
So objects contains a list of the object definitions which are then extended in other tables that are related by object id and the ObjectPermissions table basically links an object to a role.
Now at this point it might be worth explaining something about roles ...
A role as i see it is a list of permissions, nothing more and nothing less.
So if i create a role called guest and set the role to allow the read permission then create a role called admin which has global rights to do everything i can then do something like this ...
Add user 1 Admin role.
Add Object 1 to Admin role.
User 1 would now have full access to object 1 and the important thing here is that permissions are inherited so any child objects (think file and folder permissions) would also be in the same role set unless overridden recursively.
so by default i assign the root level object to every key system role.
I then selectively add users to various roles at different points in the tree.
Does this make sense?
Essentially I can choose any object and any user and grant a varying degree of permissions to the user on that object and its children by adding the user to the same role as the object.
Now there's some things to note about this ....
If i wanted a user to have admin permissions on a deeply nested object but not at root level I would have to create a new role that grants all permissions and add both the user and the object to it.
The reason being that if i added the user to the main admin role the user would be given those rights from root level up not from my nested object.
theres more to it than this but essentially this is how file system permissions work.
您没有提及您正在使用的数据库（如果有），但假设使用 SQL Server，那么如果您使用 Windows 身份验证进行连接，您可以创建基于 SYSTEM_USER 函数。
You don't mention what (if any) database you're using, but assuming SQL Server then if you connect using Windows Authentication you could create views or stored procedures that filtered the data based on the SYSTEM_USER function.
即使您不使用它，这很遗憾，您至少可以从 Rhino.Security。
Even if you don't use it, that being a shame, you can at least learn the ins and outs of the concept from Rhino.Security.