May 25, 2011 at 8:02 am
Good day: Developers are requesting to use LINQ to access our production database. I have some concerns regarding security and LINQ and not using parameterized stored procedures in LINQ. My philosophy is, just because it is easy does not mean that is should be used, however, if using LINQ does not pose any security risks, it may be allowed. Could someone give me some insights on security and LINQ?
This application is a web application that is open to pretty much everyone. I also, have been working on the premise that direct table access is not the best way to go, particularly with a web application. All access is or will be thru stored procedures only. My understanding with LINQ is that in many cases, direct table access will be required(??).
thank you!
May 25, 2011 at 9:57 am
Why do the developers need access to your Production Systems?
The following article addresses some security concerns of LINQ:
http://blog.statusnotquo.com/2011/04/11/security-considerations-for-linq-to-sql/
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 25, 2011 at 10:08 am
hello, thank you for the link. I had already read that article, but a second read is always good too.
I can feel a rant coming on 🙂
OK. So Microsoft has abandoned the notion of not allowing direct table access? Which to me, sounds a little risky. I have read the articles regarding these, there are not too many and they all say the same thing, basically "go ahead, allow direct table access". What I would like to know is do dba's really allow this? They have gone along with Microsoft and abandoned one of the security measures for a database? I can read the articles, but has anyone out there with a database accessed by users on the internet really allowed this? Sorry, I am stunned. Particularly in this day and age when not only are there more and more hackers out there with nothing better to do than try to get into a database, but laws regarding personal information are becoming more strict, and this just seems to be an open door.
Sorry for the rant! I guess, I will go with not allowing direct table access until I am really comfortable with the security and the developers' abilities to write sql that will not bring the db to it's knees 🙂
Thanks
May 26, 2011 at 12:15 pm
Linq is created by developers for developers. Don't expect DBA level thinking in it. This isn't unusual. As a DBA I just recently took the SharePoint 2010 Installation and Configuration course. Found a number of glaring issues we'd never allow as a DBA. Also found an AD issue (I was a systems and security architect until recently when I returned to a senior DBA role). So I'm not surprised.
K. Brian Kelley
@kbriankelley
May 26, 2011 at 5:17 pm
Hello, thank you all. To answer a couple of the comments: For the most part developers do NOT have access to the production environment and those that do, I am working on :-). I did speak to some colleagues one of whom is an application architect and the other is a senior dba. In that organization, LINQ is allowed as an extension of C# only, no LINQ to SQL is allowed, database access is through the Entity Framework. On the database side, the dba's only allow access through stored procedures unless for some reason a 3rd party application cannot deal with that. Mr. Kelley, thank you. I was wondering if I had completely lost my mind. But after speaking to my colleagues and posting here, I feel much safer in my call and in fact management agreed with me, so I guess all is well.
Thanks again for the assistance and comments!
May 27, 2011 at 3:44 pm
You can bind LINQ to stored procedures, but it would involve more coding. As a compromise you could offer to create a collection of views for use by their application. That would limit access and also abstract them from some of the more trickier joins.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 28, 2011 at 3:32 am
linq usage doesn't need to be less secure !
Using linq as an excuse for open doors is the summum of laziness
some refs on using sprocs with linq:
http://www.mssqltips.com/tip.asp?tip=1542
http://blogs.msdn.com/b/zainnab/archive/2007/10/25/linq-and-stored-procedures.aspx
http://msdn.microsoft.com/en-us/magazine/cc500645.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 28, 2011 at 10:11 am
Hello again! I agree, using Linq with stored procedures, I believe would work quite nicely. I have no objection to that at all. I was only concerned regarding the request to allow direct table access to a database for an internet application. Laziness is not an option. If it takes more code on the development side or the dba side, it matters not, the safety of the data is what matters 🙂 I think, however, that we will definitely investigate the entity framework in conjunction with Linq, stored procedures, etc., this could be an avenue for future development.
This is why I post questions here, discussion is important and one answer is not always the best or only answer and hearing from as many experts as possible is important.
thanks!
May 31, 2011 at 7:47 am
Ukon (5/28/2011)
Hello again! I agree, using Linq with stored procedures, I believe would work quite nicely. I have no objection to that at all. I was only concerned regarding the request to allow direct table access to a database for an internet application. Laziness is not an option. If it takes more code on the development side or the dba side, it matters not, the safety of the data is what matters 🙂 I think, however, that we will definitely investigate the entity framework in conjunction with Linq, stored procedures, etc., this could be an avenue for future development.This is why I post questions here, discussion is important and one answer is not always the best or only answer and hearing from as many experts as possible is important.
thanks!
Granting the application account (especially a web application) db_datareader, DBO, or SYSADMIN membership is a very bad idea from a security standpoint; that's database administration 101. It like giving the office receptionist the keys to the bank vault.
If the app developers want the flexibility to perform ad-hoc filtering, grouping, etc., then create for them some views that join only the specific tables they need and filter out any datasets that they should never have access to. Start by creating an account with public membership, and then grant the account with nothing but EXEC permission on specific stored procedures and SELECT permission on the above mentioned views. That's all they need; it makes no difference wether it's to facilitate LINQ or ad-hoc SQL coding; the same rules apply.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply