数据库访问模拟
我的目标是防止用户直接访问数据库服务器。一种方法是在前端应用程序和数据库服务器之间创建 WCF 服务或 Web 服务。
首先，用户将通过应用程序的身份验证。随后应用程序将通过WCF服务连接来执行业务逻辑操作。 WCF服务将使用一个Windows帐户执行数据库相关操作。这将阻止其他用户直接访问数据库服务器，因为该权限只会授予特定的一个 Windows 帐户。
我的问题是：即使数据库访问权限仅授予一个Windows帐户，并且WCF将使用此Windows帐户执行数据库相关操作，是否可以使用登录用户的凭据标记所有数据库相关操作？
更新
感谢您的回复。看来上面的场景是无法实现的。我目前正在探索 SQL 2008 应用程序角色功能。其中一个示例位于此处。但经过进一步探索，显然连接池存在问题。
更新
这里有一个关于 SQL Server 应用程序角色的堆栈溢出线程
My objective is to prevent direct user access to the database server. One way is to create a WCF service or web service in the middle between the front end application and the database server.
First of all, the users will be authenticated to the application. Subsequently the application will connect through the WCF service to perform business logic operations. The WCF service will perform the database related operations by using one windows account. This will prevent other users to directly access the database server, since the permission will only be granted to specific one windows account.
Here are my questions : Even though the database access is only granted to one windows account and the WCF will use this windows account to perform database related operations, is it possible to mark all database related operations with the credential of the logged in user ?
Update
Thanks for the replies. Seems like the above scenario is not achievable. I am currently exploring the SQL 2008 Application Role feature. One of the example is here. But after further exploration, apparently there is an issue with the connection pooling.
Update
There is a stack overflow thread here regarding SQL Server Application Role
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您必须在 sys.server_principals 中设置每个用户以启用上下文切换（例如 EXECUTE AS），这意味着他们无论如何都可以直接访问数据库。
如果您启用了 kerberos/delegation，则同样适用。链接 One 和 两个
您必须传入用户名作为每个 SQL 调用的参数，或者也许使用 CONTEXT_INFO。
注意：每个 MS Office 用户都有 MSQRY32.EXE，它充当查询工具。如果您不想直接访问数据库，那么您需要确保没有设置或授予权限
You'd have to have every user set up in sys.server_principals to enable context switching like EXECUTE AS which would mean that they have direct db access anyway.
If you enabled kerberos/delegation, the same applies. Links One and Two
You'd have to pass in the user name as a parameter on each SQL call, or use CONTEXT_INFO perhaps.
Note: every MS Office user has MSQRY32.EXE which acts as a query tool. If you want no direct DB access, then you need to ensure there are no permissions set or granted
视情况而定。
如果您的数据库和 WCF 服务位于同一个盒子上，并且您做了很多 jiggery pokery 来模拟，那么这是可能的。一旦您将数据库移至另一个盒子，它就会停止工作。
这是一个已知的限制，原因是模拟将创建一个令牌，该令牌将使您进入一个盒子，但不能传递到另一个盒子。我试图找到 MS 人员回答过的问答，但仍然找不到。每当我找到它，就会更新。
Depends.
If your database and WCF service are on the same box and you do a lot of jiggery pokery to impersonate then it is possible. As soon as you move your DB to another box then it stops working.
This is a known limitation and the reason is impersonation will create a token which will get you to a box but this is not passable to another box. I tried to find the Q&A where MS guy had answered but still havent been able to. Whenever I find it, will update.