Problem:
I want users to be able to download data stored in a table that contains a FILESTREAM column. Each file is typically 200 to 500 MBytes. I however do not want to grant the user SELECT access (specifically SELECT *) to the table. I was able to create a stored procedure that provides the Path / Transaction Context. However if I do not grant select permissions on the table the SqlFileStream() class fails to open due to a permission error.
If I grant the SELECT permission I risk a user performing a SELECT * on the table and watch the server die as it is trying to return gigabytes of information.
I believe I have a solution.
I think I can deny select permissions to the table. The only reasonable way to retrieve the data from the FileStream table is through the application. I can use an Application Role which has permission to the table to perform the download. I will try this out, I was hoping to not have to make this more complicated…
Best Answer
There's no way to restrict SELECT access to a set number of rows.
If the users don't need access to the entire table, but just their "own" rows, you could configure row level security (SQL 2016 and up) to restrict access to only what they need. This will not prevent the user from performing a
SELECT *, but it will restrict the volume of data returned if they do run one.Ultimately, this is the kind of situation which would be best solved by not allowing users to have any direct access to the table, and instead have the data returned via app code which can be controlled to not run a
SELECT *.