Permission issue with LINQ ?
joker197cinque
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.
Dan Guzman
> 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" <joker19...@gmail.com> wrote in message
news:130356cb-d34c-418f...@t20g2000yqa.googlegroups.com...
joker197cinque
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.
Erland Sommarskog
> Unfortunately, the developing environment is already well-
> established ... and switching to stored procedures is not an option.
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, esq...@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
Dan Guzman
> 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.
Erland Sommarskog
>> 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.
with SQL injection with it.
--
Erland Sommarskog, SQL Server MVP, esq...@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
joker197cinque
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.
joker197cinque
> 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.
Erland Sommarskog
> On Aug 4, 10:05 pm, Erland Sommarskog <esq...@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.
Dan Guzman
>> 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.
Erland Sommarskog
> 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.
If the do
sql = "EXEC some_sp '" & param_1 & "'"
Nothing has changed.
And don't laugh. The system I work with, have plentiful of that.