3

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.

Improve this question
3
  • 2
    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
    Commented Oct 8, 2013 at 12:53
  • 2
    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
    Commented Oct 8, 2013 at 12:54
  • 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.
    – Thomas Stringer
    Commented Oct 8, 2013 at 13:05

2 Answers 2

Reset to default
6

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.

Improve this answer
6
  • 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).
    – NeverStopLearning
    Commented Oct 9, 2013 at 11:29
  • 1
    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
    – db2
    Commented Oct 9, 2013 at 14:36
  • 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?
    – NeverStopLearning
    Commented Oct 9, 2013 at 14:40
  • 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.
    – db2
    Commented Oct 9, 2013 at 14:49
  • 1
    @NeverStopLearning: More to the point: SELECT 1 DROP DATABASE MyProdDB -- is the application user a member of db_owner?
    – Jon Seigel
    Commented Oct 9, 2013 at 17:55
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.

Improve this answer
2
  • 1
    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.
    – db2
    Commented Oct 9, 2013 at 14:53
  • In my case that would be considered as out of scope for the security setting. But a good point.
    – NeverStopLearning
    Commented Oct 10, 2013 at 8:24

Your Answer

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

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