Multiple security contexts

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric Smith - 28 May 2007 19:28 GMT

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 custom
application while the other would be a normal SQL port, providing access to
the reporting tool.

Thanks,

--Eric

Signature

Eric Smith

Systems Architect
Msc Eng (Elec)

Reply to this Message

Erland Sommarskog - 28 May 2007 23:25 GMT

> I've been going through the SQL Server documentation (2005), looking for a
> way to solve the following problem:
[quoted text clipped - 14 lines]
> 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.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Reply to this Message

Eric Smith - 29 May 2007 11:56 GMT

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

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

Signature

Eric Smith

Systems Architect
Msc Eng (Elec)

> > I've been going through the SQL Server documentation (2005), looking for a
> > way to solve the following problem:
[quoted text clipped - 34 lines]
>
> Of these three, I am certainly in favour of stored procedures.

Reply to this Message

Russell Fields - 29 May 2007 15:42 GMT

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

Reply to this Message

Erland Sommarskog - 30 May 2007 23:23 GMT

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

Signature

Reply to this Message