SimpleMembership with custom database schema in ASP.NET MVC 4

Question

I want to enable the ASP.NET MVC 4's SimpleMembership API to integrate with my own database schema. I have a plain and simple table in my database called Users with these fields:

• Id
• Name
• Password
• Email
• IsDeleted

I have already configured the SimpleMembership API to use my database:

WebSecurity.InitializeDatabaseConnection("MyStuff", "Users", "Id", "Name", autoCreateTables: true);

And I can insert a user too:

WebSecurity.CreateUserAndAccount(model.UserName, model.Password, 
                                 new 
                                 { 
                                        IsDeleted = false, 
                                        Email = "[email protected]"                
                                 });

However, the Password field (or it's hash) is not inserted into the Users table (of course), it inserted into another table called webpages_Membership which is created with the InitializeDatabaseConnection call and contains a lot of unnecessary information which I don't need.

Also, I have other automatically created tables called webpages_OAuthMembership, webpages_Roles and webpages_UsersInRoles which I don't need.

I've already tried to set the table generation to false:

WebSecurity.InitializeDatabaseConnection("MyStuff", "Users", "Id", "Name", autoCreateTables: false);

But in this case the CreateUserAndAccount call will throw an exception because it will not find the webpages_Membership table.

It looks like these tables needed when I want to use the SimpleMembership API.

My question is that: what should I do in a scenario like this when I want only a simple Users table and nothing more?

Do I have to write the whole membership handling and the authentication logic (hash code generation, etc.) myself?

Answer 1

I asked the same question to the product team.

The design goal of SIMPLE membership was to work out-of-the box as simple as possible.

So really there's no customization possible as far as the tables are concerned. The recommended workaround is, to still use ASP.NET Membership (SqlMembershipProvider).

Answer 2

Customization is possible.

You can get the source code for SimpleMembershipProvider from the aspnetwebstack project on CodePlex, since this is where the mapping occurs between the DB Schema and the runtime environment you can modify this code to modify/replace schema, statements, etc to match your needs (without much headache, IMO.)

Answer 3

1 - You need to enable migrations, prefereably with EntityFramework 5. Use Enable-Migrations in the NuGet package manager.

2 - Move your

WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "EmailAddress", autoCreateTables: true); 

to your Seed method in your YourMvcApp/Migrations/Configuration.cs class

    protected override void Seed(UsersContext context)
    {
        WebSecurity.InitializeDatabaseConnection(
            "DefaultConnection",
            "UserProfile",
            "UserId",
            "UserName", autoCreateTables: true);

        if (!Roles.RoleExists("Administrator"))
            Roles.CreateRole("Administrator");

        if (!WebSecurity.UserExists("lelong37"))
            WebSecurity.CreateUserAndAccount(
                "lelong37",
                "password",
                new {Mobile = "+19725000000", IsSmsVerified = false});

        if (!Roles.GetRolesForUser("lelong37").Contains("Administrator"))
            Roles.AddUsersToRoles(new[] {"lelong37"}, new[] {"Administrator"});
    }

Now EF5 will be in charge of creating your UserProfile table, after doing so you will call the WebSecurity.InitializeDatabaseConnection to only register SimpleMembershipProvider with the already created UserProfile table, also tellling SimpleMembershipProvider which column is the UserId and UserName. I am also showing an example of how you can add Users, Roles and associating the two in your Seed method with custom UserProfile properties/fields e.g. a user's Mobile (number).

3 - Now when you run update-database from Package Manager Console, EF5 will provision your table with all your custom properties

For additional references please refer to this article with sourcecode: http://blog.longle.io/2012/09/25/seeding-users-and-roles-with-mvc4-simplemembershipprovider-simpleroleprovider-ef5-codefirst-and-custom-user-properties/

Answer 4

You can use OAUth with ASP.NET Universal Providers. universal providers are the new version of sqlmembership providers. these are based on EF CodeFirst and also generate a more cleaner schema of your database look at my following post for more details http://blogs.msdn.com/b/pranav_rastogi/archive/2012/09/12/integrate-openauth-openid-with-your-existing-asp-net-application-using-universal-providers.aspx

Answer 5

This may not work for your use-case but of course you can very easily add your user attributes to the Simplemembership UserProfile Table instead of creating another User Table

I would recommend using the existing membership table to keep hashed password, and just ignore the fields that you don't need. That way maintains compatibility with SimpleMembership without much fuss.

SQLMembership is very easy out of the box. I've done extensive customized extensions to SQLMembership on a couple of projects, which wasn't that difficult - but looking back, I wish I hadn't. It is kind of a maintenance hassle,