SimpleMembershipProvider 的 MySql 实现

前几天想想，要不要玩玩 MySql 吧，看看是什么样子的。于是动手把手头上的一个小程序的数据库移植到 MySql 上，正好把开发环境从 VS2010 升级到 VS2012。还有 MVC2 升级到 MVC4 看看是有什么变化没。程序有认证的部分，就先从认证数据部分试开始了。

认 证我一直用的是微软的 Membership 体系，看 MySql 也实现了对应的 Provider，在新建立了一个 MVC4 项目后，把 Provider 一改就直接启动，然后就出错了。异常是“To call this method, the “Membership.Provider”property must be an instance of “ExtendedMembershipProvider”.

其原因是微软引进了一个叫 SimpleMembershipProvider 的东西。这个东西确实在数据库端做了大量的简化，根本没用到任何存储过程，表的数量也减少了很多，并且提供了与其它网站进行登录的功能。在 缺省情况下，WebMatrix.WebData.WebSecurity 利用 PreApplicationStart这个技术，在程序启动时将全局变量 Membership 中的"AspNetSqlMembershipProvider"与 Roles 中的"AspNetSqlRoleProvider"这两个 Provider 分别替换为 SimpleMembershipProvider 与 SimpleRoleProvider，于是在什么也没有改变的表面下，传统的 Membeship 体系就被 SimpleMembership 给代替了。从这个替换的动作就可以发现，微软写的 SimpleMembership 根本就是为自家的 SqlServer 服务的，如果你用 IlSpy 去看 SimpleMembershipProvider 的代码的话，就会看到 SqlServer 的 T-Sql 语法直接写在了代码中，直接使用这个 SimpleMembership 去连接 MySql 的话，是会出错的。

而 如果你直接用 MySql 的 Provider 的话，在 WebSecurity 中又会检查这个 Provider 是不是继承至微软新加入的 ExtendedMembershipProvider，当然，第三方怎么会跟的上微软这么自私的举动，于是上面那个异常就出现了。

在 各种网站的问答栏中，有人推荐把这个放在 Web.config 的中，其实这个只是阻止了上面 WebSecurity 将系统的 maching.config 中配置的缺省 AspNetSql 系列的 Provider 用它的 Simple 系统的 Provider 来代替而已，如果你用的是自定义的 Membership，那么这个 enableSimpleMembership 的设置是无用的。所以解决方法要么是不要使用微软给你的那个套登录验证机制，要么自己写一个 Provider 来实现。

下面来说正经的吧。在 WebSecurity 中如果使用的是SimpleMembershipProvider 那么会自动调用建表语句在数据库中建立要使用的表结构。如果不是 SimpleMembershipProvider 的话，则没有这个动作，所以建表要自己手动完成，或者在自定义的 Provider 中找合适的地方实现。在此，我直接用手动建立了表结构。

Create Table UserProfile (UserId int not null primary key auto_increment, UserName varchar(256) not null);

Create Table webpages_Membership (UserId int not null primary key, CreateDate datetime NULL, ConfirmationToken varchar(128) NULL,
IsConfirmed bit NULL, LastPasswordFailureDate datetime NULL, PasswordFailuresSinceLastSuccess int NOT NULL,
Password varchar(128) NOT NULL, PasswordChangedDate datetime NULL, PasswordSalt varchar(128) NOT NULL,
PasswordVerificationToken varchar(128) NULL, PasswordVerificationTokenExpirationDate datetime NULL);

Create Table webpages_OAuthMembership (Provider varchar(30) not null, ProviderUserId varchar(100) not null, UserId int not null);

Alter Table webpages_OAuthMembership Add Primary Key (Provider, ProviderUserId);

Create Table webpages_OAuthToken (Token varchar(100) not null primary key, Secret varchar(100) not null);

Create Table webpages_Roles (RoleId int not null primary key auto_increment, RoleName varchar(256) not null);

Create Table webpages_UsersInRoles (UserId int not null, RoleId int not null);

