Re: Multiple security contexts
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 28 May 2007 22:25:11 +0000 (UTC)
Eric Smith (EricSmith@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I've been going through the SQL Server documentation (2005), looking for a
way to solve the following problem:
Two ways of accessing SQL server, by multiple clients, viz.,
(1) Using a reporting tool, and access limited to read-only;
(2) Using a custom-built application, and access limited to read-only for
some tables, and read-write for others.
So-called "Trusted authentication" (SSPI) will be used for authentication
purposes.
In both instances (1) and (2) above I would like security to be applied on
SQL Server, and accesses logged accordingly. However, two access control
contexts should be applied to the same database instance *depending on
whether the user is accessing the database using the reporting tool or the
custom-built application*. Is this at all possible, without creating two
accounts per user?
One way out of the dilemma is to use stored procedure in the custom-
built application. Then the users would only need SELECT permissions to
be able to run the report tool, where I assume that they are permitted to
construct their own queries against the table. They would also need EXEC
rights on the stored procedures.
Another way out is to use an application role in the custom-built
application, but from a security point of view this is not defensible
if the application is a two-tier application. The app needs to send
a password to set the application role, and if the password is embedded
into the application, anyone who is dead set on finding it, will find it.
In a third-tier app, the password can be stored on the middle-layer server
in a place where users don't have access.
Then again, if you have a three-tier application, you can authenticate
users in the middle layer, and then connect to the database with a
proxy user which has been granted the required rights.
Of these three, I am certainly in favour of stored procedures.
--
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
.
- Follow-Ups:
- Re: Multiple security contexts
- From: Eric Smith
- Re: Multiple security contexts
- Prev by Date: Re: Windows Authentication - Multiple Groups
- Next by Date: Re: Viewing Role Permissions for Custom Role
- Previous by thread: Perms required to encrypt and decrypt with s key protected by cert
- Next by thread: Re: Multiple security contexts
- Index(es):
Relevant Pages
|
|
