Membership and Role providers for MySQL

• Download source files - 11.0 Kb

Introduction

This article provides two files that contain a Membership provider and a Role provider for ASP.NET v2.0.

Microsoft provides a Membership provider in the framework but only for SQL Server. The class does not seem to work for MySQL so I decided to write a new provider from the ODBC provider sample code included in the framework SDK.

How to use it

To use these classes, you will need the latest MySQL .NET Connector. You might need to recompile it with the new C# compiler for v2.0 (but the new framework should normally support v1.0 or 1.1 assemblies).

1. Create the tables (note that these SQL commands might not work with MySQL 4).
   SQL

   CREATE TABLE Roles
   (
     Rolename Varchar (255) NOT NULL,
     ApplicationName varchar (255) NOT NULL
   )
   
   CREATE TABLE UsersInRoles
   (
     Username Varchar (255) NOT NULL,
     Rolename Varchar (255) NOT NULL,
     ApplicationName Text (255) NOT NULL
   )
   ALTER TABLE 'usersinroles' 
         ADD INDEX ( 'Username', 'Rolename', 'ApplicationName') ;
   ALTER TABLE 'roles' ADD INDEX ( 'Rolename' , 'ApplicationName' ) ;
   
   CREATE TABLE 'users' (
     'PKID' varchar(36) collate latin1_general_ci NOT NULL default '',
     'Username' varchar(255) collate latin1_general_ci NOT NULL default '',
     'ApplicationName' varchar(100) 
                       collate latin1_general_ci NOT NULL default '',
     'Email' varchar(100) collate latin1_general_ci NOT NULL default '',
     'Comment' varchar(255) collate latin1_general_ci default NULL,
     'Password' varchar(128) collate latin1_general_ci NOT NULL default '',
     'PasswordQuestion' varchar(255) collate latin1_general_ci default NULL,
     'PasswordAnswer' varchar(255) collate latin1_general_ci default NULL,
     'IsApproved' tinyint(1) default NULL,
     'LastActivityDate' datetime default NULL,
     'LastLoginDate' datetime default NULL,
     'LastPasswordChangedDate' datetime default NULL,
     'CreationDate' datetime default NULL,
     'IsOnLine' tinyint(1) default NULL,
     'IsLockedOut' tinyint(1) default NULL,
     'LastLockedOutDate' datetime default NULL,
     'FailedPasswordAttemptCount' int(11) default NULL,
     'FailedPasswordAttemptWindowStart' datetime default NULL,
     'FailedPasswordAnswerAttemptCount' int(11) default NULL,
     'FailedPasswordAnswerAttemptWindowStart' datetime default NULL,
     PRIMARY KEY  ('PKID')
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2. Change MySqlMembershipProvider::encryptionKey to a random hexadecimal value of your choice.
3. Upload the two files to ~/App_Code.
4. Modify your web.config using the following template (if you are on a shared hosting server, you will have to set writeExceptionsToEventLog to false):
   XML

   <connectionStrings>
       <add name="ConnString" 
         connectionString="Database=YOURDBNAME;Data Source=localhost;
                           User Id=YOURUSERNAME;Password=YOURPWD" />
   </connectionStrings>
   <system.web>
       <roleManager defaultProvider="MySqlRoleProvider"
           enabled="true"
           cacheRolesInCookie="true"
           cookieName=".ASPROLES"
           cookieTimeout="30"
           cookiePath="/"
           cookieRequireSSL="false"
           cookieSlidingExpiration="true"
           cookieProtection="All" >
       <providers>
           <clear />
           <add
               name="MySqlRoleProvider"
               type="Andri.Web.MySqlRoleProvider"
               connectionStringName="ConnString"
               applicationName="YOURAPPNAME"
               writeExceptionsToEventLog="true"
           />
       </providers>
       </roleManager>
       
       <membership defaultProvider="MySqlMembershipProvider" 
                   userIsOnlineTimeWindow="15">
           <providers>
               <clear />
               <add
                   name="MySqlMembershipProvider"
                   type="Andri.Web.MySqlMembershipProvider"
                   connectionStringName="ConnString"
                   applicationName="YOURAPPNAME"
                   enablePasswordRetrieval="false"
                   enablePasswordReset="true"
                   requiresQuestionAndAnswer="true"
                   requiresUniqueEmail="true"
                   passwordFormat="Hashed"
                   writeExceptionsToEventLog="true"
               />
         </providers>
       </membership>
   </system.web>

5. That's it! You can use the login controls provided in the ASP.NET framework v2.0 if you want or use your own controls.

You can do whatever you want with this code but just send me an email if you have done some improvements or some bug correction. Isn't that what this website is about, sharing?