Code On Time: ASP.NET Membership

Blog

ASP.NET Membership

Thursday, November 8, 2012Print Subscribe

Advanced Membership Provider for SQL Server

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 identity not null primary key,
    UserName nvarchar(128) not null,
    Password nvarchar(128) not null,
    Email nvarchar(128),
    Comment ntext,
    PasswordQuestion nvarchar(256),
    PasswordAnswer nvarchar(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
)
go

create table Roles (
   RoleID int identity not null primary key,
   RoleName nvarchar(50)
)
go

create table UserRoles (
    UserID int not null,
    RoleID int not null,
    primary key(UserID, RoleID)
)
go

alter table UserRoles with check add constraint FK_UserRoles_Roles 
foreign key (RoleID) references Roles (RoleID)

alter table UserRoles with check add constraint FK_UserRoles_Users 
foreign key (UserID) references Users (UserID)

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

SQL:

create table Users (
    UserID uniqueidentifier not null default newid() primary key,
    UserName nvarchar(128) not null,
    Password nvarchar(128) not null,
    Email nvarchar(128),
    Comment ntext,
    PasswordQuestion nvarchar(256),
    PasswordAnswer nvarchar(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
)
go

create table Roles (
   RoleID uniqueidentifier not null default newid() primary key,
   RoleName nvarchar(50)
)
go

create table UserRoles (
    UserID uniqueidentifier not null,
    RoleID uniqueidentifier not null,
    primary key(UserID, RoleID)
)
go

alter table UserRoles with check add constraint FK_UserRoles_Roles 
foreign key (RoleID) references Roles (RoleID)

alter table UserRoles with check add constraint FK_UserRoles_Users 
foreign key (UserID) references Users (UserID)

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, Security, SQL Server, Tutorials, Web App Generator

Thursday, November 8, 2012Print Subscribe

Basic Membership Provider for SQL Server

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 identity not null primary key,
    UserName nvarchar(128) not null,
    Password nvarchar(128) not null,
    Email nvarchar(128)
)
go

create table Roles (
   RoleID int identity not null primary key,
   RoleName nvarchar(50)
)
go

create table UserRoles (
    UserID int not null,
    RoleID int not null,
    primary key(UserID, RoleID)
)
go

alter table UserRoles with check add constraint FK_UserRoles_Roles 
foreign key (RoleID) references Roles (RoleID)

alter table UserRoles with check add constraint FK_UserRoles_Users 
foreign key (UserID) references Users (UserID)

These are the basic membership and role provider tables with “unique identifier” primary keys.

SQL:

create table Users (
    UserID uniqueidentifier not null default newid() primary key,
    UserName nvarchar(128) not null,
    Password nvarchar(128) not null,
    Email nvarchar(128)
)
go

create table Roles (
   RoleID uniqueidentifier not null default newid() primary key,
   RoleName nvarchar(50)
)
go

create table UserRoles (
    UserID uniqueidentifier not null,
    RoleID uniqueidentifier not null,
    primary key(UserID, RoleID)
)
go

alter table UserRoles with check add constraint FK_UserRoles_Roles 
foreign key (RoleID) references Roles (RoleID)

alter table UserRoles with check add constraint FK_UserRoles_Users 
foreign key (UserID) references Users (UserID)

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, ASP.NET Membership, Designer, Security, SQL Server, Tutorials, Web Application Generator

Wednesday, November 7, 2012Print Subscribe

Comparing Fly-Over Login and Dedicated Login Page

If custom membership and role providers are enabled for a project then the application is configured to expose the page with the name Home to anonymous users. A link on a membership bar allows activating the fly-over Login window.

Developers can re-design the page Home by removing the standard user controls and adding the custom ones. Standard user controls display a site map and login instructions.

Additional pages can be exposed to end users if their Roles property is set to “?”.

For example, create a new page MySiteMap, set its Roles property to “?” (do not copy the double quotes). Activate User Controls tab in Project Explorer, right-click the user control node TableOfContents and choose “Copy”. “Paste” the user control on the new page. Right-click the page and choose “View in Browser”.

The new page will be visible to anonymous users along with Home page.

A dedicated login page can “greet” users when they access the web app.

Select the project on the start page of application generator, choose Settings, and proceed to Authentication and Membership. Choose Login Window section and enable a dedicated login page instead of a fly-over login window. Click Finish button.

Select Refresh action to ensure that the dedicated login page is included in the application design. Do not choose any data controllers in Refresh Dialog and simply proceed to refresh the project by clicking on Refresh button.

Generate the project. A dedicated page will be displayed asking users to sign in.

If you need to change the layout of the login page, then activate Project Designer. Select User Controls tab in Project Explorer. Right-click Login user control node and select “Edit in Visual Studio” option in context menu.

Visual Studio will start and display the definition of the user control. The user control is configured to be generated “First Time Only”. Any changes done in Visual Studio will persist between sessions of code generation.

Notice that the pages available to anonymous users are still accessible if the URL of the page is known.

For example, an anonymous user can access MySiteMap page created above by entering the URL directly in the address bar of a web browser without being required to sign in.

An attempt to access a protected page will redirect an anonymous user to the dedicated login page.

Labels: Application Factory, ASP.NET Membership, Security, User Interface, Web Application Generator