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

MySQL Membership, Role, Sitemap, Personalization Provider for ASP.NET 2.0

, 7 Sep 2007
Rate this:
Please Sign up or sign in to vote.
A Membership, Role, Sitemap, and Personalization provider using MySQL.

Introduction

This is the implementation of a Membership, Role, Sitemap, and Personalization provider of ASP.NET using MySQL as the backend database. This class uses the native net MySQL Connector version 5.1.2.

Background

This code is based on the work of J Snyman; he used ODBC to connect to MySQL. I modified it to use the native net MySQL Connector version 5.1.2.

Using the code

This is actually a C# class project. You only need to reference the compiled DLL output from this project in your Web project. You can use this even if the language in your web app is VB. The provided compiled DLL can be used right out of the box, but if you prefer, you can compile your own.

Using the providers is really easy.

  1. Create a new database on your MySQL server, e.g.: SimpleProviders.
  2. Execute the following SQL statement on the newly created database:
  3. 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 a SQL file named DBStructure.sql included with the source code zip file that contains the code above.

  4. Open Visual Studio and create a new Website Project.
  5. Add a reference to Simple.Providers.MySQL.dll.
  6. Make the following changes to your web.config file:
    1. Add the connection string to your newly created database to the connectionStrings section:
    2. <add 
        connectionString="server=localhost;database=simpleproviders;
                          user id=<put user>;pwd=<put password>" 
        name="SimpleProviderconnectionstring" 
        providerName="MySql.Data.MySqlClient"/>

      * Please replace the {Your username} and {Your password} entries in the connection string with your own values.

    3. Under the <system.web> section, add the following:
    4. <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 />
          <!--
            Add any needed attributes for profiles here.
            eg. <add name="Theme" type="System.String" 
            defaultValue="Default"/>
          -->
        </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.

  7. 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.

History

  • 9/7/2007 - Uses the MySQL Net Connector 5.1.2 instead of ODBC.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

onrac
Web Developer
Philippines Philippines
No Biography provided

Comments and Discussions

 
Generalhelp me remove this error. Pinmembervirus232-Feb-10 1:25 
QuestionHow do the Simple Providers recognize the current version of MySQL Connector Driver PinmemberLuizItatiba8-Sep-09 17:34 
QuestionRe: Webparts PinmemberMember 41156042-Apr-09 13:27 
QuestionHow CurrentNodes and accented with spaces in SiteMapPath? For example São Paulo-SP. PinmemberLuizItatiba22-Oct-08 9:25 
QuestionHow to correctly set the standard MemberShipProvider Provider of the SqlServer2005 to work together with the MySqlSiteMapProvider? PinmemberLuizItatiba8-Oct-08 6:26 
QuestionHow can I use same database for more than one application? PinmemberUmut TURER2-Sep-08 6:39 
QuestionCan someone provide the same example with some aspx pages..... PinmemberMember 358658015-Jul-08 23:20 
QuestionFail to test to the provider in the Asp.net Configuration PinmemberCGARRIGOS20-May-08 1:54 
QuestionHow to Order Alphabetically by the children MySqlSiteMapProvider us PinmemberLuizItatiba1-Apr-08 16:30 
QuestionAs Custom Menus of MYSQLSITEMAPPROVIDER, based on profiles or roles PinmemberLuizItatiba1-Apr-08 9:24 
QuestionError Tool Administration of Site or ASP.NET Configuration PinmemberLuizItatiba1-Apr-08 8:57 
Based on your article and did correct the webconfig below for my application which is now working properly but the Tool Administration of Site, in the error happens Safety Guide
 
There is a problem with data storage selected. You may be caused by credential or Filename invalid, or by inadequate level of permission. He may also is occurring because the resource management functions are not allowed. Click on the button below to be redirected to the page where you can choose a new data storage.
 
The message below can help diagnose the problem: Hashed or Encrypted passwords are not supported with self-generated keys. (C: \ Inetpub \ wwwroot \ TesteMySqlSiteMapProvider \ TesteMySqlSiteMapProvider \ web.config line 56)
 
As the line 56 of webconfig being as
 
Type = "Simple.Providers.MySQL.MysqlMembershipProvider"
 
Below the full WebConfig to better analyse and I look forward to your response in my doubts will be possible at. Luiz - Brazil-SP.
 
My WebConfig
 
<?xml version="1.0"?>
<configuration>
<configSections>
<sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
<sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="Everywhere" />
<section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication" />
<section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication" />
</sectionGroup>
</sectionGroup>
</sectionGroup>
</configSections>
<connectionStrings>
<add connectionString="server=myserver;database=mydatabase;user id=myid;pwd=mypass" name="SimpleProviderConnectionString" providerName="MySql.Data.MySqlClient"/>
 

 
</connectionStrings>
<system.web>
<customErrors mode="Off"/>
<!-- INÍCIO CONFIGS MYSQLSITEMAPPROVIDER -->
<siteMap defaultProvider="siteMapProvider" enabled="true">
 