Alter Table webpages_UsersInRoles Add Primary Key (UserId, RoleId);

在 SimpleMembershipProvider 中，微软写进了大量的 Sql 代码，个人不喜欢这么做，于是就使用 LinqToEF 来实现数据端的操作。映射 EF 这个事情，你们比我懂，我就不说了。下面直接贴 MembershipProvider 的代码吧。在代码中，我的映射的 EF 叫 MembersAuthorContext。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Transactions;
using System.Web;
using System.Web.Helpers;
using System.Web.Security;
using WebMatrix.WebData;

namespace TestSimpleMembership.Help
{
    public class MySqlSimpleMembershipProvider : ExtendedMembershipProvider
    {
        private static string DEFAULT_PROVIDER_NAME = "MySQLMembershipProvider";
        private static string DEFAULT_NAME = "MySqlExtendedMembershipProvider";
        private static string DEFAULT_PROVIDER_CONFIG_NAME = "provider";

        private MembershipProvider preProvider;
        private MemberAuthorContext dbContext;
        private System.Data.Entity.DbSetuserProfiles;
        private System.Data.Entity.DbSetoAuthMemberships;
        private System.Data.Entity.DbSetroles;
        private System.Data.Entity.DbSetusersInRoles;
        private System.Data.Entity.DbSetmemberships;

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            if (config == null) throw new ArgumentNullException("config");
            if (string.IsNullOrEmpty(name)) name = DEFAULT_NAME;
            base.Initialize(name, config);

            var providerName = config[DEFAULT_PROVIDER_CONFIG_NAME];
            if (!string.IsNullOrEmpty(providerName))
                this.preProvider = Membership.Providers[providerName] ?? Membership.Providers[DEFAULT_PROVIDER_NAME];
            if (this.preProvider != null)
                this.preProvider.ValidatingPassword += delegate(object sender, ValidatePasswordEventArgs args)
                {
                    this.OnValidatingPassword(args);
                };

            this.dbContext = new MemberAuthorContext();
            this.userProfiles = this.dbContext.userprofile;
            this.oAuthMemberships = this.dbContext.webpages_oauthmembership;
            this.roles = this.dbContext.webpages_roles;
            this.usersInRoles = this.dbContext.webpages_usersinroles;
            this.memberships = this.dbContext.webpages_membership;
        }

        public override bool ConfirmAccount(string accountConfirmationToken)
        {
            var rsl = from m in memberships
                      where m.ConfirmationToken == accountConfirmationToken
                      select new { UserId = m.UserId, ConfirmationToken = m.ConfirmationToken };

            if (!rsl.Any()) return false;

            var user = (from m in memberships where m.UserId == rsl.First().UserId select m).FirstOrDefault();
            if (user == null) return false;

            user.IsConfirmed = true;
            dbContext.SaveChanges();
            return true;
        }

        public override bool ConfirmAccount(string userName, string accountConfirmationToken)
        {
            var rsl = from membership in memberships
                      join userProfile in userProfiles on membership.UserId equals userProfile.UserId
                      where membership.ConfirmationToken == accountConfirmationToken &&
                          userProfile.UserName == userName
                      select new { UserId = membership.UserId, ConfirmationToken = membership.ConfirmationToken };

            if (!rsl.Any()) return false;

            var user = (from membership in memberships where membership.UserId == rsl.First().UserId select membership).FirstOrDefault();
            if (user == null) return false;

            user.IsConfirmed = true;
            dbContext.SaveChanges();
            return true;
        }

