2

I have a client that wants me to implement a webpage that allows users to enter custom queries in a text box so they can directly query the SQL Server database. I am concerned about users entering malicious queries *. Is there a way to assure (via syntax checking or role permissions or whatever) to assure that the database is kept secure while providing this functionality?

* Ignoring denial of service. My concern is primarily loss of data

2
  • Fair enough... Ignoring denial of service. My concern is primarily loss of data. – hershmans Sep 20 '16 at 21:47
  • Will you consider setting up standby db in readonly mode ? Even simple Log Shipping will give end users database which is very close to production (depends on how often you apply transaction logs). – a1ex07 Sep 20 '16 at 22:54
6

I am concerned about users entering malicious queries. Is there a way to assure (via syntax checking or role permissions or whatever) to assure that the database is kept secure while providing this functionality?

That is actually two separate, though related, questions: one about "security", and one about "malicious". While security violations are malicious, not all malicious queries are security violations.

You can lock down read and write access to various tables through proper use of permissions and Views. However, I don't think you can prevent them from doing things such as issuing SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; and then using XLOCK or TABLOCK table hints. Or combine that with issuing BEGIN TRAN; and then select from a table using those query hints, and then issue a WAITFOR DELAY '10:00:00.000'; -- 10 hour delay, hee hee.

Read-only access to user databases doesn't prohibit temporary objects, right? So they can still create temporary tables and table variables? If so, someone could intentionally, or "accidentally" fill up tempdb **, or at least degrade performance by adding a lot of contention to tempdb and its log file.

Lots of CURSORs will cause either more locking and contention (i.e. blocking) if dynamic, or more tempdb usage if STATIC.

<sarcasm>And I'm sure all of these users have been trained in both T-SQL and your data-model, hence won't be executing horribly inefficient queries that will fill the Buffer Pool more than necessary, thereby reducing Page Life Expectancy and degrading performance of the entire application.</sarcasm>

This is what a Read-Only Replica is for, or a secondary system populated via log-shipping, etc. Don't give direct, ad hoc query access to your OLTP system to anyone who can't be fired for misuse (i.e. employee or subcontractor).

** @MartinSmith noted that one can just execute the following to create a 10 billion character string:

SELECT DATALENGTH(REPLICATE(REPLICATE(CAST('X' AS VARCHAR(MAX)),100000),100000));

That amounts to 9.31 GB of data space needed in tempdb (it shows up when using the sys.dm_db_task_space_usage and sys.dm_db_session_space_usage DMVs). OR, just add one little letter to make the VARCHAR(MAX) an NVARCHAR(MAX) instead, and you will have yourself an 18.63 GB string ;-). Woo hoo!


Since the question was clarified to focus mostly on data loss and not "denial of service", I can think of two scenarios to look out for:

  1. Linked Servers: If you have any Linked Servers, make sure that the local, read-only Login will not connect to the remote server using a mapped Login/password for a read-write account.

    Or you can at least try to hide Linked Servers from the read-only Login. Please see the "Permissions" section of the MSDN page for sys.servers for details.

  2. Elevated permissions due to Ownership-Chaining, EXECUTE AS, and code signing: Do a thorough review of all Stored Procedures that this read-only Login might have EXECUTE rights to. It is really easy to bypass direct object permissions via Ownership-Chaining, EXECUTE AS, and code-signing. If you are using any "Ole Automation Procedures" or SQLCLR objects, those can potentially reach outside of the database and so while you might have CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, MERGE, TRUNCATE, etc locked down, there can be Stored Procedures that write to the file system, registry, etc that won't be prohibited by those other restrictions. You need to make sure that the entire system is read-only, not just SQL Server.


P.S. While this is not a security issue, on a more practical level relating to giving "users" the ability to write ad hoc queries: how will these queries be used, and what is your level of obligation to notify the "users" of changes to the data model? Giving people ad hoc query ability is not the same thing as providing an API. The data model will inevitably change over time. These changes will possibly break queries that the "users" had been executing successfully (even if painfully) in the past. Will your client be upset with you when their users complain that there queries broke (and yes, they will complain because they will save queries in a file or wiki page and build business-processes to run them on a regular basis)?

2
  • 1
    As always, a well written and clearly presented answer. I love that you picked up on the fact that these "users" likely won't have domain-specific knowledge about how to join tables together, etc. – Hannah Vernon Sep 22 '16 at 16:49
  • 1
    @MaxVernon Why thank you (as always :). I just added a related issue to a P.S. at the end. – Solomon Rutzky Sep 22 '16 at 18:52
1

You are creating your own SQL injection framework doing this. There is no way that this would ever be secure (no matter what permission set or syntax checking you put around it).

6
  • 2
    Even if the login used by the webpage only had read-only access to the instance? – Hannah Vernon Sep 20 '16 at 20:32
  • Data loss comes right to mind. The ability to take down the server with a query is another. There are really smart people out there that can (and will take advantage) of something like that. – Nic Sep 20 '16 at 20:39
  • I agree with Nic here. This is a HORRIBLE solution. You could in theory build permissions so that users only have access to the data that they need, but 5 seconds later someone will tell you they need access to everything. If these are internal users give them SSMS to run queries with. If external users, give them prebuilt reports. – mrdenny Sep 20 '16 at 20:43
  • Agreed! By the time you've restricted this using something like resource governor so they can't kill your system, then restricted the permissions to less dangerous data that hopefully won't open you to a lawsuit (good luck with that by the way since all data is dangerous in some way shape or form) you now have a nightmare to maintain. You're going to be getting calls all day and night for support. – Kenneth Fisher Sep 20 '16 at 20:47
  • 4
    I think some of the comments aren't quite right, this isn't a SQL injection framework as that would suggest users can do things their permission levels do not allow. There is no difference between this page and giving them ssms - although this will take a lot less for users to use! Basically lock down the user to read from tables/views you allow and if you are worried about resource usage use resource governor. Make sure the site is secured so only authorised users can get to it. Alternatives are things like tableau server / shared datasets which might be a better solution – Ed Elliott Sep 20 '16 at 22:41
0

The only people who should ever have access to run queries direct on a server is authorised people who actually know what they're doing.

That being said if this is something that absolutely needs to be done here's my advice.

Firstly create a new schema and fill it with views for any information they're allowed to access.

The user will have connect and select access to that schema and deny to everything else. the user also wants to have a custom timeout set on it (easiest to set on the webpage) which is something short. 10 seconds maximum, if they're running a query which lasts longer than that then they're doing something too complicated or malicious.

Also on the input I'd validate the input severely and harshly. no -- or /* allowed, words like 'cursor', 'create', '#' instantly get the entire query rejected.

If there are some standard things which are more complicated, have a set of predefined queries (stored procedures with validated input for parameters(aka userid)) which they can select from a drop down somewhere which can run for slightly longer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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