How to SELECT records based upon ASP.NET Roles

microsoft.public.dotnet.security

13 Aug 2006 7:02 PM

Bob

I am new to ASP.NET and need to develop an application that archives /
retrieves documents. When the document is stored, the user specifies the
document permissions ( public or limited to a specific role/class of users
..) This means that when a person in Customer Service does a search, they
aren't able to retrieve company payroll reports archived by the Finance
department.

The solution will be implemented in a site setup with NT Authentication via
a local domain that the IIS 6.0 server is a member of. The data store will
be SQL Server Express 2005 initially.

I understand that ASP.NET supports Role based security. Presently however,
it appears that ASP.NET runs under a single NT Service account.

If I attempt to use the SYSTEM_USER() or similar SQL function, will it
return the current ASP.NET authenticated browser user or the security info
for the .NET service account?

Assuming I store a role / group id with each record, is there a way to
construct a WHERE clause in a SELECT statement that identifies the Roles of
the current ASP.NET authenticated user without having to dynamically build
the SQL statement?

Thanks,
Bob

18 Aug 2006 2:32 PM

Joe Kaplan (MVP - ADSI)

Hi,

No one has answered your question yet, so I thought I'd give it a whack.

For this type of application, what I would tend to do is create some sort of
abstraction layer between the roles that the application supports from a
business perspective and the actual NT groups of the authenticated user.
I'd rather store application-specific stuff in SQL than store the names of
security principals directly. There is a nice framework called AzMan that
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

:) ).

A couple of things worth knowing:
When the user is authenticated by ASP.NET, the authenticated user's security
context is represented in the HttpContext.User property. When using Windows
authentication in IIS, this will be a WindowsPrincipal object, but it would
be something else if you were using forms authentication or membership or
something.

Whether or not the code is actually executing as this user depends on
whether you have impersonation enabled (and whether you are using Windows
auth in IIS again). It is OFF by default in ASP.NET. This is opposite of
how classic ASP works, which always impersonates and doesn't give you an
option to turn it off.

Thus, when your code calls into SQL, the Windows security context will be
the process account, not the authenticated user's account.

If you want to do the Windows security integration at the database level,
you would want to impersonate the authenticated Windows user, either by
enabling impersonation in web.config (<identity impersonate="true"/> inside
the <system.web> element). You can also impersonate programmatically just
when you are running your SQL queries.

Other things to know are that you can cast the IPrincipal to a
WindowsPrincipal and the IIdentity in the Identity property to a
WindowsIdentity class in order to access the Groups property. This allows
you to get the user's list of groups. You can also simply check to see if a
user is in a group with the IPrincipal.IsInRole method.

I hope this gives you some ideas.

Joe K.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
--

Show quoteHide quote

"Bob" <_NO_SPAM_PLEASE_@gmail.com> wrote in message
news:9vadneZPbP-06kLZnZ2dnUVZ_sGdnZ2d@comcast.com...
>I am new to ASP.NET and need to develop an application that archives /
>retrieves documents. When the document is stored, the user specifies the
>document permissions ( public or limited to a specific role/class of users
>.) This means that when a person in Customer Service does a search, they
>aren't able to retrieve company payroll reports archived by the Finance
>department.
>
> The solution will be implemented in a site setup with NT Authentication
> via a local domain that the IIS 6.0 server is a member of. The data store
> will be SQL Server Express 2005 initially.
>
> I understand that ASP.NET supports Role based security. Presently however,
> it appears that ASP.NET runs under a single NT Service account.
>
> If I attempt to use the SYSTEM_USER() or similar SQL function, will it
> return the current ASP.NET authenticated browser user or the security info
> for the .NET service account?
>
> Assuming I store a role / group id with each record, is there a way to
> construct a WHERE clause in a SELECT statement that identifies the Roles
> of the current ASP.NET authenticated user without having to dynamically
> build the SQL statement?
>
> Thanks,
> Bob
>
>
>