        public override string CreateAccount(string userName, string password, bool requireConfirmationToken)
        {
            if (String.IsNullOrEmpty(password))
                throw new MembershipCreateUserException(MembershipCreateStatus.InvalidPassword);
            string passwordHash = Crypto.HashPassword(password);
            if (passwordHash.Length > 128)
                throw new MembershipCreateUserException(MembershipCreateStatus.InvalidPassword);

            if (String.IsNullOrEmpty(userName))
                throw new MembershipCreateUserException(MembershipCreateStatus.InvalidUserName);

            var user = (from u in userProfiles where u.UserName == userName select u).FirstOrDefault();
            if (user == null)
                throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
            if ((from m in memberships where m.UserId == user.UserId select m).Any())
                throw new MembershipCreateUserException(MembershipCreateStatus.DuplicateUserName);

            var token = requireConfirmationToken ? GenerateToken() : null;

            memberships.Add(new webpages_membership()
            {
                UserId = user.UserId,
                Password = passwordHash,
                CreateDate = DateTime.UtcNow,
                IsConfirmed = !requireConfirmationToken,
                PasswordFailuresSinceLastSuccess = 0,
                PasswordSalt = string.Empty,
                ConfirmationToken = token,
                PasswordChangedDate = DateTime.UtcNow
            });
            try
            {
                dbContext.SaveChanges();
            }
            catch (Exception e)
            {
                throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
            }
            return token;
        }

        private string GenerateToken()
        {
            using (RNGCryptoServiceProvider provider = new RNGCryptoServiceProvider())
            {
                var bytes = new byte[16];
                provider.GetBytes(bytes);
                return HttpServerUtility.UrlTokenEncode(bytes);
            }
        }

        public override string CreateUserAndAccount(string userName, string password, bool requireConfirmation, IDictionary values)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                if ((from u in userProfiles where u.UserName == userName select u).FirstOrDefault() != null)
                    throw new MembershipCreateUserException(MembershipCreateStatus.DuplicateUserName);

