Working with an application that needs to provide row and column level security for user reports. The logic for the row filtering an column masking is in place, but there are still decisions to be made about identifying users at report execution time.
使用需要为用户报告提供行级和列级安全性的应用程序。行筛选列屏蔽的逻辑已到位,但仍有决定在报告执行时识别用户。
The application uses a single SQL Server login to authenticate, as all rights are data driven within the application itself. This mechanism does not carry well to reports, as clients like Crystal and MS Office do not authenticate through the application (web and WinForms).
该应用程序使用单个SQL Server登录进行身份验证,因为所有权限都是在应用程序本身内驱动的数据。这种机制不适合报告,因为像Crystal和MS Office这样的客户端不通过应用程序(web和WinForms)进行身份验证。
The traditional approach of using SQL Server logins and database users will work will, but may have one issue. In some implementations of the application, the number of users who run reports and need to be uniquely identified may run into the hundreds.
使用SQL Server登录和数据库用户的传统方法将起作用,但可能有一个问题。在本申请的一些实现中,运行报告并且需要被唯一标识的用户的数量可以达到数百个。
Are there any practical limits to the number of logins or users on a SQL Server database (v 2005+) where this approach may cause problems? Administration of the users on the database server can be automated by the application, but the potential number of credentials may be a concern.
SQL Server数据库(v 2005+)上的登录次数或用户数是否存在实际限制?此方法可能会导致问题?应用程序可以自动管理数据库服务器上的用户,但可能需要考虑潜在的凭证数量。
We have looked into user impersonation techniques, but they become difficult to implement when a report client such as Excel authenticates directly to the server.
我们已经研究了用户模拟技术,但是当Excel等报表客户端直接向服务器进行身份验证时,它们很难实现。
Edit: The concern is not concurrency or workload, but rather administration issues on remote instances where a local DBA is not available, especially when the server is not dedicated to the application. Interested in scenarios where the numbers of logins were problematic.
编辑:关注的不是并发或工作负载,而是本地DBA不可用的远程实例上的管理问题,尤其是当服务器不专用于应用程序时。对登录数量有问题的情况感兴趣。
1 个解决方案
#1
I've used your described approach (SQL Server accounts managed automatically by our application) and we didn't have any trouble. However, we only ever had a maximum of perhaps 200 SQL accounts. But we didn't experience any kind of administrative overhead except when "power users" restored databases without telling us, causing the SQL login account to become out of synch with the database*.
我已经使用了您描述的方法(由我们的应用程序自动管理的SQL Server帐户),我们没有遇到任何麻烦。但是,我们最多只有200个SQL帐户。但是我们没有遇到任何类型的管理开销,除非“高级用户”在没有告诉我们的情况下恢复数据库,导致SQL登录帐户与数据库*不同步。
I think your approach is sound.
我认为你的方法很合理。
EDIT: Our solution for this was a proc that simply ran through the user accounts and called our procs that deleted/created the user accounts. When the power users called this proc all was well, and it was reasonably fast.
编辑:我们的解决方案是一个简单地遍历用户帐户并调用我们删除/创建用户帐户的过程的过程。当高级用户称这个过程一切顺利时,它的速度相当快。
#1
I've used your described approach (SQL Server accounts managed automatically by our application) and we didn't have any trouble. However, we only ever had a maximum of perhaps 200 SQL accounts. But we didn't experience any kind of administrative overhead except when "power users" restored databases without telling us, causing the SQL login account to become out of synch with the database*.
我已经使用了您描述的方法(由我们的应用程序自动管理的SQL Server帐户),我们没有遇到任何麻烦。但是,我们最多只有200个SQL帐户。但是我们没有遇到任何类型的管理开销,除非“高级用户”在没有告诉我们的情况下恢复数据库,导致SQL登录帐户与数据库*不同步。
I think your approach is sound.
我认为你的方法很合理。
EDIT: Our solution for this was a proc that simply ran through the user accounts and called our procs that deleted/created the user accounts. When the power users called this proc all was well, and it was reasonably fast.
编辑:我们的解决方案是一个简单地遍历用户帐户并调用我们删除/创建用户帐户的过程的过程。当高级用户称这个过程一切顺利时,它的速度相当快。