[dba-SQLServer] Job Change, and a Question

I understand your dilemma, Liz, but I feel most compelled to protest your developers' insistence on running SQL directly, for at least two reasons: SQL injection is difficult to defend against when wild-ass SQL statements are coming in, and this method implies no clearly-thought-out security (roles/users/etc.). In your position, I would resolutely stand my ground in favor of sprocs/UDFs/views, all of which have been locked down with roles. A few really basic guidelines, not intended to be exhaustive:

1. NOBODY gets direct access to tables, except you (and your team, and even then, think carefully about the possible damage someone could do -- the damage might be accidental or might stem from a disgruntled employee).

2. For each table, there should be 4 sprocs -- insert, update, delete and select. NOBODY should be able to touch a table without going through your sprocs. (These, incidentally, can be generated, although you might need to tweak the code.)

3. Clearly define the roles that various users and employees and managers and developers might occupy. A role means, oversimplifying, that Role A can do this and that, Role B can do everything Role A can plus some other things, and Role C can do everything Role B can, plus some other things. Once you have a clear handle on the roles, you can define access to the various sprocs. To take just one example, in my last app only three people (all in one role) had the right to insert, delete or update the Countries table. Similarly for the States/Provinces table (it is rare that Canada adds a province, USA adds a state, Switzerland adds a canton, etc. On the other hand, the Cities table will probably not be exhaustive, so any given salesperson should be able to enter one.

Fight the developers tooth and nail on their preference for dynamic SQL. Be politically savvy about it, and ask them how to fire a stored procedure in their language. Learn as much as you can about the intricacies therein. Find out if their language lets them fire UDFs and views, too. But guide them toward relying on the back end, and gracefully point out the risks inherent in dynamic SQL. (If you need info on this, google SQL Injection.)

This is perhaps more information than you wanted.

Arthur
 
Dear SQL Server List,

It is no doubt due to lurking on this list that I have now landed myself in
the kettle of soup I'm in. 

Two weeks ago, I was a all-Access developer at a tiny almost-all-Access
shop.  Now I am the SQL Server DBA on a new project internal to Wells Fargo.
That I was able to stumble through the interview satisfactorily was due to
you folks having put enough concepts in my head so that I could avoid
sounding like a complete idiot.

f course, now that I've been on the job for a week, the veneer of knowledge
is looking thinner and thinner, and the number of questions I can't answer
is growing daily.

Here's the most recent poser:  They're using SmallTalk for this project,
which is actually a major extension of another application which has been in
use for 10+ years.  They are devoted to using Windows authentication.  The
developers would like SmallTalk to be able to run SQL statements directly
from their code, however, they can only do that if the logged-in user has
such rights.  Which raises the specter of a savvy user running random SQL
statements directly against the database.  The solution has been to disallow
running any "unapproved" SQL statements by requiring that only sprocs can be
run, but the development team isn't happy with that solution, so they are
asking me for alternatives.

I'm getting more confused as I write this, so I'll guarantee you that there
are questions I don't know enough to ask. Can you recommend some reading for
me?  Do any of you have any specific ideas for this problem?  

Thanks so much!  Hopefully I'll be wise enough to contribute here
eventually!
 

Liz