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?

Solution 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).

Solution 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.)

Solution 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 = "[phone]", 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/

Solution 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

Solution 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,

View more solutions

Comments

[Zsolt]

Ok, thanks! Maybe I will write my custom authentication logic.

[Max]

NO! Please don't do it! :-)

[Max]

Using SqlMembership is sooo easy! Just search for a tutorial on ASP.NET Membership. It's very flexible!

[Zsolt]

Hmm...I searched for "OAuth" and I like this kind of login concept. Can I use the new OAuth support with SqlMembershipProvider?

[Max]

You mean OAuth in ASP.NET MVC 4? As far as I know.. NO! So.. better stick with the tables of simple Membership :) That's what I've decided!

[James Fleming]

There's nothing preventing you from using EF to get additional user data. weblogs.asp.net/jgalloway/archive/2012/08/29/…

[Zsolt]

I think this blogpost is about another topic: seeding data with EF Code Migrations. I know that I can add any property to my User table (e.g. a Mobile string), my question was not about that but about rewriting/customizing the whole schema of the automatically generated SimpleMembership tables.

[Adam Tolley]

I came here to post the same link as James did, which more or less contradicts the idea that the intent of Simple Membership was simplicity out of the box - the original membership provider did a fine job of that. The new provider is designed, at least allegedly, to be simpler to extend.

[Shaun Wilson]

Universal Providers are not compatible with SimpleMembership, and this doesn't address the OP's question.

[Shaun Wilson]

This answer is misleading. It is possible to embrace and extend the existing system. If the out-of-box experience isn't providing a schema you agree with you can easily implement your own SimpleMembershipProvider to create and utilize a schema of your own design.