Re: Database security design with ASP.net and form-based authentication
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 10 Mar 2006 07:35:04 -0600
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@xxxxxxxxxxxxxxxx> wrote in message
news:e8WPEH5QGHA.2300@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:eoNTQdyQGHA.5552@xxxxxxxxxxxxxxxxxxxxxxx
Since you already have forms-based security, why not use a single SQLlogin
for all database access?assigned
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@xxxxxxxxxxxxxxxx> wrote in message
news:OUiKBQwQGHA.5500@xxxxxxxxxxxxxxxxxxxxxxx
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
havespecific 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
usedto 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
now,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
passwords.but then I'm not sure how to manage the multiple usernames and
group,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
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.
.
- References:
- Prev by Date: Re: User Permissions disappearing
- Next by Date: Re: Should my host be showing me all his databases?
- Previous by thread: Re: Database security design with ASP.net and form-based authentication
- Next by thread: Should my host be showing me all his databases?
- Index(es):
Relevant Pages
|