                userprofile user = new userprofile() { UserName = userName };
                userProfiles.Add(user);
                dbContext.SaveChanges();
                var rsl = CreateAccount(userName, password, requireConfirmation);
                ts.Complete();
                return rsl;
            }
        }

        public override bool DeleteAccount(string userName)
        {
            var user = (from u in userProfiles where u.UserName == userName select u).FirstOrDefault();
            if (user == null) return false;

            foreach (var membership in from m in memberships where m.UserId == user.UserId select m)
                memberships.Remove(membership);

            dbContext.SaveChanges();
            return true;
        }

        public override string GeneratePasswordResetToken(string userName, int tokenExpirationInMinutesFromNow)
        {
            if (string.IsNullOrEmpty(userName))
                throw new ArgumentNullException("userName");

            var user = getConfirmedUser(userName);
            if (user == null)
                throw new InvalidOperationException();

            var membership = (from m in memberships
                              where m.UserId == user.UserId && m.PasswordVerificationTokenExpirationDate > DateTime.UtcNow
                              select m).FirstOrDefault();

            if (membership.PasswordVerificationToken == null)
            {
                membership.PasswordVerificationToken = GenerateToken();
                membership.PasswordVerificationTokenExpirationDate = DateTime.UtcNow.AddMinutes(tokenExpirationInMinutesFromNow);
                dbContext.SaveChanges();
            }
            return membership.PasswordVerificationToken;
        }

        private webpages_membership getConfirmedUser(string userName)
        {
            var rsl = (from u in userProfiles
                    join m in memberships on u.UserId equals m.UserId
                    where m.IsConfirmed == true && u.UserName == userName
                    select m).FirstOrDefault();
            return rsl;
        }

        public override ICollectionGetAccountsForUser(string userName)
        {
            var rsl = new List();

            foreach (var oAuth in from o in oAuthMemberships
                                  join u in userProfiles on o.UserId equals u.UserId
                                  where u.UserName == userName
                                  select o)
            {
                rsl.Add(new OAuthAccountData(oAuth.Provider, oAuth.ProviderUserId));
            }

            return rsl;
        }

        public override DateTime GetCreateDate(string userName)
        {
            var membership = (from m in memberships
                              join u in userProfiles on m.UserId equals u.UserId
                              where u.UserName == userName
                              select m).FirstOrDefault();
            return membership == null ? DateTime.MinValue : membership.CreateDate.Value;
        }

        public override DateTime GetLastPasswordFailureDate(string userName)
        {
            var membership = (from m in memberships
                              join u in userProfiles on m.UserId equals u.UserId
                              where u.UserName == userName
                              select m).FirstOrDefault();
            return membership == null || membership.LastPasswordFailureDate.HasValue ?
                DateTime.MinValue : membership.LastPasswordFailureDate.Value;
        }

        public override DateTime GetPasswordChangedDate(string userName)
        {
            var membership = (from m in memberships
                              join u in userProfiles on m.UserId equals u.UserId
                              where u.UserName == userName
                              select m).FirstOrDefault();
            return membership == null || membership.PasswordChangedDate.HasValue ?
                DateTime.MinValue : membership.PasswordChangedDate.Value;
        }

        public override int GetPasswordFailuresSinceLastSuccess(string userName)
        {
            var membership = (from m in memberships
                              join u in userProfiles on m.UserId equals u.UserId
                              where u.UserName == userName
                              select m).FirstOrDefault();
            return membership == null ? -1 : membership.PasswordFailuresSinceLastSuccess;
        }

        public override int GetUserIdFromPasswordResetToken(string token)
        {
            var user = (from m in memberships
                        where m.PasswordVerificationToken == token
                        select m).FirstOrDefault();
            return user == null ? -1 : user.UserId;
        }

        public override bool IsConfirmed(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                throw new ArgumentNullException("userName");

            return getConfirmedUser(userName) != null;
        }

        public override bool ResetPasswordWithToken(string token, string newPassword)
        {
            if (string.IsNullOrEmpty(newPassword))
                throw new ArgumentNullException("newPassword");

            var user = (from m in memberships
                        where m.PasswordVerificationToken == token && m.PasswordVerificationTokenExpirationDate > DateTime.UtcNow
                        select m).FirstOrDefault();

            if (user == null)
                return false;

            user.Password = Crypto.HashPassword(newPassword);
            user.PasswordSalt = string.Empty;
            user.PasswordChangedDate = DateTime.UtcNow;
            user.PasswordVerificationToken = null;
            user.PasswordVerificationTokenExpirationDate = null;
            dbContext.SaveChanges();

            return true;
        }

        public override string ApplicationName
        {
            get
            {
                if (preProvider == null)
                    throw new NotSupportedException();
                return preProvider.ApplicationName;
            }
            set
            {
                if (preProvider == null)
                    throw new NotSupportedException();
                preProvider.ApplicationName = value;
            }
        }

        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            if (preProvider == null) throw new NotSupportedException();
            return preProvider.ChangePassword(username, oldPassword, newPassword);
        }

        public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
        {
            if (preProvider == null) throw new NotSupportedException();
            return preProvider.ChangePasswordQuestionAndAnswer(username, password, newPasswordQuestion, newPasswordAnswer);
        }

        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, 
        string passwordAnswer, bool isApproved, object providerUserKey, out System.Web.Security.MembershipCreateStatus status)
        {
            if (preProvider == null) throw new NotSupportedException();
            return preProvider.CreateUser(username, password, email, passwordQuestion, passwordAnswer, isApproved, providerUserKey, out status);
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            if (preProvider != null)
                return preProvider.DeleteUser(username, deleteAllRelatedData);

            foreach (var u in (from u in userProfiles where u.UserName == username select u))
                userProfiles.Remove(u);

            dbContext.SaveChanges();
            return true;
        }

        public override bool EnablePasswordReset
        {
            get { return preProvider != null && preProvider.EnablePasswordReset; }
        }

        public override bool EnablePasswordRetrieval
        {
            get { return preProvider != null && preProvider.EnablePasswordRetrieval; }
        }

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            if (preProvider != null)
                return preProvider.FindUsersByEmail(emailToMatch, pageIndex, pageSize, out totalRecords);
            else
                throw new NotSupportedException();
        }

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            if (preProvider != null)
                return preProvider.FindUsersByName(usernameToMatch, pageIndex, pageSize, out totalRecords);
            else
                throw new NotSupportedException();
        }

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            if (preProvider != null)
                return preProvider.GetAllUsers(pageIndex, pageSize, out totalRecords);
            else
                throw new NotSupportedException();
        }

        public override int GetNumberOfUsersOnline()
        {
            if (preProvider != null)
                return preProvider.GetNumberOfUsersOnline();
            else
                throw new NotSupportedException();
        }

        public override string GetPassword(string username, string answer)
        {
            if (preProvider != null)
                return preProvider.GetPassword(username, answer);
            else
                throw new NotSupportedException();
        }

        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            if (preProvider != null)
                return preProvider.GetUser(username, userIsOnline);

            var user = (from u in userProfiles
                        join m in memberships on u.UserId equals m.UserId
                        where u.UserName == username
                        select new
                        {
                            UserId = u.UserId,
                            UserName = u.UserName,
                            CreateDate = m.CreateDate,
                            PasswordChangeDate = m.PasswordChangedDate
                        }).FirstOrDefault();
            return user == null ? null : new MembershipUser(Membership.Provider.Name,
                username, user.UserId, null, null, null, true, false, user.CreateDate.Value,
                DateTime.MinValue, DateTime.MinValue, user.PasswordChangeDate.Value, DateTime.MinValue);
        }

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            if (preProvider != null)
                return preProvider.GetUser(providerUserKey, userIsOnline);

            throw new NotSupportedException();
        }

        public override string GetUserNameByEmail(string email)
        {
            if (preProvider == null)
                throw new NotSupportedException();

            return preProvider.GetUserNameByEmail(email);
        }

        public override int MaxInvalidPasswordAttempts
        {
            get { return preProvider == null ? int.MaxValue : preProvider.MaxInvalidPasswordAttempts; }
        }

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return preProvider == null ? 0 : preProvider.MinRequiredNonAlphanumericCharacters; }
        }

        public override int MinRequiredPasswordLength
        {
            get { return preProvider == null ? 0 : preProvider.MinRequiredPasswordLength; }
        }

        public override int PasswordAttemptWindow
        {
            get { return preProvider == null ? int.MaxValue : preProvider.PasswordAttemptWindow; }
        }

        public override MembershipPasswordFormat PasswordFormat
        {
            get { return preProvider == null ? MembershipPasswordFormat.Hashed : preProvider.PasswordFormat; }
        }

        public override string PasswordStrengthRegularExpression
        {
            get { return preProvider == null ? string.Empty : preProvider.PasswordStrengthRegularExpression; }
        }

        public override bool RequiresQuestionAndAnswer
        {
            get { return preProvider != null && preProvider.RequiresQuestionAndAnswer; }
        }

        public override bool RequiresUniqueEmail
        {
            get { return preProvider != null && preProvider.RequiresUniqueEmail; }
        }

        public override string ResetPassword(string username, string answer)
        {
            if (preProvider == null)
                throw new NotSupportedException();

            return preProvider.ResetPassword(username, answer);
        }

        public override bool UnlockUser(string userName)
        {
            if (preProvider == null)
                throw new NotSupportedException();

            return preProvider.UnlockUser(userName);
        }

        public override void UpdateUser(MembershipUser user)
        {
            if (preProvider == null)
                throw new NotSupportedException();

            preProvider.UpdateUser(user);
        }

        public override bool ValidateUser(string username, string password)
        {
            if (preProvider != null)
                return preProvider.ValidateUser(username, password);

            if (string.IsNullOrEmpty(username))
                throw new ArgumentNullException("username");

            if (string.IsNullOrEmpty(password))
                throw new ArgumentNullException("password");

            var user = getConfirmedUser(username);
            if (user == null)
                return false;

            var rsl = user.Password != null && Crypto.VerifyHashedPassword(user.Password, password);
            if (rsl)
                user.PasswordFailuresSinceLastSuccess = 0;
            else
            {
                user.PasswordFailuresSinceLastSuccess += 1;
                user.LastPasswordFailureDate = DateTime.UtcNow;
            }
            dbContext.SaveChanges();
            return rsl;
        }

        public override bool HasLocalAccount(int userId)
        {
            return (from m in memberships where m.UserId == userId select m).Any();
        }

        public override void CreateOrUpdateOAuthAccount(string provider, string providerUserId, string userName)
        {
            if (string.IsNullOrEmpty(userName))
                throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);

            var user = (from u in userProfiles where u.UserName == userName select u).FirstOrDefault();
            if (user == null)
                throw new MembershipCreateUserException(MembershipCreateStatus.InvalidUserName);

            var oAuthUser = (from o in oAuthMemberships where o.Provider == provider.ToUpperInvariant() 
            && o.ProviderUserId == providerUserId.ToUpperInvariant() select o).FirstOrDefault();
            if (oAuthUser == null)
            {
                oAuthMemberships.Add(new webpages_oauthmembership()
                {
                    Provider = provider.ToUpperInvariant(),
                    ProviderUserId = providerUserId.ToUpperInvariant(),
                    UserId = user.UserId
                });
            }
            else
            {
                oAuthUser.UserId = user.UserId;
            }
            dbContext.SaveChanges();
        }

        public override void DeleteOAuthAccount(string provider, string providerUserId)
        {
            foreach (var oauth in (from o in oAuthMemberships where o.ProviderUserId == providerUserId.ToUpperInvariant() 
            && o.Provider == providerUserId.ToUpperInvariant() select o))
                oAuthMemberships.Remove(oauth);
            dbContext.SaveChanges();
        }

        public override void DeleteOAuthToken(string token)
        {
            foreach (var t in (from o in dbContext.webpages_oauthtoken where o.Token == token select o))
                dbContext.webpages_oauthtoken.Remove(t);

            dbContext.SaveChanges();
        }

        public override string GetOAuthTokenSecret(string token)
        {
            var secret = (from o in dbContext.webpages_oauthtoken where o.Token == token select o).FirstOrDefault();
            return secret == null ? null : secret.Secret;
        }

        public override string GetUserNameFromId(int userId)
        {
            var user = (from u in userProfiles where u.UserId == userId select u).FirstOrDefault();
            return user == null ? null : user.UserName;
        }

        public override int GetUserIdFromOAuth(string provider, string providerUserId)
        {
            var user = (from u in oAuthMemberships
                        where u.Provider == provider.ToUpperInvariant() && u.ProviderUserId == provider.ToUpperInvariant()
                        select u).FirstOrDefault();
            return user == null ? -1 : user.UserId;
        }

        public override void ReplaceOAuthRequestTokenWithAccessToken(string requestToken, string accessToken, string accessTokenSecret)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                foreach (var oauth in (from o in dbContext.webpages_oauthtoken where o.Token == requestToken select o))
                    dbContext.webpages_oauthtoken.Remove(oauth);

                dbContext.SaveChanges();

                StoreOAuthRequestToken(accessToken, accessTokenSecret);
                ts.Complete();
            }
        }

        public override void StoreOAuthRequestToken(string requestToken, string requestTokenSecret)
        {
            var secret = (from oauth in dbContext.webpages_oauthtoken where oauth.Token == requestToken select oauth).FirstOrDefault();
            if (secret == null)
            {
                dbContext.webpages_oauthtoken.Add(new webpages_oauthtoken()
                {
                    Token = requestToken,
                    Secret = requestTokenSecret
                });
            }
            else
            {
                secret.Secret = requestTokenSecret;
            }
            dbContext.SaveChanges();
        }

    }
}

