Contents
Introduction
As I started to work on a new AJAX-enabled website, I looked around for a MySQL implementation of the ASP.NET 2.0 membership provider. To my amazement, I couldn't find anything. So I decided to do my own implementation. After a few days of dev work, on and off, I found that I hadn't only implemented the membership provider but also the roles provider, site map provider and personalization provider.
All the providers inherit from the generic providers from Microsoft.
[^]
Using the Code
Using the providers is really easy.
Create a new database on your MySQL server, e.g. SimpleProviders.
Execute the following SQL statement on the newly created database.
CREATE TABLE `personalization` (
`username` varchar(255) default NULL,
`path` varchar(255) default NULL,
`applicationname` varchar(255) default NULL,
`personalizationblob` blob
);
CREATE TABLE `profiles` (
`UniqueID` int(8) NOT NULL auto_increment,
`Username` varchar(255) NOT NULL default '',
`ApplicationName` varchar(255) NOT NULL default '',
`IsAnonymous` tinyint(1) default '0',
`LastActivityDate` datetime default NULL,
`LastUpdatedDate` datetime default NULL,
PRIMARY KEY (`UniqueID`),
UNIQUE KEY `PKProfiles` (`Username`,`ApplicationName`),
UNIQUE KEY `PKID` (`UniqueID`)
);
CREATE TABLE `roles` (
`Rolename` varchar(255) NOT NULL default '',
`ApplicationName` varchar(255) NOT NULL default '',
PRIMARY KEY (`Rolename`,`ApplicationName`)
);
CREATE TABLE `sitemap` (
`ID` int(11) NOT NULL auto_increment,
`ApplicationName` varchar(255) NOT NULL default '',
`Title` varchar(255) default NULL,
`Description` text,
`Url` text,
`Roles` text,
`Parent` int(11) default NULL,
PRIMARY KEY (`ID`)
);
CREATE TABLE `users` (
`PKID` varchar(255) NOT NULL default '',
`Username` varchar(255) NOT NULL default '',
`ApplicationName` varchar(255) NOT NULL default '',
`Email` varchar(128) default NULL,
`Comment` varchar(255) default NULL,
`Password` varchar(128) NOT NULL default '',
`FailedPasswordAttemptWindowStart` datetime default NULL,
`PasswordQuestion` varchar(255) default NULL,
`IsLockedOut` tinyint(1) default '0',
`PasswordAnswer` varchar(255) default NULL,
`FailedPasswordAnswerAttemptCount` int(8) default '0',
`FailedPasswordAttemptCount` int(8) default '0',
`IsApproved` tinyint(1) NOT NULL default '0',
`FailedPasswordAnswerAttemptWindowStart` datetime default NULL,
`LastActivityDate` datetime default NULL,
`IsOnLine` tinyint(1) default '0',
`CreationDate` datetime default NULL,
`LastPasswordChangedDate` datetime default NULL,
`LastLockedOutDate` datetime default NULL,
`LastLoginDate` datetime default NULL,
PRIMARY KEY (`PKID`),
UNIQUE KEY `PKID` (`PKID`),
KEY `PKID_2` (`PKID`),
KEY `usr` (`Username`)
);
CREATE TABLE `usersinroles` (
`Username` varchar(255) NOT NULL default '',
`Rolename` varchar(255) NOT NULL default '',
`ApplicationName` varchar(255) NOT NULL default '',
PRIMARY KEY (`Username`,`Rolename`,`ApplicationName`)
);
There is an SQL file named DBStructure.sql included with the source code ZIP file that contains the code above.
Open Visual Studio and create a new Website Project.
Add a reference to the Simple.Providers.MySQL.dll file.
Make the following changes to your web.config file:
Add the connection string to your newly created database to the connectionStrings section, e.g. <add connectionstring="Driver={MySQL ODBC 3.51 Driver};server={Your Server IP};port={Your Server Port No.};option=3;database={New Database Name};uid={Your username};pwd={Your password}" name="SimpleProviderConnectionString" providername="System.Data.Odbc" />.
* Please replace the {Your Server IP}, {Your Server Port No.}, {New Database Name}, {Your username} and {Your password} entries in the connection string with your own values.
Under the <system.web> section add the following:
<siteMap defaultProvider="siteMapProvider" enabled="true">
<providers>
<clear />
<add name="siteMapProvider"
type="Simple.Providers.MySQL.MysqlSiteMapProvider"
connectionStringName="SimpleProviderConnectionString"
applicationName="{Your App Name}"
description="MySQL site map provider"
securityTrimmingEnabled="true"/>
</providers>
</siteMap>
<roleManager defaultProvider="roleProvider" enabled="true"
cacheRolesInCookie="false" cookieName=".ASPROLES"
cookieTimeout="7200" cookiePath="/" cookieRequireSSL="false"
cookieSlidingExpiration="true" cookieProtection="All">
<providers>
<clear />
<add name="roleProvider"
type="Simple.Providers.MySQL.MysqlRoleProvider"
connectionStringName="SimpleProviderConnectionString"
applicationName="{Your App Name}"
description="MySQL role provider"/>
</providers>
</roleManager>
<membership defaultProvider="membershipProvider"
userIsOnlineTimeWindow="15">
<providers>
<clear />
<add name="membershipProvider"
type="Simple.Providers.MySQL.MysqlMembershipProvider"
connectionStringName="SimpleProviderConnectionString"
applicationName="{Your App Name}"
enablePasswordRetrieval="true"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
requiresUniqueEmail="true" passwordFormat="Encrypted"
minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0"
description="MySQL membership provider"/>
</providers>
</membership>
<profile defaultProvider="profileProvider"
automaticSaveEnabled="true">
<providers>
<clear />
<add name="profileProvider"
type="Simple.Providers.MySQL.MysqlProfileProvider"
connectionStringName="SimpleProviderConnectionString"
applicationName="{Your App Name}"
description="MySQL Profile Provider"/>
</providers>
<properties>
<clear />
</properties>
</profile>
<webParts>
<personalization defaultProvider="personalizationProvider">
<providers>
<clear />
<add name="personalizationProvider"
type="Simple.Providers.MySQL.
MysqlPersonalizationProvider"
connectionStringName=
"{Your Connection String Name}" applicationName="
{Your App Name}"
description="MySQL Personalization Provider/>
</providers>
</personalization>
</webParts>
/* !!! Please replace the {Your App Name} instances with a valid
application name. The application name should not contain
any spaces or special characters. !!! */
Everything should be set up correctly now. Continue with the rest of your project and make sure to make use of the features provided by the above mentioned providers.
[^]
I made use of the Microsoft MSDN site while developing the provider suite. For more info on...
[^]
- 2007-04-25: Initial release of the article
- 2007-10-18: Update of the
UpdateUser code to enable LastActivityDate functionality
[^]
