Advanced Membership Provider for MySQL

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Friday, November 9, 2012PrintSubscribe
Advanced Membership Provider for MySQL

Requirements

An advanced membership and role provider requires three tables.

One table keeps track of user information. This information includes the UserName, Email, and a Comment. Additional columns allow for implementation of a password question and answer in order to recover a forgotten password. When users are created, they can not be approved by default. Additional information is captured about the most recent login, activity, and change of password. When a user inputs an incorrect password past the limit, the user will become locked out. The number of failed attempts and most recent failed attempt will be stored.

Two tables are required to keep track of roles and associations of users with roles.

These are the advanced membership and role provider tables with “identity” primary keys.

Advanced membership provider for MySQL with integer 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),
    `Comment` text,
    PasswordQuestion varchar(256),
    PasswordAnswer varchar(128),
    IsApproved bit not null,
    LastActivityDate datetime not null,
    LastLoginDate datetime not null,
    LastPasswordChangedDate datetime not null,
    CreationDate datetime not null,
    IsLockedOut bit not null,
    LastLockedOutDate datetime not null,
    FailedPasswordAttemptCount int not null,
    FailedPasswordAttemptWindowStart datetime not null,
    FailedPasswordAnswerAttemptCount int not null,
    FailedPasswordAnswerAttemptWindowStart datetime not null
    );
    
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 advanced membership and role provider tables with “unique identifier” primary keys.

Advanced membership provider for MySQL 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),
    `Comment` text,
    PasswordQuestion varchar(256),
    PasswordAnswer varchar(128),
    IsApproved bit not null,
    LastActivityDate datetime not null,
    LastLoginDate datetime not null,
    LastPasswordChangedDate datetime not null,
    CreationDate datetime not null,
    IsLockedOut bit not null,
    LastLockedOutDate datetime not null,
    FailedPasswordAttemptCount int not null,
    FailedPasswordAttemptWindowStart datetime not null,
    FailedPasswordAnswerAttemptCount int not null,
    FailedPasswordAnswerAttemptWindowStart datetime not null
    );
    
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 membership and role provider 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
column [text] Comment = Comment
column [text] PasswordQuestion = PasswordQuestion
column [text] PasswordAnswer = PasswordAnswer
column [bool] IsApproved = IsApproved
column [date] LastActivityDate = LastActivityDate
column [date] LastLoginDate = LastLoginDate
column [date] LastPasswordChangedDate = LastPasswordChangedDate
column [date] CreationDate = CreationDate
column [bool] IsLockedOut = IsLockedOut
column [date] LastLockedOutDate = LastLockedOutDate
column [int] FailedPasswordAttemptCount = FailedPasswordAttemptCount
column [date] FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart
column [int] FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount
column [date] FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart

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.