Salient Solutions

Multiple ASP.Net Apps with common SqlMembershipProvider and unique SqlRoleProvider/SqlProfileProvider

After a false start, a running battle in coments and a hot steaming serving of crow, all in response to a question on StackOverflow regarding a scenario in which I think the OP could use a single SqlMembershipProvider with multiple SqlRoleProviders and SqlProfileProviders, I believe I have a solution that is not a 'hack' or 'subverts' the intended usage of the SqlProvider stack.

Lets begin with a bit of shadetree philosphy:

If you can re-use something that has had millions of dollars and thousands of man hours devoted to design, coding, documentation and test, you should do so.

Implementing skeleton custom providers  can be accomplished by implementing just a few methods but then there are the schema changes to contend with.

If you can get the whole stack for free with a bit of creative thinking, maybe that is the way to go.

Less work, more play.

Ok, so that is my mindset going into this saga.....

My first solutions was simple and worked with the exception of user deletion, which I was unwilling to acknowledge. Thomas insisted this was the weak spot and it was, although not, I think, in the way he supposed it was. But as stubborn as I am, I argued for a few days, nitpicking on some of the things I thought he was misunderstanding and all the while I was missing the big picture.

Here is is in a nutshell:

• 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.

It looked good to me, I built a solution real quick and was off to the races. I was quite suprised when it was shot down and actually called a hack. Who you callin a hack?

Well, another detractor, Gabriel, eventually quit arguing with me but Thomas stuck it out, although we were talking apples and oranges at each other for a couple days.

It wasn't until I after I threw up my hands and pointed him @ Strong Opinions, Weakly Held that I stopped for a minute and realized maybe I needed to re-read it as well.

So, I took a step back, put together some tools and actually ran quite a few tests it became clear that Thomas was right, inadvertantly but right nonetheless, in stating it was not viable out of the box.

The problem lay in that when SqlMembershipProvider deletes a user, it uses it's own appId as half the key, as Thomas correctly stated, and if multiple SqlRoleProviders are in play any roles that have been associated with that common user will be orphaned. Even if you use the RoleProvider to remove the user from all of its roles, the other apps don't know about this and a time bomb has been set.

In all of the tests this is the only problem with the strategy of using stock providers with creative configuration in this scenario lie in one short sproc, aspnet_Users_DeleteUser.

aspnet_Users_DeleteUser normally keys off the membership appId (applicationName) and the userId, which it gets by querying with the supplied username.

If we stipulate that this instance of aspnet_db is owned by the common membership provider and that only applications using the common membership provider shall connect, we can safely modify the aspnet_Users_DeleteUser to key off the username alone.

In looking at the sproc, you can see that a lot of work is being done, deleting membership rows, user rows, optionally cleaning associated data such as roles and profiles and all of these are being keyed by the GUID userId, not the userName that was passed in.

The solution to this problem was short and sweet. Instead of keying all of these deletions on a single GUID userId, at the head of the sproc, using the username, fill a table variable with all of the userId GUIDs that have the passed in username and then replace all of the 'where userid = @userId' with 'where userId in (select userId from @userIds)'.

Bingo, problem solved. All the way.

So the solution now looks like this: 

• 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.
• A modified aspnet_Users_DeleteUser
• A stipulation that each common membership provider owns it's instance of aspnet_db

Now, if Thomas is reading this, I am sure he is pwopping himself (google it) and wanting to reiterate his objections regarding the multiple user rows in aspnet_users as an indication of a defect in this strategy.

To which I say, to be crystal clear: aspnet_Users rows are NOT users. aspnet_Membership rows are users. aspnet_Users rows are more likened to user names. They are not authoritive in any  measurable way.

The RoleProvider and ProfileProvider will, when asked to provide services related to a user, create a row in aspnet_Users upon which to hang thier respective data, if a matching UserName/ApplicationId is not found. Which will be the case in this scenario.

These rows do not indicate that a new user has been created. Only the MembershipProvider can create a user. And all membership related actions are taken against the aspnet_Membership row and aspnet_Users row that the MembershipProvider created.

The 'extra' aspnet_User rows are, again, simply created as needed to enable the functionality of those providers. Only MembershipProvider is concerned with authentication. Role and Profile providers will take any username you want to give them and create roles/profiles for them whether they exists as Membership users or not. This is how anonymous profiles are implemented.

