MySQL :: Connectors and APIs Manual :: 4.6.2.1 Tutorial: Connector/NET ASP.NET Membership and Role Provider

Connectors and APIs Manual / ... / Tutorial: Connector/NET ASP.NET Membership and Role Provider

4.6.2.1 Tutorial: Connector/NET ASP.NET Membership and Role Provider

Many websites feature the facility for the user to create a user account. They can then log into the website and enjoy a personalized experience. This requires that the developer creates database tables to store user information, along with code to gather and process this data. This represents a burden on the developer, and there is the possibility for security issues to creep into the developed code. However, ASP.NET introduced the membership system. This system is designed around the concept of membership, profile, and role providers, which together provide all of the functionality to implement a user system, that previously would have to have been created by the developer from scratch.

Currently, MySQL Connector/NET includes web providers for membership (or simple membership), roles, profiles, session state, site map, and web personalization.

This tutorial shows you how to set up your ASP.NET web application to use the Connector/NET membership and role providers. It assumes that you have MySQL Server installed, along with Connector/NET and Microsoft Visual Studio. This tutorial was tested with Connector/NET 6.0.4 and Microsoft Visual Studio 2008 Professional Edition. It is recommended you use 6.0.4 or above for this tutorial.

Create a new MySQL database using the MySQL Command-Line Client program (mysql), or other suitable tool. It does not matter what name is used for the database, but record it. You specify it in the connection string constructed later in this tutorial. This database contains the tables, automatically created for you later, used to store data about users and roles.
Create a new ASP.NET website in Visual Studio. If you are not sure how to do this, refer to Section 4.6.4, “Tutorial: Data Binding in ASP.NET Using LINQ on Entities”, which demonstrates how to create a simple ASP.NET website.
Add References to MySql.Data and MySql.Web to the website project.
Locate the machine.config file on your system, which is the configuration file for the .NET Framework.
Search the machine.config file to find the membership provider MySQLMembershipProvider.
Add the attribute autogenerateschema="true". The appropriate section should now resemble the following example.

Note

For the sake of brevity, some information is excluded.
```
<membership>
 <providers>
   <add name="AspNetSqlMembershipProvider"
     type="System.Web.Security.SqlMembershipProvider"
     ...
     connectionStringName="LocalSqlServer"
     ... />
   <add name="MySQLMembershipProvider"
     autogenerateschema="true"
     type="MySql.Web.Security.MySQLMembershipProvider, 
           MySql.Web, Version=6.0.4.0, Culture=neutral, 
           PublicKeyToken=c5687fc88969c44d"
     connectionStringName="LocalMySqlServer"
     ... />
 </providers>
</membership>
```
Note

The connection string, LocalMySqlServer, connects to the MySQL server that contains the membership database.

The autogenerateschema="true" attribute will cause Connector/NET to silently create, or upgrade, the schema on the database server, to contain the required tables for storing membership information.
It is now necessary to create the connection string referenced in the previous step. Load the web.config file for the website into Visual Studio.

Locate the section marked <connectionStrings>. Add the following connection string information.

<connectionStrings>
  <remove name="LocalMySqlServer"/>
  <add name="LocalMySqlServer"
       connectionString="Datasource=localhost;Database=users;uid=root;pwd=password"
       providerName="MySql.Data.MySqlClient"/>
</connectionStrings>

The database specified is the one created in the first step. You could alternatively have used an existing database.

At this point build the solution to ensure no errors are present. This can be done by selecting Build, Build Solution from the main menu, or pressing F6.
ASP.NET supports the concept of locally and remotely authenticated users. With local authentication the user is validated using their Windows credentials when they attempt to access the website. This can be useful in an Intranet environment. With remote authentication, a user is prompted for their login details when accessing the website, and these credentials are checked against the membership information stored in a database server such as MySQL Server. You will now see how to choose this form of authentication.
Start the ASP.NET Website Administration Tool. This can be done quickly by clicking the small hammer/Earth icon in the Solution Explorer. You can also launch this tool by selecting Website and then ASP.NET Configuration from the main menu.
In the ASP.NET Website Administration Tool click the Security tab and do the following:
1. Click the User Authentication Type link.
2. Select the From the internet option. The website will now need to provide a form to allow the user to enter their login details. The details will be checked against membership information stored in the MySQL database.
You now need to specify the role and membership provider to be used. Click the Provider tab and do the following:
1. Click the Select a different provider for each feature (advanced) link.
2. For membership provider, select the MySQLMembershipProvider option and for role provider, select the MySQLRoleProvider option.
In Visual Studio, rebuild the solution by clicking Build and then Rebuild Solution from the main menu.

Check that the necessary schema has been created. This can be achieved using SHOW DATABASES; and SHOW TABLES; the mysql command interpreter.

mysql> SHOW DATABASES;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| test                |
| users               |
| world               |
+---------------------+
5 rows in set (0.01 sec)

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_users           |
+---------------------------+
| my_aspnet_applications    |
| my_aspnet_membership      |
| my_aspnet_profiles        |
| my_aspnet_roles           |
| my_aspnet_schemaversion   |
| my_aspnet_users           |
| my_aspnet_usersinroles    |
+---------------------------+
7 rows in set (0.00 sec)

Assuming all is present and correct, you can now create users and roles for your web application. The easiest way to do this is with the ASP.NET Website Administration Tool. However, many web applications contain their own modules for creating roles and users. For simplicity, the ASP.NET Website Administration Tool will be used in this tutorial.
In the ASP.NET Website Administration Tool, click the Security tab. Now that both the membership and role provider are enabled, you will see links for creating roles and users. Click the Create or Manage Roles link.
You can now enter the name of a new Role and click Add Role to create the new Role. Create new Roles as required.
Click the Back button.
Click the Create User link. You can now fill in information about the user to be created, and also allocate that user to one or more Roles.
Using the mysql command interpreter, you can check that your database has been correctly populated with the membership and role data.
```
mysql> SELECT * FROM my_aspnet_users;
```
```
mysql>  SELECT * FROM my_aspnet_roles;
```

In this tutorial, you have seen how to set up the Connector/NET membership and role providers for use in your ASP.NET web application.

PREV HOME UP NEXT