然后是 RoleProvider 的代码。

using System;
using System.Collections.Generic;
using System.Data.Linq.SqlClient;
using System.Globalization;
using System.Linq;
using System.Resources;
using System.Transactions;
using System.Web;
using System.Web.Security;

namespace TestSimpleMembership.Help
{
    public class MySqlSimpleRoleProvider : RoleProvider
    {
        private static string Security_NoUserFound = "No user was found that has the name "{0}".";

        private static string SimpleRoleProvder_UserNotInRole = "User "{0}" is not in role "{1}".";
        private static string SimpleRoleProvder_UserAlreadyInRole = "User "{0}" is already in role "{1}".";
        private static string SimpleRoleProvider_RoleExists = "Role "{0}" already exists.";
        private static string SimpleRoleProvder_RolePopulated = "The role "{0}" cannot be deleted because there are still users in the role.";
        private static string SimpleRoleProvider_NoRoleFound = "No role was found that has the name "{0}".";

        private static string DEFAULT_PROVIDER_NAME = "MySQLRoleProvider";
        private static string DEFAULT_NAME = "MySqlSimpleRoleProvider";
        private static string DEFAULT_PROVIDER_CONFIG_NAME = "provider";

        private RoleProvider preProvider;
        private MemberAuthorContext dbContext;
        
        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            if (config == null) throw new ArgumentNullException("config");
            if (string.IsNullOrEmpty(name)) name = DEFAULT_NAME;
            base.Initialize(name, config);