So, as long as these rows are kept pruned when a user deletion is enacted, every facet of the provider stack will work as advertised. Including all stock asp.net membership controls, the asp.net website configuration tool, direct access via provider instances etc etc.

Just a though: do you notice that no membership/profile/role related function, anywhere, takes a userId? The abstract, compartmentalized, Provider-ness of the stack revolves around the fact that all functions accept a UserName.  The bleed through from membership to roles/profiles exhibited in the Sql stack's implementation Membership.DeleteUser is a mixing of concerns that is necessary to make common scenarios 'Just Work'.

It is this single instance of mixing of concerns that we must compensate for in order to provide a viable solution to an interesting scenario with no compiled code to write or maintain or deploy, and just a single sproc replacement.

Download the test solution and see for yourself.

I despise MSTest for anything web related, so I have merged a few open source projects to create a rather interesting test setup.

Hosting is done with CassiniDev. I have merged the CassiniDev test fixture with an on-the-fly database fixture found in Salient.SqlServer and used a mini 'test-runner' call uUnit inside .ashx in the web apps to perform the tests.

Note: the versions of these components contained in this test solution are not 'released' yet, but I have committed them so you can pull the source if you like.

Here is the output of the tests. 

Smoke Test

------ Test started: Assembly: Tests.dll ------

Creating aspnet_db
Starting WebApplication1
@ WebApplication1
Create a common user on app1 and set local role and profile
	created role
	created user:testUser
	user exists
	added user to role
	created user profile
	user has profile
CreateUser : PASSED

Starting WebApplication2
@ WebApplication2
Ensure common user exists but no local role or profile exist
	user exists
EnsureUserExists : PASSED
	user has no profile
EnsureUserHasNoProfile : PASSED
	user has no role
EnsureUserHasNoRole : PASSED
Give the common user an application specific role and profile
	created role
CreateRole : PASSED
	added user to role
AddUserToRole : PASSED
	created user profile
CreateUserProfile : PASSED
	user has profile
EnsureUserHasProfile : PASSED

Starting WebApplication1
@ WebApplication1
Delete common user and ensure local roles and profiles are deleted
	user exists
	user has role
	user has profile
	deleted user:testUser via Membership.DeleteUser(Username);
	user does not exist
	user has no profile
	user has no role
DeleteUser : PASSED

Starting WebApplication2
@ WebApplication2
Common user was deleted in app1. Ensure roles and profile in app2 are deleted as well
	user does not exist
EnsureUserDoesNotExist : PASSED
EnsureUserHasNoRoles : PASSED
	user has no profile
EnsureUserHasNoProfile : PASSED

Dropping aspnet_db - comment out base.TestFixtureTearDown(); in TestFixtureTearDown to retain db for examination

4 passed, 0 failed, 0 skipped, took 34.48 seconds (NUnit 2.5.3).

Modified 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

Update: Thomas is doing his best to drive me to prove that things are not always as complicated as they may seem.

Apparently, I am screwing the pooch by not providing functionality that does not exist in the provider stack (for good reason, in my opinion), and I mouthed off quickly as I am wont to do that a 20 line sproc would take care of the problem.

I was wrong.

It only took 4 lines.

CREATE PROCEDURE aspnet_ChangeUserName(@oldUsername nvarchar(256),	@newUsername nvarchar(256))

	-- your code must verify that the new username meets provider requirement
	-- i.e. length, complexity
AS
	-- ensure new username is unique
	IF EXISTS(SELECT LoweredUserName FROM aspnet_Users WHERE LoweredUserName = LOWER(@newUsername))
		RETURN -1
	-- update aspnet_Users
	UPDATE aspnet_Users SET UserName = @newUsername, LoweredUserName = LOWER(@newUsername) WHERE LoweredUserName = LOWER(@oldUsername)
	RETURN 0

I tried to find the need for exception handling and transactions but there are none.

Of course you would need to ensure the user was logged off when you change the username, but that would be the case in any implementation, whether you decided to rewrite the provider stack or add a 4 line sproc. ;-)

Next?

Print | posted on Friday, March 05, 2010 2:29 AM |