March 5, 2009 at 10:44 am
Hi all. I wanted to get some feedback from other database professionals regarding applications accessing production database servers. The grand old debate has surfaced in the workplace: should we allow programmers to use Link to SQL or the new Entity Framework to manipulate data or should we lock the data layer utilizing stored procedures? I personally feel programmers should go through stored procedures and not have direct table level access but would like to hear other points of view.
March 5, 2009 at 10:47 am
My personal preference, and the recommended security best practice, is to use stored procedures. In that way ownership chaining can be used and direct access to the base tables is prevented. This is especially crucial if users come in via the app as their own Windows credentials. Because in this case there's nothing stopping them from connecting via another application (Excel, Microsoft Access, etc.) and doing damage unexpectedly.
K. Brian Kelley
@kbriankelley
March 5, 2009 at 10:51 am
Exactly my thoughts. I am trying to fight the battle with SAS70 compliance but can't find much ground to stand on. I am basing my argument primarily on best practices.
March 5, 2009 at 11:30 am
SAS70, as you've probably realized, is extremely subjective and really dependent upon the caliber of folks from that certified accounting firm. Hard to argue meeting SAS70 as a reason.
Your better bet would be to discuss attack vectors. Formulate what can be done, how, why. Write up what can prevent. Put a cost # down for both sides. Ultimately it is the business' data and it's a business decision. Don't forget to show that if they aren't using proper coding techniques, SQL injection can occur, even for a fat client app. If code review isn't being done, it is entirely possible for a developer to slip in bad code. That's what triggers the possibility for your SQL injection attack vector.
K. Brian Kelley
@kbriankelley
March 5, 2009 at 11:35 am
I appreciate your feedback. I am putting some documentation together on best practices and hope to get the winning vote from upper management.
March 5, 2009 at 2:25 pm
An interesting topic on EF I was reading today ...
http://sqlskills.com/BLOGS/KIMBERLY/post/Seriously-are-you-kidding-me.aspx
In our organization as a DBA I don't get much say :(. Yaa it is sad, but we ended up using a open source production I can't seem to recall its name of head right now. But I was given the line "we don't want to code for SQL Server". They said what if SQL Server it not around next year, I was like hmm stop kidding yourself heh.
But I find using EF, or even this open source frame work that abstracts the backend leads performance issues and security issues. Beside I am not being fan of SQL Accounts; I like to use integrated security when possible. And at least this open source tool doesn't let me. So once someone can get the password to the account they can do anything becuase no Stored Procs.
Few of my buddies hate the idea of Store Procs because they don't have control any more meh -.-.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 5, 2009 at 10:30 pm
Since others have touched on what might be best practice or shared their opinions, I'll touch on another subject that doesn't seem to have been addressed.
Who has responsibility for the performance of the server, security of the data, and integrity of the data?
If the DBA is going to be held responsible then it would be wise to require the front end developers to use stored procedures (SPs) and views assuming the DBA also has the authority to review the code in the SPs and views prior to allowing them to be put into production. There's nothing worse than having the boss ask you why all the data is missing from a table when you don't have any control over the code being executed. Worse yet, having the boss ask you why all the data appears to have been corrupted by an errant update.
March 6, 2009 at 12:23 am
Heh Ed,
I going to point you to http://sqlskills.com/BLOGS/KIMBERLY/post/Whose-job-is-it-anyway.aspx :P.
heh similar thing being discussed there :).
I think data is ultimately DBA's responsibility. I feel DBA should know what code is about to be executed on their server; however that said I don't expect DBA to know the business logic. But as a DBA I always scan developer script for obvious mistakes; like SELECT * From huge table; delete without where clause; forgetting permissions, etc. Too many I think to list and I am sure every DBA has had their share ...
I think of it this way I have to backup, plan for DR, security and performance. All that is involve around DATA; so DATA is DBA responsibility. Yes the ownership of the data belongs to business, as for the security and safety of the data thats in DBA's hand. Make or Break I thinks.. :Whistling:
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply