Balancing security needs in ADO.NET applications

[Guest]

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?

 

[Norman Yuan]

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. Say, userA is given permission to
only execute SP1, which only does a "SELECT" on a table. So, userA can only
do "SELECT.." from a table, nothing else. UserB is given permission SP1 and
SP2 (doing "UPDATE..."). So, userB can both "SELECT..." from the table and
"UPDATE..." to the table.... No matter userA and userB connect to the
database from where, your app or their own "secret tool", they can only
execute the given SPs, nothing else.

 

[William Vaughn]

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)
-----------------------------------------------------------------------------------------------------------------------

 

[Guest]

Thank you for taking the time to reply. I had not considered leveraging SP's
to return table data in this way nor limiting exposure of base tables. I
naively took the approach of creating datasources and bound controls directly
in VS, the way the VS designers facilitate this approach. I always assumed
that the intent was to create the security in SQL Server itself. I guess that
a different paradigm is in order. I probably can still leverage datasources
and binding, but just not in the original way I got data from the server. I
look forward to learning more by way of the book.

 

[Guest]

Thanks Norman for taking the time to address my question. Your suggestions
and the ones from Bill in the following post agree with using SP's as the
tool of heavy lifting and security. I'll give it a shot. Thanks.

 

[Guest]

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.

 

[William Vaughn]

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)
-----------------------------------------------------------------------------------------------------------------------