0

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...

Improve this question
3
  • 1
    Are you concerned about the users selecting all rows from the table (your SELECT * scenario)? Or, about users executing SELECT FileContents FROM myTable with no WHERE clause, possibly returning thousands of rows (and thus, thousands of files)? Do users have the ability to query the database directly, or only through an application?
    – RDFozz
    Oct 19, 2017 at 22:37
  • Users require the ability to select directly from the table. I provide an application to allow users to download the file(s) via FileStream and to view data. The data I'm storing is all sensor measurement and engineering data. Their is a requirement to allow them to use other tools to access the data. For example using R or Python to perform their own custom analysis. I don't think it is reasonable to expect each user to remember not to query the table with the FileStream column.
    – Michael Ward
    Oct 20, 2017 at 13:25
  • 1
    What about providing a view with all columns but the FileStream column - do you think you could get your users used to using that?
    – RDFozz
    Oct 20, 2017 at 14:52

1 Answer 1

Reset to default
0

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 *.

Improve this answer
1
  • I agree the best solution is to not allow anyone to access the table except for the application itself. I managed to get this to work but it was a big pain. Things like application roles etc did not work. I had to implement impersonation in my application to impersonate a user then the impersonated user had to connect to the database in order to retrieve the data.
    – Michael Ward
    Jan 10, 2018 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.