Is there a way to enforce the use of a where clause in a select?

Question

Is there a way to enforce the use of a where clause in a select?

Viewing 15 posts - 16 through 29 (of 29 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Alexander Suprun (10/16/2014)
Create a job which runs every 5 seconds and KILL the processes of specific user(s) where program_name is 'Microsoft SQL Server Management Studio - Query'

And hope they don't learn how easy it is to change the program name when connecting a query window.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

Probably the easiest way to do that is to create views which limit the data by date or whatever, then give them access to only the views. But that could be a hard sell at this point.

You could also rename the existing table, and make the original table name a restricted view. But that would (likely) require changing all your existing code to refer to the new table name, which might be extremely difficult if not impossible.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Darryn_the_Tired (10/16/2014)
Devs have told me they cant change this and its my problem.

Time to get management involved?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Darryn the Tired Ten Centuries Points: 1278 More actions · Answer 4

GilaMonster (10/16/2014)
Darryn_the_Tired (10/16/2014)
Devs have told me they cant change this and its my problem.
Time to get management involved?

HA!

“Program (pro’ gram) n. a logical sequence of operations to be performed by a computer that usually results in error messages, v.t. to engage in an activity similar to banging one’s head against a wall.”

ZZartin SSC-Dedicated Points: 30758 More actions · Answer 5

Devs have told me they cant change this and its my problem.

Start telling anyone that complains about the problem that it's working as intended and they need to be talking to the devs and not you if they don't like that.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

Is this person named Paul, by any chance? 😀

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

I'd raise a stink internally. You can't work miracles. There are physical limitations to the universe and a query without a WHERE clause is going to smack right into them pretty hard.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

Grant Fritchey (10/16/2014)
I'd raise a stink internally. You can't work miracles. There are physical limitations to the universe and a query without a WHERE clause is going to smack right into them pretty hard.

Yup. My suggestion of management wasn't intended to be funny.

You can' fix this without the dev team's cooperation. If they're not willing to cooperate....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Darryn the Tired Ten Centuries Points: 1278 More actions · Answer 9

Not looking good...

Thanks all for taking the time to think about this...much appreciated

“Program (pro’ gram) n. a logical sequence of operations to be performed by a computer that usually results in error messages, v.t. to engage in an activity similar to banging one’s head against a wall.”

paul.knibbs SSCoach Points: 15320 More actions · Answer 10

Think about it for a second. If you could somehow achieve what you wanted, all that will happen is that the same client will do the same thing, but get no data back--at which point they'll complain to the developers, who, from the sounds of it, will bat it right back at you again. You're no further along.

To be honest, if your developers really aren't capable of coding in a simple message that says "Sorry, you must select some filters first" if a client hits Get Data with no filters selected, you need to get some new developers.

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 11

paul.knibbs (10/17/2014)
To be honest, if your developers really aren't capable of coding in a simple message that says "Sorry, you must select some filters first" if a client hits Get Data with no filters selected, you need to get some new developers.

+1 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

serg-52 SSCrazy Eights Points: 9802 More actions · Answer 12

Quite possible a business rule exists, allowing users query DB with no filters set. And there're users and users. So it's a management problem really. Teach users or buy more processors or change rules.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 13

The other option is to simply throw hardware at the problem, but that's very expensive and of limited utility. You're going to have to convince people about the realities of the situation.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Chris Wooding SSCarpal Tunnel Points: 4427 More actions · Answer 14

If they're allowed to specify the "from <table> join <some other table>" part, then it's not just the lack of a WHERE clause that you need to worry about. I assume they have read-only access so they can't corrupt data, but unrestricted read access is still wrong in many (most) situations.

davoscollective SSCertifiable Points: 6355 More actions · Answer 15

Well you could do it with security. The application is either using a SQL login, a single AD login or service account, or it's passing through end user credentials. Deny those users access to the tables then they won't show up in the application. Then if they need access, create some similarly named views that all have a nice default where clause, like last 2 months as someone suggested.

To be honest, your devs sound a bit closed minded. What they should really be doing is some sort of paging. Either controlled by the code, or by injecting an ORDER BY with OFFSET and FETCH http://msdn.microsoft.com/en-us/library/ms188385.aspx

see the examples at the end of that msdn entry on the ORDER BY clause.

To be fair, if the users have been provided with a tool that is open slather for creating select * queries, then they should be provided with training on those elevated rights and their responsibilities with regard to the data. It's a privilege not a right to have that level of access