ASP/DB Security Model Advice

[Chris Hohmann]

ENVIRONMENT:
Windows 2000 Professional (sp3)
Internet Information Server (5.0)
Active Server Pages (3.0)
VBScript (5.6)
Microsoft SQL Server (08.00.0760)
Microsoft Data Access Components (2.80)
Microsoft OLE DB Provider for SQL Server (08.50.1022)
OLE DB (02.70)

QUESTION:
I've been doing some thinking about ASP/Database security and would
appreciate feedback on the pros and cons of various models. The three
(3) models I've been considering are:

1. Role Your Own: Create various user defined security tables and
restrict database access to stored procedure calls that validate against
data stored said tables. The model would look something like this:

User(1)---(+)Member(+)---(1)Group(1)---(+)Grant(+)---(1)Right

2. SQL Server Security: Use standard logins (vs. Windows logins) and
manage the security model via system stored procedures (sp_addlogin,
sp_adduser, etc...)

3. Windows Security: Use windows logins and similar to option two (2),
manage the security model via system stored procedures.

I'd also be interested in the successes\failures people have had with
other models.

-Chris Hohmann

[Bob Barrows]

Chris Hohmann wrote:

> ENVIRONMENT:
> Windows 2000 Professional (sp3)
> Internet Information Server (5.0)
> Active Server Pages (3.0)
> VBScript (5.6)
> Microsoft SQL Server (08.00.0760)
> Microsoft Data Access Components (2.80)
> Microsoft OLE DB Provider for SQL Server (08.50.1022)
> OLE DB (02.70)
>
> QUESTION:
> I've been doing some thinking about ASP/Database security and would
> appreciate feedback on the pros and cons of various models. The three
> (3) models I've been considering are:
>
> 1. Role Your Own: Create various user defined security tables and
> restrict database access to stored procedure calls that validate
> against data stored said tables. The model would look something like
> this:
>
> User(1)---(+)Member(+)---(1)Group(1)---(+)Grant(+)---(1)Right
>
> 2. SQL Server Security: Use standard logins (vs. Windows logins) and
> manage the security model via system stored procedures (sp_addlogin,
> sp_adduser, etc...)
>
> 3. Windows Security: Use windows logins and similar to option two (2),
> manage the security model via system stored procedures.
>
> I'd also be interested in the successes\failures people have had with
> other models.
>
> -Chris Hohmann

I use a single sql login for all database activity. I combine that with
user-defined security tables which I use to determine which pages (and which
functionality in those pages) should be displayed for each user.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.