locked
Security issues using LINQ ? RRS feed

  • Question

  • SQL 2005 SP3 Standard.

    I used to assign execution permission to stored procedures; you all know the benefits of this choice.

    With the use of LINQ, developers move routines into Visual Studio, asking for datareader and datawriter permissions, with clear security problems.

    How can I do to mitigate this situation ? Which are best practices in this case ?

    What is the official position from Microsoft, about that ?

    Thanks.

    Wednesday, August 4, 2010 8:38 AM

Answers

  • I think that your question is one for which there are very passionate, but very different, opinions.  However, LINQ has not created these debates, but is just another tool that raises the questions again and again.

    Obviously, LINQ will work with stored procedures as this blog describes:
    http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

    I (a DBA) absolutely hate db_datareader and db_datawriter being used as the role of choice.  The principal of "least privilege" should still apply.  Having said that, each project will need to choose where and how it focuses security. 

    1. In the database, stored procedures, views, (maybe tables), etc. with specific grants and revokes to role.  Put Windows Logins or Groups into those roles.  Using stored procedures is the tightest security option.
    2. In the application, switches to a proxy account and does all access under that account in behalf of the person who runs the application.  (In a 2-tier environment, this has security exposure issues that can be exploited.)  In that case, perhaps the proxy account is db_owner (still not my favorite) and the Windows login that runs the application has no rights in the database. 

    The push-back is that it is 'easier' to fly along, using your tool of choice (LINQ, etc) and just writing code.  That is an issue that your organization will need to decide how to address.  Even for 2-tier projects, using stored procedures is a more secure approach and one that I encourage. 

    You should read through the topics that start here:

    http://msdn.microsoft.com/en-us/library/kb9s9ks0.aspx  SQL Server and ADO.NET

    Look at the topics SQL Server Security (ADO.NET) and LINQ to SQL.  Under LINQ to SQL, also see Programming Guide (LINQ to SQL) which leads to LINQ topics on SQL Server and on stored procedures.

    FWIW,
    RLF

    • Proposed as answer by Tom Li - MSFT Friday, August 6, 2010 12:07 PM
    • Marked as answer by Tom Li - MSFT Wednesday, August 11, 2010 10:59 AM
    Wednesday, August 4, 2010 3:03 PM

All replies

  • I think that your question is one for which there are very passionate, but very different, opinions.  However, LINQ has not created these debates, but is just another tool that raises the questions again and again.

    Obviously, LINQ will work with stored procedures as this blog describes:
    http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

    I (a DBA) absolutely hate db_datareader and db_datawriter being used as the role of choice.  The principal of "least privilege" should still apply.  Having said that, each project will need to choose where and how it focuses security. 

    1. In the database, stored procedures, views, (maybe tables), etc. with specific grants and revokes to role.  Put Windows Logins or Groups into those roles.  Using stored procedures is the tightest security option.
    2. In the application, switches to a proxy account and does all access under that account in behalf of the person who runs the application.  (In a 2-tier environment, this has security exposure issues that can be exploited.)  In that case, perhaps the proxy account is db_owner (still not my favorite) and the Windows login that runs the application has no rights in the database. 

    The push-back is that it is 'easier' to fly along, using your tool of choice (LINQ, etc) and just writing code.  That is an issue that your organization will need to decide how to address.  Even for 2-tier projects, using stored procedures is a more secure approach and one that I encourage. 

    You should read through the topics that start here:

    http://msdn.microsoft.com/en-us/library/kb9s9ks0.aspx  SQL Server and ADO.NET

    Look at the topics SQL Server Security (ADO.NET) and LINQ to SQL.  Under LINQ to SQL, also see Programming Guide (LINQ to SQL) which leads to LINQ topics on SQL Server and on stored procedures.

    FWIW,
    RLF

    • Proposed as answer by Tom Li - MSFT Friday, August 6, 2010 12:07 PM
    • Marked as answer by Tom Li - MSFT Wednesday, August 11, 2010 10:59 AM
    Wednesday, August 4, 2010 3:03 PM
  • Hi Russel, very thanks for your reply. There are so many concepts into that I have to take some time to read all of them :)

    I will be back in a few days to reply.

    Thanks again.

    Friday, August 6, 2010 7:27 AM
  • My choice is to create a user as db_datareader and give it exec permission on the SPs that updated data, the DBA was very happy with that approach since they freak out everytime they have to give an user write privileges and since the marketing team needs db_datareader for their Excel stuff the DBA couldn't refuse the proposal.
    Thursday, November 4, 2010 4:02 PM