ASP.NET 用户浏览来自 sql-server 的选定/限制的原始数据
在 ASP.NET WebForms 应用程序中，我希望允许最终用户浏览 sql-server 数据库中选定的原始数据。
但是，我想限制用户的访问权限，使其仅根据用户名查看某些数据。
我不确定如何以用户可以理解的方式执行此操作，因为用户不一定了解 SQL。
我在这里有什么选择？
作为这一基础，我考虑为每个相关表创建一个 sql 函数。该函数应该返回允许用户查看的数据，例如，
CREATE FUNCTION ufn_RawData_Employee(@username nvarchar(256))
RETURNS TABLE
AS ( SELECT * FROM Employee
WHERE [@username is allowed to view the given Employee] )在网页中，最终用户可能会键入类似 SQL 的语句，例如
SELECT Name, HireDate FROM ((Employee))where (([TableName])) then might be在调用数据库之前替换为 ufn_RawData_[TableName]([UserName])。 （出于安全原因，此类调用可以由其唯一权限是这些函数的 SELECT 权限的 sql 用户执行。）
但是，这种方法对于最终用户来说可能太困难。我想知道是否存在更简单/用户友好的解决方案供最终用户浏览选定的原始数据？
In an ASP.NET WebForms application I would like to allow the end-user to browse selected raw data in an sql-server database.
However, I would like to restrict access for the user to only view some of the data based on the username.
I'm not sure how to do this in a way that is possible for the user to understand, since SQL is not necessarily known to the user.
What options do I have here?
As a basis for this I have considered creating one sql function per table in question. That function should return the data that the user is allowed to view, e.g.,
CREATE FUNCTION ufn_RawData_Employee(@username nvarchar(256))
RETURNS TABLE
AS ( SELECT * FROM Employee
WHERE [@username is allowed to view the given Employee] )In a webpage the end-user might then type an SQL-like statement like
SELECT Name, HireDate FROM ((Employee))where (([TableName])) then could be replaced by ufn_RawData_[TableName]([UserName]) before calling the database.
(For security reasons such calls could then be performed by a sql user whose only permissions are SELECT permissions to these functions.)
However, this approach might be too difficult for the end-user. I would like to know if an easier/user-friendlier solution exists for the end-user to browse selected raw data?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您仅显示一张表或一个视图中的用户数据（这可能更有用），那么您可以将该视图的名称存储在表中并使用函数检索它。然后，您可以在页面中显示数据，并确保您的标准选择脚本具有内置搜索功能（如有必要）。
如果用户只从一张表或视图中获取数据，则无需编写 SQL。如果您需要提供多个潜在的表/视图，那么让他们从下拉列表中进行选择，但允许他们编写自己的 SQL 查询听起来并不值得。
If you are only showing the user data from one table, or one view (which would probably be more useful) then yes you could store the name of that view in a table and retrieve it with a function. You could then display the data in pages, and make sure your standard select scripts have a built in search function if necessary.
There is no need for the user to write SQL if they are only getting data from one table or view. If you need to provide multiple potential tables/ views, then let them choose from a drop down, but it doesn't sound worthwhile to allow them to write their own SQL queries.