What security model do you recommend ?

[Peter]

Given..
We are developing web apps using SQLServer and Oracle databases
We want to take advantage of connection pooling
We are currently using ASP but will be moving to ASP.NET

Should we use a single data access account ? Where should this be stored

This approach, however, would allow an unscrupulous developer to determine the passwor
of the account. S/He could include a page that displays the properties of a connection

Currently, we have an app/SQLServer tables/com component that activates roles for users based on
their role membership (in our custom security tables)

The problem with this approach is that the connections cannot be pooled because onc
a role has been activated on a connection, it cannot be used for anything else an
will throw an error if another role attempts to be activated. Therefore we disable
connection pooling

Any advice would be appreciated

Thanks

....Peter

 

[avnrao]

to leverage on connection pooling, single identity should be used to connect
to data source. general pattern is to create one windows user and give DB
permissions to that account and use the same.
In your case, you have roles created for all users right? are they used for
partitioning the data in databases. what i am saying is, are these roles
created only for data access and segregation? if so you can go ahead using
one single identity.

Av.