'Restrict user-defined T-SQL query to SELECT type only

I am working on an app that is providing a way for an user to define a SQL query and run it against the database. The only purpose of the query is to create a dataset which will be used to build a report. As this is a shared database (meaning large number of accounts with per-account tables), security is very important.

I am looking for the best way to verify that the supplied query is SELECT type only - meaning the user cannot run DELETE, UPDATE, etc.. Of course, the verification must be server side. The project is written in C#.

The first check is to verify that the query begins with SELECT, but it is not enough because of sub-queries. I was thinking to verify if the query string (C#) contains some reserved words like: EXEC, DELETE, UPDATE, INSERT.

Are there any ways to "hack" this verification (using &nbsp or other chars) ? Which reserved words must be blocked ? Any other thoughts or ideas ?

Thanks a lot for your time!

c++sharpsqlsql-server


Solution 1:[1]

Can you give the user a restricted GUI where they can point-and-click? That way the query is correct by construction.

Next idea would be to create a special user which has only the right permissions. This should be 100% reliable according to the docs (if it was not, it would be impossible to secure the server). But I don't trust that 100% because there can be bugs in SQL Server or your implementation of security. Decide yourself whether this is enough for you.

Alternatively, use SMO to parse the query and validate it. Make sure that it conforms to a white-listed format (e.g. exactly one SELECT, no function invocations, no EXEC, ...). This is hard.

Solution 2:[2]

The following code grants permissions on SELECT, and deny permissions on SQL keywords that may cause malicious attacks:

GRANT SELECT ON SchemaName TO UserName
DENY ALTER ON SchemaName TO UserName
DENY CREATE ON SchemaName TO UserName
DENY INSERT ON SchemaName TO UserName
DENY UPDATE ON SchemaName TO UserName
DENY DELETE ON SchemaName TO UserName
DENY DROP ON SchemaName TO UserName
DENY TRUNCATE ON SchemaName TO UserName

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 usr
Solution 2

Related Questions