Re: Balancing security needs in ADO.NET applications

You're working under the assumption that TableAdapters need to be sourced from base tables--they don't. See my article http://www.developer.com/db/article.php/3693236 that shows how to put together a hierarchical TableAdapter using SPs. No, it's not as easy as it should be, and virtually every demo you see will use base tables, but it can (and should) be done. The other pundits and I have been on their case for a decade to support SPs in these tools. They're getting closer, but remember that many of their customers are using toy/home databases where SPs don't really make much sense or aren't supported.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"michael" <michael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:21E98694-2E7A-4A7B-A7C6-C8AFB0E942B7@xxxxxxxxxxxxxxxx
Hope you're still watching this thread.

After thinking about this for a bit, it seems that the whole idea of using
TableAdapters, because of the direct access to the base tables, has a serious
security flaw. Since these objects don't operate through SP's (and therefore
you can't control security at this level) user access is determined by their
SQL Server credentials or the application's logon/pw. If the SQL credentials
allow DELETE, there's nothing to prevent a user to use SSMS and wipe out
data. If the user can extract the application's logon/pw from the code, the
same thing is possible.

If my assumption is correct, then I'm sort of surprised that MS would
promote this kind of architecture to end user developers.

Unless they put a whole lot of faith in the ability of obfuscation to
protect an application's logon/pw.

--
Michael


"William Vaughn" wrote:

There are several approaches that can be taken--this is what I recommend for many applications:
a.. Do not expose base tables at all. This is the foundation of the remaining strategies.
b.. Create Views that return focused subsets of the tables as needed by the applications, but do not grant "write" access.
c.. Create stored procedures that manage the changes to the base tables. Since many operations involve more than a single table, these procedures can deal with the complexities of managing business rules and RI. Grant access to these SPs to specific accounts created specifically for the applications that invoke them.
d.. Create user accounts for the application--not the individual. That way the user need not know what credentials are used to gain access to the data. If the credentials are discovered, all they can do is run specific SPs that carefully guard the data and do not permit gross operations like dropping tables or changing rights.
e.. Manage user access to the applications through your own means using Windows authentication with login rights management that has no correlation to the rights granted to the application. For example Sam clerk signs in to Windows and runs the accounting application which asks him to log in. These credentials are validated by the application and grant Sam specific rights and enable portions of the application that only apply to him. When Sam is fired or moves on, you simply drop him from the list of valid accounts. This approach also permits you to log all of Sam's operations and grant him just the rights he needs--and no more.
I discuss this at length in my book.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"michael" <michael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:8029BF55-5287-44FF-9158-D83C181CC6E3@xxxxxxxxxxxxxxxx
> What's the best way of having a .NET application interact with SQL > server
> without compromising security? Here's my issue:
>
> Let's say you want a user to be able to read, write, and delete records > from
> a SQL Server table. It's simple enough to give the user logon > credentials on
> SQL Server allowing them to do just that. Then, a .NET application can > use
> those credentials to consume the data. But let's say that same user has > some
> saavy and uses his/her credentials with SQL Server Management Studio > for the
> purposes of evil?
>
> Alternatively, you could use your own credentials to logon to the SQL > Server
> from within the application preventing the user from using a tool like > SSMS.
> However, then you're storing your credentials within application code > which
> could be dissected and recovered (unless obfuscated).
>
> Or maybe write a "middle tier" that alone interacts with the SQL > Server. The
> application would only have to leverage the middle tier's own security > scheme
> and not SQL Servers.
>
>
> What's the best way?
> -- > Michael

.

Relevant Pages

  • Re: Balancing security needs in ADO.NET applications
    ... The simplest to prevent such kind of potential "evil" user is the only give permission of executing certain SPs, used by that application, to that user, no access to any other SQL Server objects. ... This way, the only thing he can do when connected to the SQL Serer database is to execute those SPs, which is the same when he uses the application. ... It's simple enough to give the user logon credentials on ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Login failed for user . The user is not associated with a trusted SQL Server connection.
    ... he never mentioned he is impersonating in asp.net - so no delegation needed. ... Cassini runs with the credentials of the interactive user - which seems to have access to sql - in contrast to the local ASPNET account - which i am trying to tell him since 2 days.... ... yes - use explicit credentials and enable mixed mode auth in sql server to get this to work. ...
    (microsoft.public.dotnet.security)
  • Re: Balancing security needs in ADO.NET applications
    ... you can't control security at this level) user access is determined by their ... SQL Server credentials or the application's logon/pw. ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: IIS 6 and SQL Server - two seperate boxes
    ... make use of a risky solution. ... login to your SQL Server is that exploiting a "known vulnerability"? ... And the credentials could be stolen on ... the server (e.g. from a web.config file if you are using a static account), ...
    (microsoft.public.inetserver.iis.security)
  • RE: SQLXML Newbie Question...
    ... create SQL Server logins, but how do you pass the credentials to the service? ... Mark ... I want to plae my web service on the public internet but it needs ...
    (microsoft.public.sqlserver.xml)