Code On Time: Application Factory

Blog

Application Factory

Tuesday, November 27, 2012Print Subscribe

Advanced User Manager for Custom Membership and Role Provider: Adding “Roles” Tab

Let’s add a “Roles” tab on the Users page to allow management of roles. When a role is selected, users corresponding to the role will be displayed below. In order to create this “implied” master-detail relationship, a “UsersInRole” view must be created that will use a custom command to extract the RoleID.

Creating “Users In Role” View

Right-click on Users / Views / grid1 view node, and press Copy.

Right-click on Users / Views node, and press Paste. A duplicate of the view will be created.

Double-click on Users / Views / v100 node. Next to the Command property, select the New Command icon.

Assign these values.

Property	Value
Id	command2
Text	select "Users"."UserID" "UserID" ,"Users"."UserName" "UserName" ,"Users"."Password" "Password" ,"Users"."Email" "Email" ,"Users"."Comment" "Comment" ,"Users"."PasswordQuestion" "PasswordQuestion" ,"Users"."PasswordAnswer" "PasswordAnswer" ,"Users"."IsApproved" "IsApproved" ,"Users"."LastActivityDate" "LastActivityDate" ,"Users"."LastLoginDate" "LastLoginDate" ,"Users"."LastPasswordChangedDate" "LastPasswordChangedDate" ,"Users"."CreationDate" "CreationDate" ,"Users"."IsLockedOut" "IsLockedOut" ,"Users"."LastLockedOutDate" "LastLockedOutDate" ,"Users"."FailedPasswordAttemptCount" "FailedPasswordAttemptCount" ,"Users"."FailedPasswordAttemptWindowStart" "FailedPasswordAttemptWindowStart" ,"Users"."FailedPasswordAnswerAttemptCount" "FailedPasswordAnswerAttemptCount" ,"Users"."FailedPasswordAnswerAttemptWindowStart" "FailedPasswordAnswerAttemptWindowStart" ,UserRoles.RoleID RoleID from "dbo"."Users" "Users" left join UserRoles on Users.UserID = UserRoles.UserID
Type	Text
Event	Select

Press OK to create the command and insert it into the property. Also make the following changes:

Property	Value
Label	Users In Role
Show In View Selector	false

Press OK to save. In the Project Explorer, right-click on Users / Views / v100 and press Rename.

Change the name to “UsersInRole” and press Enter on your keyboard to save.

Adding “RoleID” Field

Right-click on Users / Fields node, and press New Field.

Assign the following properties:

Property	Value
Name	RoleID
Type	Int32
The field is hidden from users.	true
Label	Role ID
Allow Query-by-Example	true

Press OK to save the field. Drop Users / Fields / RoleID field node onto Users / Views / UsersInRole to create a data field and append it to the view.

Adding Roles Tab to Users Page

Switch to the Controllers tab in the Project Explorer. While holding Ctrl key, click on Roles and Users controller nodes in that order. Right-click and press Copy.

Switch back to the Pages tab. Right-click on Users / container1 node, and press Paste. The controllers will be instantiated as views in the container.

Double-click on Users / container1 / view1 (Users) data view node.

Change the following properties.

Property	New Value
Activator	Tab
Text	Users
Search By First Letter	true

Press OK to save. Double-click on Users / container1 / view2 (Roles) node.

Make the following changes.

Property	New Value
Activator	Tab
Text	Roles

Save the data view. Double-click on Users / container1 / view3 (Users) node.

Make these changes:

Property	New Value
View	UsersInRole
Activator	Tab
Text	Roles

Save the data view. Expand Users / container1 / view3 (Users, UsersInRole) / UsersInRole node. Drop RoleID data field node onto Users / container1 / view2 (Roles) to establish a master-detail relationship.

Labels: Application Factory, Designer, Security, Tutorials, User Interface, Web Application Generator

Friday, November 9, 2012Print Subscribe

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.

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.

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.

Labels: Application Factory, ASP.NET Membership, Designer, MySQL, Security, Tutorials, Web Application Generator

Friday, November 9, 2012Print Subscribe

Basic Membership Provider for MySQL

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.

Labels: Application Factory, MySQL, Security, Tutorials, Web Application Generator