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

  • 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
  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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
  • 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.”

  • 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.

  • 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]

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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
  • 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.”

  • 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.

  • 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

  • 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.

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.

  • 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

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

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