Click here to Skip to main content
Click here to Skip to main content

Membership and Role providers for MySQL

, 20 Dec 2005
Rate this:
Please Sign up or sign in to vote.
This article provides two files that contain a Membership Provider and a Role Provider for ASP.NET v2.0.

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).
    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):
    <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?

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Rakotomalala Andriniaina
Web Developer
France France
I am a software developper working for a multinational, in the south of France.

Comments and Discussions

 
QuestionRe: PasswordRecovery Control Problem Pinmemberjosepaulino30-Sep-07 17:57 
AnswerRe: PasswordRecovery Control Problem PinmemberPurple Fox30-Sep-07 23:18 
GeneralRe: PasswordRecovery Control Problem Pinmemberjosepaulino1-Oct-07 11:12 
GeneralThat assembly does not allow partially trusted callers Pinmembergabbeauchamp1-Dec-06 11:04 
GeneralRe: That assembly does not allow partially trusted callers Pinmember_metroid_6-Dec-06 8:42 
GeneralError When Creating User PinmemberBrad8730-Nov-06 5:16 
GeneralRe: Error When Creating User PinmemberJFH10824-Dec-06 10:18 
GeneralRe: Error When Creating User PinmemberDerek Curtis3-Feb-07 8:08 
AnswerRe: Error When Creating User Pinmemberdustinhorne5-Apr-07 11:16 
GeneralVB.Net version PinmembertheDude5B6-Nov-06 3:37 
GeneralRe: VB.Net version PinmemberKilty20-Feb-07 1:56 
QuestionQuestion PinmemberShin-Ra31-Oct-06 10:11 
QuestionNew code doesn't work Pinmemberflaxx1-Oct-06 23:04 
AnswerRe: New code doesn't work Pinmemberperlmunger18-Oct-06 11:26 
I have the same problem. Did you figure this out?
 
-Matt
 
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

AnswerRe: New code doesn't work Pinmemberflaxx18-Oct-06 11:38 
GeneralRe: New code doesn't work PinmemberFreddy Setiawan AW19-Oct-06 5:16 
AnswerRe: New code doesn't work Pinmemberflaxx19-Oct-06 5:21 
GeneralRe: New code doesn't work Pinmembermaxim.gorkij23-Nov-06 1:10 
AnswerRe: New code doesn't work Pinmemberperlmunger19-Oct-06 15:14 
GeneralRe: New code doesn't work PinmemberFreddy Setiawan AW19-Oct-06 15:40 
AnswerRe: New code doesn't work Pinmemberjswoofer25-Oct-06 9:56 
AnswerRe: New code doesn't work Pinmembersweeperq2-Nov-06 8:59 
AnswerRe: New code doesn't work Pinmembernicolasfox2-Nov-06 22:29 
GeneralRe: New code doesn't work Pinmemberedwardttt26-Nov-06 8:53 
GeneralRe: New code doesn't work Pinmemberflaxx1-Dec-06 0:24 
GeneralRe: New code doesn't work Pinmemberflaxx14-Dec-06 6:09 
GeneralError Logging In/Out with Firefox Pinmemberjosepaulino24-Sep-06 18:18 
NewsNew updated code with bug fixes PinmemberRakotomalala Andriniaina6-Sep-06 10:40 
GeneralIt's a good thinf that it works then PinmemberEShy29-Sep-06 9:21 
QuestionSample code on how to log in users [modified] PinmemberSveinErik26-Aug-06 23:46 
AnswerRe: Sample code on how to log in users PinmemberRakotomalala Andriniaina6-Sep-06 10:43 
Questionerror connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. [modified] Pinmemberwrighty17-Aug-06 10:39 
AnswerRe: error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. [modified] PinmemberRakotomalala Andriniaina6-Sep-06 10:45 
QuestionWhy am I not showing as logged in? Pinmemberjhdoc815-Aug-06 13:19 
QuestionMySQL .... Pinmemberthachvv181-Aug-06 23:42 
GeneralIs this user online Pinmemberpaolo100021-Jul-06 3:33 
GeneralIssues in the web config not finding the class PinmemberAlanSchneider10-Jul-06 10:56 
AnswerRe: Issues in the web config not finding the class Pinmemberlitograph6820-Mar-07 13:48 
GeneralRe: Issues in the web config not finding the class [modified] Pinmemberayfaizal20-Mar-07 15:36 
GeneralCheking session PinmemberFrehley9-Jul-06 13:27 
GeneralMySql Profile Provider [modified] PinmemberEdacio23-Jun-06 10:10 
GeneralRe: MySql Profile Provider PinmemberEdacio28-Jun-06 10:13 
GeneralAccess Denied Pinmemberbonfire8922-Jun-06 18:41 
GeneralRe: Access Denied PinmemberRakotomalala Andriniaina6-Sep-06 10:59 
Generalemail and password validation Pinmemberaldaran20-Jun-06 10:49 
GeneralRe: email and password validation PinmemberRakotomalala Andriniaina6-Sep-06 10:50 
GeneralThanks!!! PinmemberSleepyCrat16-Jun-06 7:02 
QuestionProfile Provider ? Pinmembertbp039-Jun-06 10:27 
AnswerRe: Profile Provider ? PinmemberEdacio28-Jun-06 10:13 
GeneralServer Explorer Pinmembertkrafael_net9-Jun-06 8:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.1 | Last Updated 20 Dec 2005
Article Copyright 2005 by Rakotomalala Andriniaina
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid