Permission issue with LINQ ?
in [Programming SQL]
|
Prev: Decode XML Data Type
Next: Need to recover my database
From: joker197cinque on 4 Aug 2010 04:36 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 ? Thanks.
From: Dan Guzman on 4 Aug 2010 08:40 > 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 ? One method is to use LINQ with stored procedures. See http://msdn.microsoft.com/en-us/library/bb386946.aspx. > Which are best practices in this case ? I don't think there is a single Best Practice because it depends on your environment. If the database supports a public facing web site and contains sensitive data, I would opt for exclusive access via stored procedures to mitigate security concerns. Stored procedure interfaces can also insulate applications from the actual database schema, which can allow database refactoring without app code changes. This is especially useful if you have separation of database and application development responsibilities in your shop. The downside of course the additional development effort is needed to to create and maintain procs. OTOH, direct table access is more palatable for internal applications and/or development organizations that have less specialization of development roles. Direct table access will likely speed development, but at the cost of tighter security and perhaps performance. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "joker197cinque" <joker197cinque(a)gmail.com> wrote in message news:130356cb-d34c-418f-b467-03ea3550cd20(a)t20g2000yqa.googlegroups.com... > 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 ? > > Thanks.
From: joker197cinque on 4 Aug 2010 09:36 On Aug 4, 2:40 pm, "Dan Guzman" <guzma...(a)nospam-online.sbcglobal.net> wrote: > One method is to use LINQ with stored procedures. Seehttp://msdn.microsoft.com/en-us/library/bb386946.aspx. Using this way I would migrate all logic into SPs, right ? I don't think our developer will be happy :) .. they like very much to work with LINQ. > If the database supports a public facing web site and contains > sensitive data, I would opt for exclusive access via stored procedures to > mitigate security concerns. Unfortunately, the developing environment is already well- established ... and switching to stored procedures is not an option. > OTOH, direct table access is more palatable for internal applications and/or > development organizations that have less specialization of development > roles. Direct table access will likely speed development, but at the cost > of tighter security and perhaps performance. I totally agree. I would search for mixed solutions to implement in our company. Thanks Dan.
From: Erland Sommarskog on 4 Aug 2010 16:05 joker197cinque (joker197cinque(a)gmail.com) writes: > Unfortunately, the developing environment is already well- > established ... and switching to stored procedures is not an option. Well, that depends on your political skills. "Assinging direct table permissions is not option for security reasons". Yeah, it is not going give you very many votes for the "buddy of the month". Then again, if it is unacceptable to give security permissions on the table from a security perspective it is. But usually it depends on how you architecture the application. If users can only access the database through the application, then permissions is no issue at all. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Dan Guzman on 4 Aug 2010 23:26
> If users > can only access the database through the application, then permissions > is no issue at all. Unless of course, there is a SQL injection vulnerability in the application. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ |