November 7, 2009 at 3:15 am
No, you cannot select on just that dummy column, because if you read the original article he placed the DENY on that specific column with this statement:
DENY SELECT ON OBJECT:: dbo.Table_1(DummyColumn) TO test_user;
GO
He created this dummy column on this table unnecessarily just so he could prevent a SELECT * on the entire table. Not a very practical solution at all IMHO ... Cute? Yes Practical? No. Always remember, DENY overrides ALL other permissions, so be very careful with where and how you use it. As I said before, you can really mess things up bad quick...Aa an alternative, always educate your development and BI staff first with creating a centrally accessed database standards document before resorting to coding gimmicks into your databases... 🙂 KISS.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 8, 2009 at 9:26 pm
Scott.Deagan (11/5/2009)
Another solution that works well in most situations is to simply avoid allowing users to execute SQL statements at all - ensure that all access to tables is performed via stored procedures.
Technically a good discovery and can work wonders in some places. But looking at practicality, it is not workable and should not be done in most of the situations.
1- Why would you want to break the code and create an erring system?
2- I may sometimes want to make a quick query for some health check of the solution. Why would you want me to write a full length query?
As a DBA or SQL experts, we should focus on taking our developers to the next level. Age old recommendation - train them and groom them to writing a better and non-breaking code.
Genarally code reviews are done and a reviewer can reject implementaion of this code. For code review - you can have some tools so that you don't spend many hours for each review.
November 9, 2009 at 3:45 am
An interesting article…
November 9, 2009 at 4:53 pm
Its always better to educate others with its pros and cons. Select * is advisable in certain scenarios but also harmful in some scenarios.
Good post although but practically not advisable!!!
Hiren Shah
:kiss:KISS (Keep It Simple Stupid) :kiss:
November 9, 2009 at 4:58 pm
Completely agree to Kit!
Hiren Shah
:kiss:KISS (Keep It Simple Stupid) :kiss:
November 10, 2009 at 11:27 am
princess.lipscomb (11/5/2009)I believe they are going to stop letting us * pretty soon anyway
Bollocks! I'll eat my knickers if that happens!
November 11, 2009 at 3:39 am
sorte.orm (11/10/2009)
princess.lipscomb (11/5/2009)I believe they are going to stop letting us * pretty soon anyway
Bollocks! I'll eat my knickers if that happens!
In some ways the restrictions have already started with the SQL2008 Management Studio, as the Open Table function has been removed, and replaced with a Select Top 1000, and Edit Top 200 Rows option.
I would be very suprised if Select * ever disappears, while not best practice its great when you are diving around an unfamiliar Database or one that doesnt have any documentation or data dictionaries because the DB producers dont want people understanding the structures for commercial reasons.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 11, 2010 at 8:46 am
Jason Lees-299789 (11/11/2009)
I would be very suprised if Select * ever disappears, while not best practice its great when you are diving around an unfamiliar Database or one that doesnt have any documentation or data dictionaries because the DB producers dont want people understanding the structures for commercial reasons.
I too would be very surprised if it disappears. I even believe that in some circumstances it is actually best practise (typically a stored procedure whose function is to deliver all information about an object of a specific type, where the object is identified by parameter(s) of the SP - if we add extra information about objects of this type to the schema in one of the tables concerned, we don't have to change the SP. Given normalisation and language translation options (one of the parameters of the SP may specify what language text information is to be returned in) the query may be pretty complex.
Of course this requires application developers to write things so that extra fields in the record won't throw them, and to access fields by name and not by position (number) - but that's good coding practise anyway. It also reuires the DB designer not to give two different attributes of the same object the same name, just because they are held in two different tables - but that is essential anyway if want the schema to be understandable, which we usually do. (Some parts of the application must need the new data or it wouldn't have been added; these will have to change to use it; if there are any parts that don't need it they don't have to change.) Of course there are far more cases where "select *" is horribly inappropriate.
Educating people as to where "select *" is appropriate and where it is not, and using peer code review to try to enforce a restriction that it is never used where it is inappropriate, as suggested in many earlier comments, is the sensible way to go. If neccessary, one could add code review by a DB expert until education has brough peer review up to speed.
But many of the comments to date have been disappointing (particularly the one which attributes the locking and network swamping problems caused by getting millions of rows instead of just the one or two rows needed to use of "select *" instead of to failure to use filters in WHERE and ON clauses appropriately). Why do people take such extreme positions against something as useful as "select *"?
Tom
Tom
January 11, 2010 at 8:55 am
I believe I made that comment, and it was not ever intended to say that SELECT * should be removed--as you rightly say, it is a useful development tool. I *do* believe that it has no place in production code, though.
January 11, 2010 at 9:06 am
paul.knibbs (1/11/2010)
I believe I made that comment, and it was not ever intended to say that SELECT * should be removed--as you rightly say, it is a useful development tool. I *do* believe that it has no place in production code, though.
I can generally agree with this statement. Just beware though, the methods mentioned in this article and other comments will prevent the ability to SELECT COUNT(*) from tables.
November 30, 2010 at 7:27 am
I just thought I should share an article[/url] by Thomas LaRock (SQLRockstar) on possibly a better & way more practical option to avoid SELECT *. Keywords: sysdepends & PBM.
February 11, 2011 at 5:52 am
I must have missed it the first time it was published.
Smart and interesting. Well done.
I will probably never use it for all the reasons that others have pointed out.
-- Gianluca Sartori
February 11, 2011 at 6:00 am
Raghuram (AJ) (11/30/2010)
I just thought I should share an article[/url] by Thomas LaRock (SQLRockstar) on possibly a better & way more practical option to avoid SELECT *. Keywords: sysdepends & PBM.
That is a great blog post, thanks for sharing.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2011 at 6:52 am
It is really simple- employ decent developers
If you pay peanuts you get ….
A good organisation will understand the worth of their data.
February 11, 2011 at 6:54 am
ianemery-640190 (2/11/2011)
It is really simple- employ decent developersIf you pay peanuts you get ….
A good organisation will understand the worth of their data.
If everybody did this, how would junior developers get employed?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 61 through 75 (of 140 total)
You must be logged in to reply to this topic. Login to reply