Verboten keywords to assure SELECT only commands

Question

Verboten keywords to assure SELECT only commands

Lowell

SSC Guru

Points: 323518
More actions
November 24, 2009 at 10:52 am

#137384

i know the first thing you'll say...don't give them permissions to do that in the first place.
I've been handed a project where SQL commands, which are supposed to be SELECT commands, are stored in an XML file, and run on demand to stick them in a grid/excel equivilent; sort of a grid only report writer. the application lists a title , i.e. " Financial Report", and when clicked, queries the database(SELECT [collist] FROM SOMEVIEW) and presents the results in the grid. the grid lets them filter and group and stuff, making it a decent little presentation utility.
the issue is permissions...it's kind of wide open to abuse, which i want to avoid.
think of it as a third party app...the end user puts in his credentials and db connection info for their database, so i don't have the ability to filter permissions. The end user would potentialy have the ability to add new select statements to the collection of 'reports".
In an attempt to prevent anyone from from using it to update/delete/truncate/etc,
i figured I could use regualr expressions to test any command that is attempted to be run, and if there is a match, simply don't display/don't allow the command to be run.
AFAIK, there is no command to issue the server to prevent updates, just stuff like PARSEONLY and NO_EXEC, which would prevent SELECT commands from returning data ;
besides the obvious keywords to filter, can anyone offer other suggestions? to avoid mistchief? or a better solution?
here's what I'm considering so far:
ALTER DBCC MERGE
BULK DBREPAIR MIN REVOKE
CHECK DELAY MIRROR TRUNCATE
CHECKALLOC DELETE OPEN
CHECKCATALOG DROP OVERRIDE UPDATE
CHECKFILEGROUP EXEC PARSEONLY XP_ (anything that starts with)
CHECKIDENT EXECUTE PREPARE
CHECKPOINT GRANT RECOMPILE
CHECKTABLE IDENTITY_INSERT RECONFIGURE
CONNECT INSERT REMIRROR
CREATE KILL REMOVE
CURSOR LOAD RESTORE
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Set up a read-only copy of the database and make it so it can only connect to that.

Permissions would be the best way to manage this, but if you really can't work it from that angle, then a read-only database is your best bet.

Easy enough to establish one, and to update it periodically through log shipping.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 2

Thanks Gsquared, your idea would work if i had control of the database.

imagine if i gave you an application: you download it, put in your connection info at your location, wherever that is...i don't have the ability to touch your db nor change permissions...it's an app on your desktop to make simple reports....

you copy and paste a SQL related to your data,, but year there might be canned reports expecting an existing table and structure....

It'll take some tweaking to make sure regular expressions prevent anything mischievious, that's the only thing i could think of so far.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 3

RegEx might help, but what happens if there's a table with a column named "ExecutionDate", for the execution date of a trade or sale? Will you block all queries on that column because it contains "Exec" in it?

If not, how do you block pretty much any form of injection their database rights allow?

You're in a rough place in terms of what you have to do, and I really can't suggest a good solution. They all have gaping holes in them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 4

with regex, i can use word boundaries, so UPDATE would be a match, but UPDATEDDT would not. same with EXEC/EXECUTE vs a word that contains the keyword.

I'm going to play with the project for a while, but you are right....there are so many holes if you can't use SQL , security, it's looking like it's not going to be fun

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok here's another idea...besides the planned filtering, what if the application started a transaction, ran the script, and then rolled back the transaction?

if it was a valid SELECT statement, no problem, right? but it would rollback anything else, or error out if they tried to run a statement that cannot exist in a transaction.

can anyone poke holes in that idea?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 6

Lowell (11/24/2009)
ok here's another idea...besides the planned filtering, what if the application started a transaction, ran the script, and then rolled back the transaction?
if it was a valid SELECT statement, no problem, right? but it would rollback anything else, or error out if they tried to run a statement that cannot exist in a transaction.
can anyone poke holes in that idea?

That should work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 7

dba_pkashyap

Hall of Fame

Points: 3336

November 25, 2009 at 7:23 am

#1083994

How about DDL triggers at database level!!!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

dba_pkashyap (11/25/2009)
How about DDL triggers at database level!!!

I'm liking that but can't you just set the users up with "data reader" only?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 9

Jeff Moden (11/25/2009)
dba_pkashyap (11/25/2009)
How about DDL triggers at database level!!!
I'm liking that but can't you just set the users up with "data reader" only?

i wish (sigh)

there is no access/no control to third party databases.

kind of like a simplified crystal reports...i can't control what db you point it at, nor what commands you copy/paste.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!