<providers>
<clear />
<add name="siteMapProvider"
type="Simple.Providers.MySQL.MysqlSiteMapProvider"
connectionStringName="SimpleProviderConnectionString"
applicationName="TesteMySqlSiteMapProvider"
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="TesteMySqlSiteMapProvider"
description="MySQL role provider"/>
</providers>
</roleManager>
<membership defaultProvider="membershipProvider"
userIsOnlineTimeWindow="15">
<providers>
<clear />
<add name="membershipProvider"
type="Simple.Providers.MySQL.MysqlMembershipProvider"
connectionStringName="SimpleProviderConnectionString"
applicationName="TesteMySqlSiteMapProvider"
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="TesteMySqlSiteMapProvider"
description="MySQL Profile Provider"/>
</providers>
<properties>
<clear />
<!--
Add any needed attributes for profiles here.
eg. <add name="Theme" type="System.String"
defaultValue="Default"/>
-->
</properties>
</profile>
<webParts>
<personalization defaultProvider="personalizationProvider">
<providers>
<clear/>
<add name="personalizationProvider"
type="Simple.Providers.MySQL.
MysqlPersonalizationProvider"
connectionStringName="SimpleProviderConnectionString"
applicationName="TesteMySqlSiteMapProvider"
description="MySQL Personalization Provider"

/>

</providers>
</personalization>
</webParts>
<!-- FIM CONFIGS MYSQL SITEMAPPROVIDER -->
<pages>
<controls>
<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</controls>
</pages>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
-->
<compilation debug="false">
<assemblies>
<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</assemblies>
</compilation>
 
<httpHandlers>
<remove verb="*" path="*.asmx"/>
<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
</httpHandlers>
 
<httpModules>
<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</httpModules>
</system.web>
 
<system.web.extensions>
<scripting>
<webServices>
<!-- Uncomment this line to customize maxJsonLength and add a custom converter -->
<!--
<jsonSerialization maxJsonLength="500">
<converters>
<add name="ConvertMe" type="Acme.SubAcme.ConvertMeTypeConverter"/>
</converters>
</jsonSerialization>
-->
<!-- Uncomment this line to enable the authentication service. Include requireSSL="true" if appropriate. -->
<!--
<authenticationService enabled="true" requireSSL = "true|false"/>
-->
 
<!-- Uncomment these lines to enable the profile service. To allow profile properties to be retrieved
and modified in ASP.NET AJAX applications, you need to add each property name to the readAccessProperties and
writeAccessProperties attributes. -->
<!--
<profileService enabled="true"
readAccessProperties="propertyname1,propertyname2"
writeAccessProperties="propertyname1,propertyname2" />
-->
</webServices>
<!--
<scriptResourceHandler enableCompression="true" enableCaching="true" />
-->
</scripting>
</system.web.extensions>
 
<system.webServer>
<validation validateIntegratedModeConfiguration="false"/>
<modules>
<add name="ScriptModule" preCondition="integratedMode" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</modules>
<handlers>
<remove name="WebServiceHandlerFactory-Integrated" />
<add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode"
type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode"
type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</handlers>
</system.webServer>
</configuration>
 
LADEF

QuestionError WebConfig PinmemberLuizItatiba31-Mar-08 10:18 
QuestionUsing the driver MySqlConnector 5.0 instead of 5.1.2 PinmemberLuizItatiba26-Mar-08 12:44 
GeneralGeneral Comments PinmvpJohn Simmons / outlaw programmer19-Jan-08 4:33 
QuestionODBC still present in the MysqlPersonalizationProvider.cs PinmemberLisburn Lad8-Jan-08 4:01 
AnswerRe: ODBC still present in the MysqlPersonalizationProvider.cs Pinmemberluciano vilela26-May-10 4:20 
GeneralImplementation Example PinmemberCreativeGolf19-Oct-07 5:54 
Generaljust Note Pinmemberibyasmo30-Sep-07 10:45 
QuestionERROR MESSAGE - SECURITY PinmemberKanedogg17-Sep-07 7:48 
AnswerRe: ERROR MESSAGE - SECURITY [modified] Pinmemberonrac17-Sep-07 17:18 
GeneralRe: ERROR MESSAGE - SECURITY PinmemberKanedogg18-Sep-07 0:47 
QuestionRe: MySQL Membership, Role Provider [modified] PinmemberFuzziebrain13-Sep-07 15:23 
AnswerRe: MySQL Membership, Role Provider Pinmemberonrac17-Sep-07 17:20 
Generalgood effort Pinmemberlxwde10-Sep-07 14:52 
Generalnice Pinmembermascix10-Sep-07 13:46 

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.140827.1 | Last Updated 8 Sep 2007
Article Copyright 2007 by onrac
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid