Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Other SQL Server 2012 Topics
 Determining if SQL text would update a DB

Author  Topic 

joerobbins
Starting Member

2 Posts

Posted - 2014-10-28 : 08:32:53
PROLOGUE: Should this question be posted to "General SQL Server Forums" - it's non-release specific? On the otherhand, it doesn't fit any of those forums: "New to SQL ...", etc.


Premise: a client app supports user submission of ad hoc SQL queries to SQL Server. Privileged users may submit any type of query. Non-privileged users must be restricted to non-updating type queries (SELECT).
What is the best way to implement this?

Searching the web suggests there are a few possible strategies but I haven't yet found one that is simple and/or satisfactory.

1. Ideally, there would be a way to submit SQL text to some test or system SPROC that would parse the SQL and return a value to indicate that it would update the DB (if run).

2. Is it possible to "wrap" the SQL so that it would fail to run if it was updating? Initial attempt: "SELECT * ( FROM " + @sql + " ) AS tbl" This is no good if @sql contains a WHERE clause.

3. The SQL is sent from the client to SQL Server using ADO. It seems easy to tell ADO NOT to return a recordset or that the recordset is to be read-only. I can't find anything that restricts the submitted sql to non-updateable - not on the Connection Object , Command Object or on the Recordset Object.

4. Parsing (in VB or T-SQL) the SQL text for keywords (such as INSERT UPDATE DELETE TRUNCATE etc etc) is problematic (comments, tokenizing, etc) and probably not fool-proof against malicious code injection.

5. It would be possible to construct an input-form for the user to enter the field list, join clauses, where clauses, order fields, etc. The code could then glue these together to make the SQL SELECT statement. This would make it more difficult to "smuggle" an updating statement. This would lose the beauty of simply copying SQL text (from an email attachment, for example), pasting it into an ad hoc query window, running it and getting the results in EXCEL.

6. Users cannot be directly controlled by permissions at SQL Server level (a user we want to bar from submitting updating ad hoc SQL must still be able to use the GUI of the App to update the DB Tables). It would be possible to open an ADO Connection using a "pseudo user" who would have db_dataReader properties; but this complicates things, e.g. ignore Integrated Authentication.

Perhaps I have got this all the wrong way round and there is a simple solution - but what is it?

Joe Robbins

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 09:01:01
Check this article out: http://msdn.microsoft.com/en-us/library/ms191291.aspx#_securables

Basically, you can set up permissions in SQL and use those to restrict who does what. So, you put those read-only users in one group and give them SELECT permissions. The privileged users get more permissions.
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-10-28 : 09:22:14
You will need to be very careful with this but the following outline seems to work:

CREATE DATABASE Test;
GO
USE Test
GO
CREATE TABLE dbo.Test
(
TestId int NOT NULL
,TestStr varchar(20) NOT NULL
);
INSERT INTO dbo.Test
VALUES (1, 'Test1')
,(2, 'Test2')
,(3, 'Test3');
GO
CREATE LOGIN TestRO WITH PASSWORD = 'TestRO', CHECK_POLICY = OFF;
CREATE USER TestRO FOR LOGIN TestRO WITH DEFAULT_SCHEMA=dbo;
ALTER ROLE db_datareader ADD MEMBER TestRO;

CREATE LOGIN TestRW WITH PASSWORD = 'TestRW', CHECK_POLICY = OFF;
CREATE USER TestRW FOR LOGIN TestRW WITH DEFAULT_SCHEMA=dbo;
ALTER ROLE db_datareader ADD MEMBER TestRW;
ALTER ROLE db_datawriter ADD MEMBER TestRW;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.ExecSQLRO
@SQLString nvarchar(4000)
WITH EXECUTE AS 'TestRO'
AS
EXEC (@SQLString);
GO
CREATE PROCEDURE dbo.ExecSQLRW
@SQLString nvarchar(4000)
WITH EXECUTE AS 'TestRW'
AS
EXEC (@SQLString);
GO
EXEC dbo.ExecSQLRO 'SELECT * FROM dbo.Test';
GO
EXEC dbo.ExecSQLRO 'DELETE dbo.Test WHERE TestId = 2';
GO
EXEC dbo.ExecSQLRW 'DELETE dbo.Test WHERE TestId = 2';

--USE master
--GO
--DROP DATABASE Test;
--DROP LOGIN TestRO;
--DROP LOGIN TestRW;

Go to Top of Page

joerobbins
Starting Member

2 Posts

Posted - 2014-10-28 : 10:42:08
Brilliant! Thank you both for the lightning replies.

The "CREATE PROC ... WITH EXECUTE AS 'userName'" is new to me. It looks a likely solution. I will do some tests ....

Thanks again.

Joe Robbins
Go to Top of Page
   

- Advertisement -