Multiple security contexts

Hi,
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?
Ideally, I would like to be able to specify two TCP ports that the server
should listen on--one would require SSL authentication using a
public/private key, the private part of which would be embedded in the custo
m
application while the other would be a normal SQL port, providing access to
the reporting tool.
Thanks,
--Eric
Eric Smith
Systems Architect
Msc Eng (Elec)Eric Smith (EricSmith@.discussions.microsoft.com) 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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you Erland,
Regards your first suggestion--I considered using stored procedures,
however there is no stopping users from pointing the reporting tool at the
stored procedures and executing them (or in fact just executing them directl
y
using something like Excel). That is, just like the
password-embedded-in-custom-app problem if anyone is sufficiently intent on
writing to the database, they will.
I am looking at a three-tier model so using an application role on the
middle tier sounds like a good idea. This is, or course, not very different
from simply running the middle tier application as a proxy user.
Thank you once again for you suggestions.
--Eric
Eric Smith
Systems Architect
Msc Eng (Elec)
"Erland Sommarskog" wrote:

> Eric Smith (EricSmith@.discussions.microsoft.com) writes:
> 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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Eric,

> I am looking at a three-tier model so using an application role on the
> middle tier sounds like a good idea. This is, or course, not very
> different
> from simply running the middle tier application as a proxy user.
You should read about application roles, because they do have a fundamentatl
difference from proxy users. (So fundamental that I choose not to use
application roles, if at all possible.) An application role is, in SQL 2005
syntax, a database principle. That means that it cannot be granted access
to any other database except through the guest role.
RLF|||Eric Smith (EricSmith@.discussions.microsoft.com) writes:
> Regards your first suggestion--I considered using stored procedures,
> however there is no stopping users from pointing the reporting tool at
> the stored procedures and executing them (or in fact just executing them
> directly using something like Excel). That is, just like the
> password-embedded-in-custom-app problem if anyone is sufficiently intent
> on writing to the database, they will.
They would however be limited to what the stored procedures provide, and
you if write the procedures to validate business rules, what the user is
permitted to do etc it would not really matter if the user would run
the procedures from any other application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx