How to prevent from select * from statement.

marrowyung
marrowyung used Ask the Experts™
on
hi all,

 I read this one how to prevent user from doing select * from table: http://www.sqlpassion.at/archive/2015/10/26/how-to-prevent-select-statements/#comment-54313

it seems not working much at all and I also agree on the comment post there.

how you guys prevent this if any?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In case, you want to prevent only then things like

1. Training session for development team, not to use SELECT *
2. Code-Review team like database project can be build and auto code-review rule can be implement and run

Apart, in case really needed all column then we can get from sys.columns at run time and execute query with sp_executesql

Let me know, what are your thoughts?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Main question is why do you let users to perform ad-hoc queries on a Production database?
Access to Production databases should me made only through an application where bad code can be controlled.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Main question is why do you let users to perform ad-hoc queries on a Production database?"

nono. I didn't say this.. I just want to control it everywhere if possible. it is a hope. you know resource governor , it only control CPU and RAM, none of them control TempdB resource, we need to program that up.

our code will be review but controlling this means for any one , by some reason, or planned, can't do this. we are controlling much better
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

IT Engineer
Distinguished Expert 2017
Commented:
No, you can't control things like if 'SELECT *' then throw an error or warning.
marrowyungSenior Technical architecture (Data)

Author

Commented:
can or can't sorry ? seems a bit confuse.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Can not.
marrowyungSenior Technical architecture (Data)

Author

Commented:
ok, can't anyway.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Vitor is correct in that there is not an automated process anywhere that will prevent developers from typing a 'SELECT * FROM' type query.  In most businesses there is some form of developer review either by a lead or an architect to review code for accuracy and syntax, and this would be a common thing to review.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"In most businesses there is some form of developer review either by a lead or an architect to review code for accuracy and syntax, and this would be a common thing to review. "

yeah, we are doing this too, but I am thinking about sth robust .. automated.. tks.
marrowyungSenior Technical architecture (Data)

Author

Commented:
MohitPandit,

"auto code-review rule can be implement and run
"

by system? or manual ?

"Apart, in case really needed all column then we can get from sys.columns at run time and execute query with sp_executesql"

what is that for?  I don't get it .
Top Expert 2012

Commented:
it seems not working much at all and I also agree on the comment post there.
Let's back up a bit.  Why do you say that Aaron's solution "not working much at all"  Have you really tried it or do you not agree with the approach?  Those are two totally different questions.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Anthony,

"Let's back up a bit.  Why do you say that Aaron's solution "not working much at all" "

which Aaron ? that post is not from Aaron, right?

"Have you really tried it or do you not agree with the approach?  Those are two totally different questions. "

did you read the comment on that link ? people there BASICALLY share the same through.  very hard to apply, sometimes debugs seems to do sth like Select top 1 * e.g. quick enough.

I understand code review can control that but basically I am hoping some system configuration and automate that.
Top Expert 2012

Commented:
which Aaron ? that post is not from Aaron, right?
Aaron Bertrand
did you read the comment on that link ?
Absolutely.  Did you?  I ask because you had no clue who was Aaron.  Hint: He was the author of the solution in that article,

In any case, I think you answered my two questions:
You did not test it,
You did not agree with the approach.

Anyone else reading this thread, the solution in the link does indeed work except for the edge case:
if
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

division by zero “fails” and select * is executed with only warning triggered.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"which Aaron ? that post is not from Aaron, right?
 Aaron Bertrand "

forget about that, the author just say attend his session. I am focusing on the link by Klaus Aschenbrenner.

there are only one say yes, but most of the rest disagree.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I am focusing on the link by Klaus Aschenbrenner.
That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world.
You'll need to had a column in every single table that you have and don't forget to add it in future new tables. Also you'll need to handle that error in your application or it can terminate abruptly.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world."

yeah, I can see what anyone else said,, it is hard, true. so relies on manual code review.

"You'll need to had a column in every single table that you have and don't forget to add it in future new tables."

indeed.

"Also you'll need to handle that error in your application or it can terminate abruptly. "

yes yes yes..
Sorry, for late response.

We can have auto code-review using SQL Server Database Project in Visual Studio.

Please find attached snap shot file here.
Auto_DB_Code_Review_v1.0.png
marrowyungSenior Technical architecture (Data)

Author

Commented:
very good sir, any steps by steps on how to setup it up ?
Top Expert 2012

Commented:
forget about that, the author just say attend his session. I am focusing on the link by Klaus Aschenbrenner.
Do you comprehend English?  Aaron Bertrand is the one that created the solution.  Again you may not like it, but it does work.  Period.
Top Expert 2012

Commented:
Vitor,

That's a nice trick and good as an academic solution but let be honest, it's hard to manage in the real world.
And I do not disagree with you.  The point that I was making was the author specifically stated "it seems not working much at all",  that is clearly wrong  and rather sad when a quick 5 minute test would have confirmed otherwise, even if they had no clue that Aaron Bertrand is one of the leading SQL Server gurus.
I'll prepare a document for step-by-step and let you update here.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Anthony,

"Do you comprehend English?  Aaron Bertrand is the one that created the solution.  Again you may not like it, but it does work.  Period. "

I only focus on that article and the comment down the page is valuable, I am not focus on WHO WRITE that.

I am not here to talk about person, there are nothing personal here.

"even if they had no clue that Aaron Bertrand is one of the leading SQL Server gurus. "

that's why you are so nerves on that. I respect anyone here who even can't help at all. I just like contribute and share ideas.. the object of EE. the reason why I come here.

they like it or not is up to them, but I understand them.. we are using similar effort here. human eyes to check.

MohitPandit,

tks. you are doing great. just like Victor, I see helpful sense.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial