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
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
<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_session_space_usage DMVs). OR, just add one little letter to make the
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:
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.
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
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)?