Revisit What You Know
-
Your point was spot on, Andy... I'm just spinning it the other way...
Step back and look at what you're doing... is getting it quickly always worth more than getting it done right even if it takes a little longer. Mal D's example is not atypical of the kinds of problems we've all seen. Like Steve said, it's a risk you must weigh... I just weigh heavily towards not having performance or scalability issues in the future and would hope that more folks take on that attitude.
Perhaps a good followup for all of this is in the form of the following editorial titled "The Cult of Mediocrity"...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=263&messageid=387191&p=1
--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) -
Hey we all like new and fast hardware! Of course godo efficient code combined with fast hardware is even better!
Sometimes though, good enough is just that, good enough. Take RAID 5 vs 10. Sometimes RAID 5 does what it needs to do. Of course combined with good efficient code, RAID 5 is more likely to be able to do what you need it to do.
The problem with getting efficient code into production is that, say for me where I am now, I am the only DBA supporting about 25 developers. There is no way for me to have time to review every item that goes into production for efficiency. I do look at them close enough to see if there are any obvious glaring performance gotchas in the code (and I have enough experience to be able to do so). But there is no way for me to spot every single performance problem.
Now don't get me started on XML....
-
In the original article Andy posted the following:
Let's start with a common example. If I asked you which is the best practice, using Windows authentication or SQL logins, I bet most of you would say Windows authentication. MS does, right? My follow up question is - are there any caveats to that best practice? To that question I suspect I wouldn't get many answers, maybe something along the lines of make sure users are required to have a strong password, to change passwords frequently, etc. Good advice and certainly should be part of the package, but there is a bigger gap that usually is left untouched.
The problem? Windows authentication grants the user the right to do certain things in SQL Server. How many of you are comfortable with your users executing stored procedures directly without the benefit of an application or business layer wrapped around it? Or running queries against tables directly if you have granted table access? I would guess not many DBA's are going to be in favor of that, but that's what Windows authentication allows.
We fall back on security by obscurity, hoping that among all our users who have access none will discover the name of the SQL Server and have the skills to....oh, maybe start up MS Query, or run a few lines of VBScript that they found on the web. So what would my answer be? Windows authentication when used from a web server or application server, but never grant anyone outside of IT the ability to access the server using their credentials and if at all possible, deny them access to data servers entirely by means of firewall rules.
--
I have used SQL security in our internal applications for more than 5 years now. This is a manufacturing environment that has computers out on the plant floor, where the users log in with a generic windows login that is very limited in scope. Using SQL security has provided me with more granular security, since each user can logon as themselves. It also allows a supervisor (or me) to logon to the application as themselves (myself) without having to shut down the other applications running on the computer. The users have to log in to the application to get the SQL server logon and access.
I elected to use Access ADP's as the front end to our applications and it has served us well. All the coding was in VBA and I have used stored procedures to provide access to the data, which meant that I did not have to give the user access to the tables themselves, but only execute rights to the stored procedures. Because ADP's have been removed from Access in Office 2007, I am now trying to move to developing in Visual Basic. However, the examples in most Visual Studio (Visual Basic) books use integrated security.
So I had just about decided that, even though I like SQL security, and it has worked well for us, to switch over to integrated security. In fact, I have built some Active Directory security groups paralleling my SQL server database roles, and was going to do some testing today. Then I read this article! Andy brought up some down sides that I had not thought about, as far as using Integrated Security.
Are there any other pros or cons to SQL Security or Integrated security (without writing a book!) that I should be considering?
-
Carla,
I'd actually ask you start a thread in the SQL2K or SS2K5 Security areas on authentication. It's worthy of it's own discussion.
I will say it's good you're looking into it and questioning you're decision. Don't get too bogged down though. As you question what you know or think, learn more, get some opinions and facts, and then make a decision and have faith in it. You can question things when it's a new decision point, but once you've made your decision, don't look back too much. Focus on supporting your decision the best you can.
-
I'd kinda like to see your personal standards document. Sounds like an article in the making...
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply