Trusted connection question

[Peter]

I have a question on ASP.NET security

Currently, our ASP apps use a custom COM object to determine
user's application role, connect to the database using a fixed account, an
activate the role. All of the app info, role passwords, role memberships, et
are stored in a SQLServer database which are accessible using a custom ASP
application. This COM object runs on all of our web servers. This allows us to
use connection pooling. It works great but takes a little work to get it configured and running
We are in the process of modifying the component/app to support legacy Oracl
databases too

This is similiar to what is recommended in a Microsoft white paper "Building Secur
ASP.NET Applications"

>>>>>>>>>>>>>> Excerpt from white paper

Using Multiple Database Role
If your application has multiple categories of users, and the users within eac
category require the same permissions within the database, your application re-quire
multiple roles
Each role requires a different set of permissions within the database. For example
members of an Internet User role may require read-only permissions to the majorit
of tables within a database, while members of an Administrator or Operator rol
may require read/write permissions

Option
To accommodate these scenarios, you have two main options for role-based authori-zatio
within SQL Server

[Ken Schaefer]

I think you're talking about a couple of different things.

SQL Server Application Roles are invoked from code (similar to your current
COM object connecting as a set instance), IIRC using sp_set_approle (check
in SQL Server Books Online). It would be possible for your code to determine
which SQL Server Role(s) to invoke for a particular user based on some kind
of lookup table that your application could maintain (or you could store
this in the directory if you wanted, or where-ever). However, it does
require a fairly constant set of roles. And, if there's an Oracle
equivalent, you'd need to write appropriate code to invoke it.

Another option might be the Authorization Block from Microsoft (which can
utilise the Authorization Manager that's part of Windows 2003 Server)...

Cheers
Ken


"Peter" <anonymous@discussions.microsoft.com> wrote in message
news:4561164C-2497-4F55-ACF8-1572F0B0A51E@microsoft.com...
: I have a question on ASP.NET security.
:
: Currently, our ASP apps use a custom COM object to determine a
: user's application role, connect to the database using a fixed account,
and
: activate the role. All of the app info, role passwords, role memberships,
etc
: are stored in a SQLServer database which are accessible using a custom ASP
: application. This COM object runs on all of our web servers. This allows
us to
: use connection pooling. It works great but takes a little work to get it
configured and running.
: We are in the process of modifying the component/app to support legacy
Oracle
: databases too.
:
: This is similiar to what is recommended in a Microsoft white paper
"Building Secure
: ASP.NET Applications".
:
: >>>>>>>>>>>>>> Excerpt from white paper
: Using Multiple Database Roles
: If your application has multiple categories of users, and the users within
each
: category require the same permissions within the database, your
application re-quires
: multiple roles.
: Each role requires a different set of permissions within the database. For
example,
: members of an Internet User role may require read-only permissions to the
majority
: of tables within a database, while members of an Administrator or Operator
role
: may require read/write permissions.
:
: Options
: To accommodate these scenarios, you have two main options for role-based
authori-zation
: within SQL Server:
: .
: .
: .
: SQL Application Roles. These are similar to user-defined database roles in
that
: they are used when you assign permissions to database objects. However,
unlike
: user-defined database roles, they do not contain members and are activated
from
: individual applications by using a built-in stored procedure.
: When you use application roles, you check at the gate, map users to roles,
con-nect
: to the database using a single, trusted, service identity, and activate
the
: appropriate SQL application role.
: >>>>>>>>>>>>>>>>>>>>>>>
:
:
: Our user's are wondering if, the combination of Active Directory and
ASP.NET
: would allow us to retire the current security component/application in
favour of an all
: Microsoft offering. Would it provide the flexibility to allow end users
to add/remove
: people from an app, like our custom app does ? Would it work with Oracle
too ?
:
: Thanks,
:
: ...Peter
: