ASP.net roles and Projects

Question

EDIT - Rewrote my original question to give a bit more information

---

Background info
At my work I'm working on a ASP.Net web application for our customers. In our implementation we use technologies like Forms authentication with MembershipProviders and RoleProviders. All went well until I ran into some difficulties with configuring the roles, because the roles aren't system-wide, but related to the customer accounts and projects.

I can't name our exact setup/formula, because I think our company wouldn't approve that...

What's a customer / project?
Our company provides management information for our customers on a yearly (or other interval) basis.
In our systems a customer/contract consists of:

• one Account: information about the Company
• per Account, one or more Products: the bundle of management information we'll provide
• per Product, one or more Measurements: a period of time, in which we gather and report the data

Extranet site setup
Eventually we want all customers to be able to access their management information with our online system. The extranet consists of two sites:

• Company site: provides an overview of Account information and the Products
• Measurement site: after selecting a Measurement, detailed information on that period of time

The measurement site is the most interesting part of the extranet. We will create submodules for new overviews, reports, managing and maintaining resources that are important for the research.

Our Visual Studio solution consists of a number of projects. One web application named Portal for the basis. The sites and modules are virtual directories within that application (makes it easier to share MasterPages among things).

What kind of roles?
The following users (read: roles) will be using the system:

• Admins: development users :) (not customer related, full access)
• Employees: employees of our company (not customer related, full access)
• Customer SuperUser: top level managers (full access to their account/measurement)
• Customer ContactPerson: primary contact (full access to their measurement(s))
• Customer Manager: a department manager (limited access, specific data of a measurement)

What about ASP.Net users?
The system will have many ASP.Net users, let's focus on the customer users:

• Users are not shared between Accounts
• SuperUser X automatically has access to all (and new) measurements
• User Y could be Primary contact for Measurement 1, but have no role for Measurement 2
• User Y could be Primary contact for Measurement 1, but have a Manager role for Measurement 2
• The department managers are many individual users (per Measurement), if Manager Z had a login for Measurement 1, we would like to use that login again if he participates in Measurement 2.

URL structure
These are typical urls in our application:

• http://host/login - the login screen
• http://host/project - the account/product overview screen (measurement selection)
• http://host/project/1000 - measurement (id:1000) details
• http://host/project/1000/planning - planning overview (for primary contact/superuser)
• http://host/project/1000/reports - report downloads (manager department X can only access report X)

We will also create a document url, where you can request a specific document by it's GUID. The system will have to check if the user has rights to the document. The document is related to a Measurement, the User or specific roles have specific rights to the document.

What's the problem? (finally ;))
Roles aren't enough to determine what a user is allowed to see/access/download a specific item. It's not enough to say that a certain navigation item is accessible to Managers. When the user requests Measurement 1000, we have to check that the user not only has a Manager role, but a Manager role for Measurement 1000.

Summarized:

1. How can we limit users to their accounts/measurements?
   (remember superusers see all measurements, some managers only specific measurements)

2. How can we apply roles at a product/measurement level? (user X could be primarycontact for measurement 1, but just a manager for measurement 2)

3. How can we limit manager access to the reports screen and only to their department's reports?

All with the magic of asp.net classes, perhaps with a custom roleprovider implementation.

Similar Stackoverflow question/problem
http://stackoverflow.com/questions/1367483/asp-net-how-to-manage-users-with-different-types-of-roles

Answer 1

Hey,

Store a value in the profile potentially. Setup a profile entry in the config file and use that to store the value.

More realistically, you may want to store this outside of the ASP.NET tables for ease of use and for ease of accessing the value (maybe outside of the web environment if you need to)...

Not sure what all your requirements are.

Answer 2

What you are seeking from the various posts that I see, is a custom role mechanism or said another way, a custom Authorization mechanism. Authentication can still use the standard SqlMembershipProvider.

I'm not sure that the standard role provider will provide you with what you want as authorization requires that you have the context of the Project. However, you might investigate writing a custom RoleProvider to see if you can create some custom methods that would do that. Still, for the purposes of answering the question, I'm going to assume you cannot use the SqlRoleProvider.

So, here's some potential schema:

Create Table Companies
(
    Id int not null Primary Key
    , ...
)
Create Table Projects
(
    Id int not null Primary Key
    , PrimaryContactUserId uniqueidentifier
    , ...
    , Constraint FK_Projects_aspnet_Users
        Foreign Key ( PrimaryContactUserId )
        References dbo.aspnet_Users ( UserId )
)
Create Table Roles
(
    Name nvarchar(100) not null Primary Key
    , ...
)

Create Table ProjectCompanyRoles
(
    CompanyId int not null
    , ProjectId int not null
    , RoleName nvarchar(100) not null
    , Constraint FK_...
)

As I said before, the reason for including PrimaryContact in the Projects table is to ensure that there is only one for a given project. If you include it as a role, you would have to include a bunch of hoop jumping code to ensure that a project is not assigned more than one PrimaryContact. If that were the case, then take out the PrimaryContactUserId from the Projects table and make it a role.

Authorization checks would entail queries against the ProjectCompanyRoles. Again, the addition of the contexts of Project and Company make using the default role providers problematic. If you wanted to use the .NET mechanism for roles as well as authentication, then you will have to implement your own custom RoleProvider.

Answer 3

This is exactly the kind of scenario that calls for a custom RoleProvider. You design the database schema to support your case (you might want to create a table called ProjectRole and a table called CompanyRole).

Here are a couple of things to get you started (with links to help at the bottom):

Add this section to your web.config:

<roleManager defaultProvider="MyRoleProvider" enabled="true">
    <providers>
        <add name="MyRoleProvider" type="MyNamespace.MyRoleProvider, MyAssembly, Version=1.0.0.0" description="My Custom Role Provider." enableSearchMethods="false" applicationName="MyApplicationName"/>
    </providers>
</roleManager>

Then this is what the MyRoleProvider class looks like (more or less):

(NOTE: your class must inherit from System.Web.Security.RoleProvider)

namespace MyNamespace
{
    ...

    public class MyRoleProvider : System.Web.Security.RoleProvider
    {
        private string _applicationName;

        public MyRoleProvider()
        {
        }

        public override string ApplicationName
        {
            get
            {
                return _applicationName;
            }
            set
            {
                _applicationName = value;
            }
        }

        ...

    }
}

Then you just need to override some methods to provide your application with the information it needs:

At a minimum, I would override these 2 methods:

• GetRolesForUser
• IsUserInRole

But you can also override these methods if you want to:

• AddUsersToRoles
• RemoveUsersFromRoles
• FindUsersInRole
• GetUsersInRole
• GetAllRoles
• CreateRole
• DeleteRole
• RoleExists

Nor here are the links I promised:

• Implementing a Role Provider
• Create Custom RoleProvider for ASP.NET Role Permissions and Security
• Sample Role-Provider Implementation

Answer 4

DISCLAIMER: Pursuant to the exchange in comments, in which I make a complete asshat of myself, an almost out of the box solution has been arrived at and this answer has been purged of all asshattery and now contains only a tested scenario that may or may not address the OP problem. ;-)

Kudos to Thomas for keeping his cool and not giving up.

---

Z- tell me if I understand you:

You want a central membership provider for all apps/projects and a distinct role silo for each app/project?

You may not need to implement custom providers. The standard stack may suffice with a minor stored procedure modification. It is always best to try and sweet talk the baked-in systems to do what you want. It leads to less work and more sleep.

The salient facets of the proposed solution:

• A common database and connection string,
• A common membership application name,
• A common machineKey section so that each site will use the common forms ticket.
• A UNIQUE role provider application name (or projectId, as you say).
• A modified aspnet_Users_DeleteUser sproc.

The modification to aspnet_Users_DeleteUser involves cleaning up the user references in aspnet_users that are dynamically created by the Roles and Profile providers and carries a condition that a particular aspnet_db instance is owned by the common MembershipProvider, and only the sites that use that common Membership provider should connect to it.

To map this solution to the OP scenario:

Each Account/Company would have a distinct aspnet_db instance and the 'ProjectId' would be mapped to the applicationName attribute of the RoleManager provider element.

As projects are 'migrated' they are assigned a new ProjectId (applicationName) and in doing so, the companies users can authenticate against the migrated project by virtue of the common membership provider but the roles from the original project do not carry over by virtue of distinct role providers.

