Requirements
A basic membership provider requires a dedicated table to keep track of user names, passwords, and emails.
A role provider will require two tables to keep track of roles and associations of users with roles.
These are the basic membership and role provider tables with “identity” primary keys.
SQL:
create table Users (
UserID int not null AUTO_INCREMENT primary key,
UserName varchar(128) not null,
Password varchar(128) not null,
Email varchar(256)
);
create table Roles (
RoleID int not null AUTO_INCREMENT primary key,
RoleName varchar(128) not null
);
create table UserRoles (
UserID int not null,
RoleID int not null,
primary key (UserID, RoleID),
foreign key (UserID) references Users(UserID),
foreign key (RoleID) references Roles(RoleID)
);
These are the basic membership and role provider tables with “unique identifier” primary keys.
SQL:
create table Users (
UserID varchar(36) not null primary key default '',
UserName varchar(128) not null,
Password varchar(128) not null,
Email varchar(256)
);
create table Roles (
RoleID varchar(36) not null primary key default '',
RoleName varchar(128) not null
);
create table UserRoles (
UserID varchar(36) not null,
RoleID varchar(36) not null,
primary key (UserID, RoleID),
foreign key (UserID) references Users(UserID),
foreign key (RoleID) references Roles(RoleID)
);
delimiter $$
create trigger userinsert
before insert on Users
for each row
begin
set New.UserID = UUID();
end $$
create trigger roleinsert
before insert on Roles
for each row
begin
set New.RoleID = UUID();
end $$
Configuration
Use one of the scripts above to create the tables in your database.
Start Code On Time web application generator, select the project name on the start page, and choose Settings. Select Authentication and Membership.
Select “Enable custom membership and role providers” option and enter the following configuration settings.
table Users = Users
column [int|uiid] UserID = UserID
column [text] UserName = UserName
column [text] Password = Password
column [text] Email = Email
table Roles = Roles
column [int|uiid] RoleID = RoleID
column [text] RoleName = RoleName
table UserRoles = UserRoles
column [int|uiid] UserID = UserID
column [int|uiid] RoleID = RoleID
The configuration will guide the code generator in mapping the logical tables Users, Roles, and UserRoles to the physical tables in the database.
Generate the project to create the custom membership and role provider.