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?