All standard membership management strategies, e.g. AspNet configuration tool, Login controls, createuser wizards, Membership functions (especially Membership.DeleteUser() - thank you Thomas) will behave as expected with no modifications.

Profiles may be implemented in either direction, using the applicationId of the Membership provider will allow profile data to follow a user to any of the associated projects. Using the distinct ProjectId (applicationName) of the Role provider will allow seperate profiles for each user in each project.

Some more detail and the tests are here.

The salient configuration sections are listed below and the modified sproc follows.

Web.config

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="testDb" providerName="System.Data.SqlClient" connectionString="Data Source=(local);Initial Catalog=__SingleAuthMultiRole;Integrated Security=True"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true"/>

    <!-- this key is common all your apps - generate a new one @ http://www.developmentnow.com/articles/machinekey_generator.aspx -->
    <machineKey validationKey="841FEF8E55CD7963CE9EAFED329724667D62F4412F635815DFDDBE7D2D8D15819AE0FDF70CEF8F72792DBD7BF661F163B01134092CBCB80D7D71EAA42DFBF0A9" decryptionKey="FC9B0626224B0CF0DA68C558577F3E37723BB09AACE795498C4069A490690669" validation="SHA1" decryption="AES"/>

    <authorization>
      <deny users="?"/>
    </authorization>

    <authentication mode="Forms" />

    <membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15">
      <providers>
        <clear/>
        <add name="SqlProvider"
             type="System.Web.Security.SqlMembershipProvider"
             connectionStringName="testDb"
             applicationName="Common"  /> <!-- membership applicationName is common to all projects  -->
      </providers>
    </membership>

    <roleManager enabled="true" defaultProvider="SqlRoleManager" cacheRolesInCookie="true">
      <providers>
        <add name="SqlRoleManager"
             type="System.Web.Security.SqlRoleProvider"
             connectionStringName="testDb"
             applicationName="WebApplication1"/> <!-- roleManager applicationName is unique to each projects  -->
      </providers>
    </roleManager>

  </system.web>
</configuration>

Usage: After provisioning your Aspnet_db with aspnet_regsql.exe, run this script to modify the aspnet_Users_DeleteUser sproc.

/*************************************************************/
/*************************************************************/
--- Modified DeleteUser SP

IF (EXISTS (SELECT name
              FROM sysobjects
             WHERE (name = N'aspnet_Users_DeleteUser')
               AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Users_DeleteUser
GO
CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @TablesToDeleteFrom int,
    @NumTablesDeletedFrom int OUTPUT    

AS
BEGIN
    -- holds all user id for username
    DECLARE @UserIds TABLE(UserId UNIQUEIDENTIFIER)
    SELECT  @NumTablesDeletedFrom = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
    SET @TranStarted = 0

    DECLARE @ErrorCode   int
    DECLARE @RowCount    int

    SET @ErrorCode = 0
    SET @RowCount  = 0

    -- get all userid for username
    INSERT INTO @UserIds
    SELECT  UserId
    FROM    dbo.aspnet_Users 
    WHERE   LoweredUserName = LOWER(@UserName)

DECLARE @tmp int
SELECT @tmp = COUNT(*) FROM @UserIds
    IF NOT EXISTS(SELECT * FROM @UserIds)
        GOTO Cleanup

    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        DELETE FROM dbo.aspnet_Membership WHERE UserId IN (SELECT UserId from @UserIds)

        SELECT @ErrorCode = @@ERROR,
               @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
    IF ((@TablesToDeleteFrom & 2) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_UsersInRoles WHERE UserId IN (SELECT UserId from @UserIds)

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
    IF ((@TablesToDeleteFrom & 4) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_Profile WHERE UserId IN (SELECT UserId from @UserIds)

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
    IF ((@TablesToDeleteFrom & 8) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE UserId IN (SELECT UserId from @UserIds)

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (@TablesToDeleteFrom & 2) <> 0 AND
        (@TablesToDeleteFrom & 4) <> 0 AND
        (@TablesToDeleteFrom & 8) <> 0 AND
        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE UserId IN (SELECT UserId from @UserIds))))
    BEGIN
        DELETE FROM dbo.aspnet_Users WHERE UserId IN (SELECT UserId from @UserIds)

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:
    SET @NumTablesDeletedFrom = 0

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO