实体框架和SQL Server用户角色：如何启用EF来生成角色感知查询？ [英] Entity Framework and SQL Server user roles: how to enable EF to generate role-aware queries?

问题描述

背景

我们有一个使用Entity Framework 4.2的WinForms应用程序，使用SQL Server 2008 R2的第一个/ FluentAPI。

安全性是与用户角色的 IPrincipal 和 IIdentity 的自定义实现。当窗体/菜单/按钮被显示时，会检查这些角色，并根据用户角色将其禁用/删除。

用户根据数据库进行身份验证没有主用户的连接：它是使用登录屏幕上提供的用户名/密码创建的。

所以，访问数据（一般）正在工作。 p>

问题

但有些情况可能需要我禁用访问特定的表或表内的列

有些测试在此显示 IGenericRepository.Find< MyCustomType>（_ idToFind）（返回完整实体）将失败，因为无法访问单个列，SQL Server将阻止整个select语句。

然而，我发现创建一个查询，如

  IGenericRepository.All< MyCustomType>（）。选择（_c => _c.JustASingleField）
  

将工作因为生成的查询只会查找我可以访问的特定字段。

问题

是否有一种方法来创建对数据库有角色感知的查询？

例如：查找< MyCustomType>（id） code>将像往常一样返回对象，但用户没有访问权限的字段设置为NULL或没有值？

或者我会必须为不需要保护的每个单个项目编写通用查询，并依靠安全系统完全阻止访问资源？

另一个例子是填充网格，但用户无权访问的列将为空。

是否可以使用Entity Framework？

解决方案

是否可以使用EntityFramework？

不。 EF不知道SQL服务器上的安全配置，它不能对SQL服务器预期的任何安全性要求做出反应，除了提供连接字符串的凭据。

如果您需要这种类型的安全性应该使用数据库视图，只提供可访问的项目到特定角色，并且让EF使用模型映射仅查看用户角色具有的访问权限 - 由于许多角色，它可能导致相当大的一组不同的模型。 / p>

Background

We have a WinForms application with Entity Framework 4.2 code-first / FluentAPI using SQL Server 2008 R2.

The security is a custom implementation of IPrincipal and IIdentity with the roles for the user. These roles are checked when a Form/menu/button is displayed and it will be disabled/removed based on the user role.

Users are authenticated against the database so there is no "master" user for the connection: it's created using the username/password provided on the login screen.

So, access to data (general) is working.

The problem

But some cases might require me to disable access to a specific table or to a column inside the table.

Some tests have shown here that IGenericRepository.Find<MyCustomType>(_idToFind) (which returns the complete entity) will fail because there is no access to a single column and SQL server will prevent the whole select statement.

I've found, however, that create a query like

IGenericRepository.All<MyCustomType>().Select(_c => _c.JustASingleField) 

will work because the generated query will look only for a specific field for which I have access.

Question

Is there a way for me to create queries that will be role-aware to the database?

For instance: Find<MyCustomType>(id) will return the object as usual but with the field that the user does not have access set to NULL or with no value?

Or I'll just have to write "generic" queries for every single item that does not require protection and rely on the security system to completely block access to a resource?

Another example would be to fill a grid but the column for which the user does not have access will be blank.

Is it possible at all using Entity Framework?

解决方案

Is it possible at all using EntityFramework?

No. EF is not aware of security configuration on SQL server and it is not able to react to any security demands expected by SQL server except providing credentials for connection string.

If you require this type of security you should use database views providing only accessible items to specific role and let EF to use model mapping only views the user role has access to - it can result in quite big set of different "models" due to many roles.

这篇关于实体框架和SQL Server用户角色：如何启用EF来生成角色感知查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！