Click here to Skip to main content
15,868,164 members
Articles / Web Development / ASP.NET
Article

Membership and Role providers for MySQL

Rate me:
Please Sign up or sign in to vote.
4.86/5 (71 votes)
20 Dec 20051 min read 1M   5.8K   178   227
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).
    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?

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


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

Comments and Discussions

 
QuestionRe: I get errors when running the sql Pin
Mel Shellam8-Jul-06 2:21
Mel Shellam8-Jul-06 2:21 
AnswerRe: I get errors when running the sql Pin
ehel8214-Jul-06 12:50
ehel8214-Jul-06 12:50 
GeneralSQL Pin
brajoh8-Mar-06 0:28
brajoh8-Mar-06 0:28 
GeneralRe: SQL Pin
Emma Burrows28-Mar-06 8:51
Emma Burrows28-Mar-06 8:51 
GeneralRe: SQL Pin
gremlin0629-Mar-06 12:55
gremlin0629-Mar-06 12:55 
GeneralExtending it Pin
Eng. Jalal21-Feb-06 22:05
Eng. Jalal21-Feb-06 22:05 
GeneralRe: Extending it Pin
Rakotomalala Andriniaina22-Feb-06 22:15
Rakotomalala Andriniaina22-Feb-06 22:15 
QuestionVARCHAR and TEXT question Pin
5sport17-Feb-06 21:07
5sport17-Feb-06 21:07 
GeneralProvider tab of Web Site Administration Tool not showing provider Pin
SillyWilly7812-Feb-06 15:32
SillyWilly7812-Feb-06 15:32 
GeneralRe: Provider tab of Web Site Administration Tool not showing provider Pin
gremlin0629-Mar-06 13:05
gremlin0629-Mar-06 13:05 
GeneralTrust level problems Pin
owen_meany4-Feb-06 7:00
owen_meany4-Feb-06 7:00 
GeneralRe: Trust level problems Pin
DionO7-Feb-06 0:24
DionO7-Feb-06 0:24 
GeneralRe: Trust level problems Pin
owen_meany7-Feb-06 3:10
owen_meany7-Feb-06 3:10 
GeneralRe: Trust level problems Pin
DionO7-Feb-06 3:13
DionO7-Feb-06 3:13 
GeneralRe: Trust level problems Pin
Freddy Setiawan AW19-Oct-06 14:35
Freddy Setiawan AW19-Oct-06 14:35 
QuestionSQL Error Pin
g_smasher30-Jan-06 0:55
g_smasher30-Jan-06 0:55 
AnswerRe: SQL Error Pin
Rakotomalala Andriniaina31-Jan-06 1:27
Rakotomalala Andriniaina31-Jan-06 1:27 
JokeTo make these work on a VB website.... Pin
nakins15-Jan-06 16:33
nakins15-Jan-06 16:33 
GeneralRe: To make these work on a VB website.... Pin
jazzyjas7825-Feb-06 22:56
jazzyjas7825-Feb-06 22:56 
GeneralRe: To make these work on a VB website.... Pin
nakins26-Feb-06 5:27
nakins26-Feb-06 5:27 
GeneralRe: To make these work on a VB website.... Pin
jazzyjas7826-Feb-06 7:09
jazzyjas7826-Feb-06 7:09 
GeneralRe: To make these work on a VB website.... Pin
nakins26-Feb-06 7:46
nakins26-Feb-06 7:46 
GeneralThanks! Pin
karl l15-Jan-06 0:34
karl l15-Jan-06 0:34 
GeneralConverting this to VB? And index question. Pin
nakins10-Jan-06 7:28
nakins10-Jan-06 7:28 
GeneralRe: Converting this to VB? And index question. Pin
Rakotomalala Andriniaina11-Jan-06 3:09
Rakotomalala Andriniaina11-Jan-06 3:09 

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

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