Permission issue with LINQ ?
in [Programming SQL]
|
Prev: Decode XML Data Type
Next: Need to recover my database
From: Erland Sommarskog on 5 Aug 2010 03:34 Dan Guzman (guzmanda(a)nospam-online.sbcglobal.net) writes: >> 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. So true. Not that I am a fan of LINQ, but at least there is no issues with SQL injection with it. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: joker197cinque on 5 Aug 2010 04:44 On Aug 5, 5:26 am, "Dan Guzman" <guzma...(a)nospam-online.sbcglobal.net> wrote: > Unless of course, there is a SQL injection vulnerability in the application. LINQ uses, AFAIK, sp_executesql that is not prone to SQL Injection, isn't it ? What other point of risk do I have to evaluate about SQL Injection ? Thanks for help.
From: joker197cinque on 5 Aug 2010 04:50 On Aug 4, 10:05 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > 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". Sorry, my english is not so well to understand. Are you saying that "Assinging direct table permissions" could really be a NEGATIVE sign about my work in company ? > Then again, if it is unacceptable to give security permissions on the table > from a security perspective it is. You mean that, if it is a security risk, it IS a security risk ? > 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. Actually, it is related to application user. In our environment, developers can only connect to dev server not to the production. About production server, applications use SQL users to do work ... and that user is datareader/datawriter role member. Probably working politically hard I could switch all insert/update/ delete operations to SPs and cut off users from datawriter membership. What do you think about that ? Thanks.
From: Erland Sommarskog on 5 Aug 2010 15:18 joker197cinque (joker197cinque(a)gmail.com) writes: > On Aug 4, 10:05�pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: >> 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". > > Sorry, my english is not so well to understand. Are you saying that > "Assinging direct table permissions" could really be a NEGATIVE sign > about my work in company ? What I am saying is that being stubborn and insisting on an issue is not going to make you popular among your colleagues, no matter whether you are right or not. >> Then again, if it is unacceptable to give security permissions on the >> table from a security perspective it is. > > You mean that, if it is a security risk, it IS a security risk ? Right. There is always compromises, and security and flexibility rarely go well together. If management don't understand security well, you may have an uphill battle. > Actually, it is related to application user. > In our environment, developers can only connect to dev server not to > the production. > About production server, applications use SQL users to do work ... and > that user is datareader/datawriter role member. > Probably working politically hard I could switch all insert/update/ > delete operations to SPs and cut off users from datawriter membership. There is maybe one thing I should clarify here. I can't say what is right or wrong for you. It very much depends on what your system is doing, what business you have. In some organisations, it is perfectly acceptable that users can connect with any tool to at least read all data there is. In others this is completely unacceptable. An example of the later is a journaling system in a hospital. In Sweden at least, all read access must be logged. That is, if you want to take a sneak peak on my journal, you may be able to (because we can't tell if you will be on dury when I get urgently ill), but it has to be logged, so we can tell afterwards if you really had the right to look at it. In such system all direct access to the database is out of the question. So that is the first question to answer: how sensitive is the data? Next is how the application works. There are a couple solutions where users really can't do anything in the database outside the application. If your application is implemented that way, the whole point is moot. It is also worth pointing out that stored procedure is not a miracle for security. Let's say you have an employee database that users can access directly. There is little difference between: UPDATE employees SET salary = 2*salary WHERE employee_id = @myid and EXEC employee_update_salary @myid, 1000000 The one thing you can win with stored procedures it that you have better control over data integrity. The stored procedures may implement business rules, that the users side-step if they update data directly, and thereby causes a mess in the data. And here I'm not talking about malign changes like faking your salary, but well-intended changes like flipping a switch, not knowing that you are supposed to update another table because of this. But again, if you can keep users outside the database without using the application, there is not much of an issue here. -- 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 5 Aug 2010 22:19
>> Unless of course, there is a SQL injection vulnerability in the >> application. > So true. Not that I am a fan of LINQ, but at least there is no issues > with SQL injection with it. Thanks for the addition, Erland. Just to be clear, I did not mean to insinuate that LINQ had SQL injection vulnerabilities. Rather, the practice of using stored procedures mitigates the risk of SQL injection regardless of the method used to access the database. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ |