Re: How to SELECT records based upon ASP.NET Roles

Eric:

Thanks for the reply. You hit on some very good points. Because of the
connection pooling issue, I was hoping to leave impersonation off and do
permissions based upon group membership. Since numerous users will be
members of like groups, connection pooling will work better than if a large
number of individualized views were accessed.

My biggest issue remaining issue is how to determine if a user is a member
of a specific NT Group without requiring the Server Admin to add the group
as a SQL Login (thus ruling out the use of IS_MEMBER().)

It may be that is the best solution however.

Thanks,
Bob Evans

"Eric Chaves" <eric.dot.chaves@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:e$iV0ouwGHA.5024@xxxxxxxxxxxxxxxxxxxxxxx
Ji Bob and Joe,

helps with this type of thing and is very flexible. However, you might
also be able to use SQL row-level security as well (which is something
I've never done and know nothing about except that I've heard such as
thing exists :) ).

As far as I know, MS-SQL up to SQL 2000 doesn't support row-level
security.
Row level permissions is kind a complicated topic to be implemented
without some DB server support:
- You can filter the data on BO or DAO objects with dinamic queries (ie:
queries that your application build and execute trough ADO Commands, for
examploe), but this doesn't perform well; is prone to SQL injection, is
hard to build complex joins (on the fly); The positive side is that you
can easily customize the roles, you doesn't requires DB logins (and thus
you keep all benifts of Connection pooling);
- You can filter the data on BO or DAO restrict all access by
stored_procedures; This prevents SQL Injections, has a better performance
since the queries are pre-compiled, but is hard to extend as application
changes;
- You can filter the data on DB Server, restricting all access to views
(deny access on table objects and allow insert updates on SPs or views
only). Since you can't pass parameters to views, you'll restrict the
access (the where statement) based on SQL login name; This breaks
connection pooling (you'll need at least on connection per role) but is
the most effective and with better performance aproach.

Anyway this is a design that needs to take into account what kind of
database operations will be made, since they'll drive the best aproach and
each one has a great impact on the maintence/performance jobs.

Cheers,

Eric.



.

Relevant Pages

  • Re: connection options to yukon
    ... Connection pooling depends on the fact that repeated SqlConnection objects ... My recommendation would be to use Windows authentication over sql Auth ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VB with SQL Server... Unable to create temp table using ADO connection object
    ... > I am not using connection pooling. ... SQL Server MVP ... but when I query the temp table either in VB or in SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: Encryption of application configuration block
    ... access to Sql Server objects based on the current users' Windows account. ... This would potentially help (although I still don't like potential hackers ... connection pooling kicks in for the whole domain. ...
    (microsoft.public.dotnet.general)
  • Application Role
    ... that is accessing a SQL DB. ... I dont want to set the APplication ... but I would like to somehow implement ... I know that Application Roles dont use Connection Pooling, ...
    (microsoft.public.sqlserver.security)
  • Re: Field Level Security
    ... I agree with Dan that the recommended approach is to use views to restrict ... you can assign permissions to specific columns using ... Enterprise Manager (in SQL Server 2000) or the GRANT/DENY statements (SQL ... individual columns instead of caring only about high level objects such as ...
    (microsoft.public.sqlserver.security)