18 Aug 2006 5:14 PM

Eric Chaves

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.

21 Aug 2006 5:53 PM

Bob

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

Show quoteHide quote

"Eric Chaves" <eric.dot.cha***@agsbrasil.dot.com.dot.br> wrote in message
news:e$iV0ouwGHA.5024@TK2MSFTNGP04.phx.gbl...
> 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.
>

21 Aug 2006 5:54 PM

Bob

Joe:

Great reply! Thanks for your response. I appreciate the directions to
follow.

Thanks,
Bob

Show quoteHide quote

"Joe Kaplan (MVP - ADSI)" <joseph.e.kap***@removethis.accenture.com> wrote
in message news:ugzxiMtwGHA.560@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> No one has answered your question yet, so I thought I'd give it a whack.
>
> For this type of application, what I would tend to do is create some sort
> of abstraction layer between the roles that the application supports from
> a business perspective and the actual NT groups of the authenticated user.
> I'd rather store application-specific stuff in SQL than store the names of
> security principals directly. There is a nice framework called AzMan that
> 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 :) ).
>
> A couple of things worth knowing:
> When the user is authenticated by ASP.NET, the authenticated user's
> security context is represented in the HttpContext.User property. When
> using Windows authentication in IIS, this will be a WindowsPrincipal
> object, but it would be something else if you were using forms
> authentication or membership or something.
>
> Whether or not the code is actually executing as this user depends on
> whether you have impersonation enabled (and whether you are using Windows
> auth in IIS again). It is OFF by default in ASP.NET. This is opposite of
> how classic ASP works, which always impersonates and doesn't give you an
> option to turn it off.
>
> Thus, when your code calls into SQL, the Windows security context will be
> the process account, not the authenticated user's account.
>
> If you want to do the Windows security integration at the database level,
> you would want to impersonate the authenticated Windows user, either by
> enabling impersonation in web.config (<identity impersonate="true"/>
> inside the <system.web> element). You can also impersonate
> programmatically just when you are running your SQL queries.
>
> Other things to know are that you can cast the IPrincipal to a
> WindowsPrincipal and the IIdentity in the Identity property to a
> WindowsIdentity class in order to access the Groups property. This allows
> you to get the user's list of groups. You can also simply check to see if
> a user is in a group with the IPrincipal.IsInRole method.
>
> I hope this gives you some ideas.
>
> Joe K.
>
> --
> Joe Kaplan-MS MVP Directory Services Programming
> Co-author of "The .NET Developer's Guide to Directory Services
> Programming"
> http://www.directoryprogramming.net
> --
> "Bob" <_NO_SPAM_PLEASE_@gmail.com> wrote in message
> news:9vadneZPbP-06kLZnZ2dnUVZ_sGdnZ2d@comcast.com...
>>I am new to ASP.NET and need to develop an application that archives /
>>retrieves documents. When the document is stored, the user specifies the
>>document permissions ( public or limited to a specific role/class of users
>>.) This means that when a person in Customer Service does a search, they
>>aren't able to retrieve company payroll reports archived by the Finance
>>department.
>>
>> The solution will be implemented in a site setup with NT Authentication
>> via a local domain that the IIS 6.0 server is a member of. The data store
>> will be SQL Server Express 2005 initially.
>>
>> I understand that ASP.NET supports Role based security. Presently
>> however, it appears that ASP.NET runs under a single NT Service account.
>>
>> If I attempt to use the SYSTEM_USER() or similar SQL function, will it
>> return the current ASP.NET authenticated browser user or the security
>> info for the .NET service account?
>>
>> Assuming I store a role / group id with each record, is there a way to
>> construct a WHERE clause in a SELECT statement that identifies the Roles
>> of the current ASP.NET authenticated user without having to dynamically
>> build the SQL statement?
>>
>> Thanks,
>> Bob
>>
>>
>>
>
>