How to restrict users to select only query through application textbox field?

Question

Say that I have a web application that lets users to type in a SQL query (SQL Server 2008+). I want users to be able to run any SELECT query and read whatever is in the database, but I do not want them to be able to insert/update/drop/truncate/exec etc. (in short: only select). Please note that there are no data or even database metadata that are considered confidential in the entire database.

The rules currently in place:

1. No ";" character. If it is there, it must be exactly one and at the end of the trimmed query string.
2. Query must begin with select (case ignored, white-spaces are part of regex).
3. All newlines are removed from the query.

Is this enough? Is there any way to do anything else than just select?

EDIT: Thank you Thomas Stringer for the note about SELECT ... INSERT. This is exactly the information I was after. I will remove all the newlines from the query string, which should help solve this problem. Is there any other way to get around this and be able to modify the data?

NOTE: CTE is not relevant for this case. Lets assume that query has to begin with SELECT.

• Thomas Stringer: There are other data retrieval techniques that don't necessarily begin with "select" (think CTE). It really depends on how advanced you want to go with this. Secondly, you are enforcing permissions on the front-end/GUI. Are you also reinforcing this on the database? I.e. users only have SELECT permissions, etc.
• Thomas Stringer: Also, what's to stop a user from typing this: select 1 </new line here> insert into .... blah blah blah? It looks like that would fit your rules, but if you aren't enforcing permissions on the database you could have users doing things you don't want them to do.
• Thomas Stringer: If all you want is the users to be able to see whatever data they want, then I would recommend taking out the ad-hoc nature of your application. Provide a way for users to grab tables (think drop down list). Then have a data grid view where they can filter on columns and sort. That would be a much simpler implementation. Unless of course you require more advanced data retrieval, like joins.

Answer 1

Make sure the application connects to the server using a login that has been given only read permissions (give it the db_datareader role in the database to allow reading all tables), and you should be in good shape. The easiest way to prevent changing data is to ensure the user doesn't have permission to change anything.

Be careful about granting execute permissions, as you may inadvertently allow users to change data depending on what the procedures do.

• NeverStopLearning: This is certainly a good way to ensure the read-only access. However a] I have to stick with the full access user due to legacy reasons and b] I was wondering from a knowledge point of view if there is any "security hole" in my logic (1)+(2).
• db2: I'm sure you'll find loads of holes by attempting to build security into the parser. For example, you don't actually need a ; to terminate statements. Doing this all on one line is perfectly valid: SELECT TOP 100 * FROM sys.databases UPDATE pubs..authors SET address = NULL
• NeverStopLearning: Perfect! This is the information I was asking for. I did not know that you did not have to separate the two commands using semicolon or newline! Thanks. Anything else?
• db2: If you must use the existing login/user for authenticating, then look into application roles. You can have your application switch to an application role after logging in, providing it with only read permissions.
• Jon Seigel: @NeverStopLearning: More to the point: SELECT 1 DROP DATABASE MyProdDB -- is the application user a member of db_owner?

Answer 2

If direct SQL is going to be allowed, you really have to use a separate login for it if the main application connection is read/write.

Not only should this login have restricted permissions as @db2 mentioned, but doing this also allows you to do things like better-manage the resources (Resource Governor) being used by this feature. Even if all that's allowed are SELECT statements, there's nothing stopping a malicious user (or even a careless user!) from mounting a denial-of-service attack by executing an extremely expensive query. If you don't have the granularity to be able to separate application activity from this type of activity, you're pretty much out of luck. Don't wait until this happens to try to stop it. Put mechanisms in place to prevent it in the first place.

• db2: That's a good point about denial-of-service. SELECT * FROM hugetable h1 CROSS JOIN hugetable h2 WHERE dbo.expensivefunction(h1.bigcolumn, h2.bigcolumn) = 1 OPTION (MAXDOP 0) Say goodbye to your buffer cache and CPU cycles.
• NeverStopLearning: In my case that would be considered as out of scope for the security setting. But a good point.