Database security design with ASP.net and form-based authentication

I'm setting up an ASP.Net intranet application with a SQL Server 2000
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>