Re: Grant access to applications only
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 27 May 2008 14:47:56 -0700
ulrich schumacher (ulrichschumacher@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Is there a way to allow a .net application write access to sql server
but to deny a direct user login e.g. via management studio.
The background is that the users must not be able to manipulate the data
directly but only within the application. Integrated security should be
used as authentication type.
There are a couple of ways to achieve that, but all have quite
hefty repercussions:
* Make all access through stored procedures, and remove direct access to
the tables. Good start, but if users can access SSMS, they can run the
procs manually, so you need to have your procedures to do all business
logic.
* Use application roles. This is only good for a three-tier application,
where you can hide the password in the middle layer somewhere the
users cannot access it. On a two-tier application, the users can always
find the password if they want to.
* Use a proxy user. Again, this requires a three-tier application. The
middle tier authenticates the users, connect to the database, and
then impersonate the users. The users however, do not have their own
login to the server.
Undoubtedly, this last method is the safest.
In short, to achieve what you are asking for, you need to design
your application for it.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- Prev by Date: Re: cross database query with ownership chaining
- Next by Date: Re: Encryption Best Practices
- Previous by thread: Re: Grant access to applications only
- Next by thread: cross database query with ownership chaining
- Index(es):
Relevant Pages
|