            var providerName = config[DEFAULT_PROVIDER_CONFIG_NAME];
            if (!string.IsNullOrEmpty(providerName))
                this.preProvider = Roles.Providers[providerName] ?? Roles.Providers[DEFAULT_PROVIDER_NAME];

            this.dbContext = new MemberAuthorContext();
        }

        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            if (preProvider != null)
            {
                preProvider.AddUsersToRoles(usernames, roleNames);
                return;
            }

            var users = getUserFromNames(usernames);

            var roles = getRoleFromNames(roleNames);

            foreach (var user in users)
            {
                foreach (var role in roles)
                {
                    if (IsUserInRole(user.UserName, role.RoleName))
                        throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture,
                        SimpleRoleProvder_UserAlreadyInRole, new object[] { user.UserName, role.RoleName }));

                    dbContext.webpages_usersinroles.Add(new webpages_usersinroles()
                    {
                        UserId = user.UserId,
                        RoleId = role.RoleId
                    });

                }
            }
            dbContext.SaveChanges();
        }

        private ListgetRoleFromNames(string[] roleNames)
        {
            var roles = new List();
            foreach (var name in roleNames)
            {
                var role = (from r in dbContext.webpages_roles where r.RoleName == name select r).FirstOrDefault();
                if (role == null)
                    throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture,
                        SimpleRoleProvider_NoRoleFound, new object[] { name }));
                roles.Add(role);
            }
            return roles;
        }

        private ListgetUserFromNames(string[] usernames)
        {
            var users = new List();
            foreach (var name in usernames)
            {
                var user = (from u in dbContext.userprofile where u.UserName == name select u).FirstOrDefault();
                if (user == null)
                    throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture,
                        Security_NoUserFound, new object[] { name }));
                users.Add(user);
            }
            return users;
        }

        public override string ApplicationName
        {
            get
            {
                if (preProvider == null)
                    throw new NotSupportedException();
                return preProvider.ApplicationName;
            }
            set
            {
                if (preProvider == null)
                    throw new NotSupportedException();
                preProvider.ApplicationName = value;
            }
        }

        public override void CreateRole(string roleName)
        {
            if (preProvider != null)
            {
                preProvider.CreateRole(roleName);
                return;
            }

            if ((from r in dbContext.webpages_roles where r.RoleName == roleName select r).Any())
                throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture,
                    SimpleRoleProvider_RoleExists, new object[] { roleName }));

            dbContext.webpages_roles.Add(new webpages_roles() { RoleName = roleName });
            dbContext.SaveChanges();
        }

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            if (preProvider != null)
                return preProvider.DeleteRole(roleName, throwOnPopulatedRole);

            var role = (from r in dbContext.webpages_roles where r.RoleName == roleName select r).FirstOrDefault();
            if (role == null)
                return false;

            var uirs = from uir in dbContext.webpages_usersinroles where uir.RoleId == role.RoleId select uir;
            if (throwOnPopulatedRole && uirs.Any())
                throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture,
                    SimpleRoleProvder_RolePopulated, new object[] { roleName }));

            using (TransactionScope ts = new TransactionScope())
            {
                foreach (var uir in uirs)
                    dbContext.webpages_usersinroles.Remove(uir);
                dbContext.webpages_roles.Remove(role);
                dbContext.SaveChanges();
                ts.Complete();
            }
            return true;
        }

        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            if (preProvider != null)
                return preProvider.FindUsersInRole(roleName, usernameToMatch);

            return (from u in dbContext.userprofile
                    join uir in dbContext.webpages_usersinroles on u.UserId equals uir.UserId
                    join r in dbContext.webpages_roles on uir.RoleId equals r.RoleId
                    where r.RoleName == roleName && SqlMethods.Like(u.UserName, usernameToMatch)
                    select u.UserName).ToArray();
        }

        public override string[] GetAllRoles()
        {
            if (preProvider != null)
                return preProvider.GetAllRoles();

            return (from r in dbContext.webpages_roles select r.RoleName).ToArray();
        }

        public override string[] GetRolesForUser(string username)
        {
            if (preProvider != null)
                return preProvider.GetRolesForUser(username);

            return (from r in dbContext.webpages_roles
                    join uir in dbContext.webpages_usersinroles on r.RoleId equals uir.RoleId
                    join u in dbContext.userprofile on uir.UserId equals u.UserId
                    where u.UserName == username
                    select r.RoleName).Distinct().ToArray();
        }

        public override string[] GetUsersInRole(string roleName)
        {
            if (preProvider != null)
                return preProvider.GetUsersInRole(roleName);

            return (from u in dbContext.userprofile
                    join uir in dbContext.webpages_usersinroles on u.UserId equals uir.UserId
                    join r in dbContext.webpages_roles on uir.RoleId equals r.RoleId
                    where r.RoleName == roleName
                    select u.UserName).ToArray();
        }

        public override bool IsUserInRole(string username, string roleName)
        {
            if (preProvider != null)
                return preProvider.IsUserInRole(username, roleName);

            return (from u in dbContext.userprofile
                    join uir in dbContext.webpages_usersinroles on u.UserId equals uir.UserId
                    join r in dbContext.webpages_roles on uir.RoleId equals r.RoleId
                    where u.UserName == username && r.RoleName == roleName
                    select u).Any();
        }

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            if (preProvider != null)
            {
                preProvider.RemoveUsersFromRoles(usernames, roleNames);
                return;
            }

            foreach (var name in roleNames)
            {
                if (!RoleExists(name))
                    throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture,
                        SimpleRoleProvider_NoRoleFound, new object[] { name }));
            }

            foreach (var user in usernames)
            {
                foreach (var role in roleNames)
                {
                    if (!IsUserInRole(user, role))
                        throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture,
                            SimpleRoleProvder_UserNotInRole, new object[] { user, role }));
                }
            }

            var users = getUserFromNames(usernames);
            var roles = getRoleFromNames(roleNames);

            foreach (var user in users)
            {
                foreach (var role in roles)
                {
                    dbContext.webpages_usersinroles.Remove((from uir in dbContext.webpages_usersinroles
                                                            where uir.RoleId == role.RoleId && uir.UserId == user.UserId
                                                            select uir).FirstOrDefault());
                }
            }
            dbContext.SaveChanges();
        }

        public override bool RoleExists(string roleName)
        {
            if (preProvider != null)
                return preProvider.RoleExists(roleName);

            return (from r in dbContext.webpages_roles where r.RoleName == roleName select r).Any();
        }

    }
}

以上代码可以看出，如果你指定了原来的 Membership 系统的 Provider 的话，其实两个认证系统是可以整合在一起的