ASP.NET Membership

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
ASP.NET Membership
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.

Friday, November 9, 2012PrintSubscribe
Membership & Role Providers, Visual Studio 2012, Navigation Button

Code On Time release 7.0.4.0 includes major new features and numerous enhancements.

Custom Membership and Role Providers can be generated straight from application database tables in Unlimited edition. Examples of Minimal, Basic, and Advanced configurations for Microsoft SQL Server are included. Examples of configuration for other supported databases will be provided as well in the future. Developers can quickly implement an integrated user manager based on custom security tables.

The new release also supports Microsoft Visual 2012 for all types of projects. Migration of projects is simple.

  • Install Visual Studio 2012 or Visual Studio Express 2012 for Web on your computer.
  • If you are working on a Web Site Factory project, then select the project name on the start page of application generator and choose Open. Move one level up in the hierarchy of folders in Windows Explorer and delete the files that have the same name as your project with “*.sln” and “*.suo” extensions . Generate the application.
  • If you are working on a project of a different type, then generate the application after installation of Visual Studio 2012.

Microsoft has also released ASP.NET 4.5. At this time we do not support this version of ASP.NET framework. We expect support for ASP.NET 4.5 to be available within 45 days. Note that ASP.NET 4.5 will be supported only in Web Site Factory and Web App Factory projects. Azure Factory will be upgraded to support ASP.NET 4.5 when Microsoft officially deploys ASP.NET 4.5 to Windows Azure servers. DotNetNuke Factory and SharePoint Factory are still limited to ASP.NET 3.5 – this is the limitation of these products. The future upgrades to DNN and SharePoint by their respective vendors will be reflected in Code On Time projects.

New menu presentation style called “Navigation Button” is now available in Premium and Unlimited editions.

New 'Navigation Button' menu style

The following enhancements and bug fixes are also included in this release:

  • Generated web applications integrate jQuery 1.8.2 and jQuery UI 1.9.0
     
  • Azure Factory supports Windows Azure SDK 1.7 August 2012
     
  • Thank you, Alon Elbaz, for contributing Hebrew localization of the client library.
     
  • Buttons OK and Cancel are displayed on the right side of tool bar in Project Designer when an existing project configuration element is selected. This significantly reduces amount of scrolling required when working with properties of elements.

Buttons OK and Cancel are displayed on the Project Designer tool bar when working properties of existing project configuraiton elements
 

Text property of Command is displayed in a wide text box in Project Designer.

  • Data property of Custom and SQL action is display in a wide text box in Project Designer.
     
  • Script property of Business Rules is displayed in a wide text box in Project Designer.
     
  • Dynamic access control rules will not be listed twice in the "WHERE" clause when the first page of data is selected.
     
  • Selection of a check box in a grid view with multiple-selection will make the selected row "current".
     
  • If a higher-level field in a cascading lookup dependency is changed then the fields with lower-level dependency are cleared.
  • Cascading lookups work correctly with all lookup styles.
     
  • Client library automatically sets focus on a data sheet view for Select, Edit, New, and Duplicate commands.
     
  • Static action confirmations with references to the field names will not perform URL encoding on values.
     
  • Grid views correctly identify a selected row when a key is known. This guarantees correct display of"dynamic" actions that depend on When Client Script property. This property is evaluated against the selected data row.
     
  • Business rule processor will not raise an exception when data access objects are executed for controllers with business rules.
     
  • Deployment files Deploy.bat and Retract.bat are created in ANSI encoding when a SharePoint Factory project is published.
     
  • Views can be converted to Form or Grid in 'Pages' hierarchy of Project Explorer.
     
  • Batch Edit correctly processes all styles of lookups and retains selection of fields when 'Calculate' event is raised.
     
  • The application framework will reset the Canceled flag of the result after each iteration over SelectedValues.
     
  • Fixed the incorrect export of Date Time fields when Export to Spreadsheet action is selected. Export will process both "c" and "C" format strings for currency values.
     
  • Context fields are passed correctly to the server for the fields with all styles of lookups.
     
  • Setting FormatOnClient property to false will work only if a Data Format String property is also specified.
  • Blob processor applies URL encoding to file names in the response header.
     
  • Lookup window formats the selected value upon selection. This eliminates error messages when fields with a non-blank Data Format String are selected.
     
  • Focus remains on the "new" row in a data sheet if a user clicks on a cell in a different row. The focus shifts to the column of the clicked cell.
     
  • Clearing of lookup field values (any style) in a data sheet will correctly preserve the "empty" state of a field while user moves between cells.
     
  • Data sheet view will correctly process hidden default values when the very first row is added to the view.
     
  • ApplicationServices.cs(vb) file is not referencing security namespace if membership is not enabled.
     
  • Fixed incorrect passing of contextFilter to field-level auto-complete boxes.
     
  • Search and Reset buttons on the search bar now have corresponding CSS classes assigned to them.
     
  • Enter key will close field search filters.
     
  • Class ViewPage correctly processes DateTimeOffset columns when converting a page of data to a DataTable instance.
     
  • Establishing a master-detail relationship between data views in the same container will correctly set "Auto Hide" property of details to "Self".
     
  • "External" parameters are not marked as "InputOutput" when configured for SQL Business Rules.
     
  • Utility class SqlStatement supports logging of exceptions in Windows event log. This feature is used by custom membership providers available in Unlimited edition.
     
  • Virtualization NodeSet plugin Select_TYPE_BusinessRules correctly enumerated rules of a data controller.
     
  • External Filter is passed to the data controller from the client when executing AutoComplete requests.
     
  • Option "Edit Handler in Visual Studio" is displayed in context menu of Business Rules node of a data controller.
     
  • Enhanced processing of parameters in SQL Formula of fields.
Thursday, November 8, 2012PrintSubscribe
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.

Advanced membership and role provider using 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.

Advanced membership and role provider using 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.