Advantage and the Provider Model

This post is one of a series of posts about the Advantage Providers for ASP.NET other posts in this series are listed below.

• Advantage and the Provider Model
• Advantage Membership Provider
• Advantage Role Provider
• Advantage SiteMap Provider
• Implementing the Advantage ASP.NET Providers

ASP.NET 2.0 introduced the Provider Model which provides a common interface between a service and a data source. This allows state information from web sessions to be stored in virtually any format. This is particularly useful when managing user accounts. Visual Studio 2005 includes a set of controls which provide login functionality and use the provider model to retrieve user information.

This model is open, much like ADO.NET, allowing developers to extend the model and write their own providers. Microsoft provides some great examples which make it easy to get started with creating a custom provider, see the Membership Provider example and Role Provider example on MSDN. Using these examples I was able to create some providers which use Advantage as the data store.

I’ll discuss the implementation of the Advantage Providers in future postings, however, I wanted to begin with discussing the database structure first. The provider database consists of six tables which contain the information about users, roles, profiles and applications. The database is configured to allow support for multiple applications. The structure is based on the structure used by SQL Server which is the default when using Visual Studio.

Applications Table

The applications table contains detalis about each application which uses the database to store user, role and sitemap information. This allows multiple web sites (applications) to use the same database for a central authentication database. When the first user is created by a specific web application a new ApplicationID is automatically generated.

Fields

• ApplicationID - Char(36) GUID value used as the unique identifier for each application
• ApplicationName - CiChar(50) Name of the appication
• Description - CiChar(255) Optional description field for additional information about the application

Indexes

• APPLICATIONID - PrimaryKey based on ApplicationID

Membership Table

The Membership Table stores information about individual users. The users are tied to an application allowing the same table to be used for unique users from multiple web sites. Passwords are stored in the table using one of three options: Plain Text - Password is stored unencrypted in the table, Encrypted - Password is stored encrypted and decrypted as needed, Hashed - Password is stored encrypted by a one-way hash and cannot be recovered.

Fields

• UserID - Char(36) GUID value used as the unique identifier for each user
• ApplicationID - Char(36) GUID value for application
• UserName - CiChar(50) Unique username per application
• Password - Char(50) Password for user
• PasswordFormat - CiChar(50) Password storage method
• Email - CiChar(255) User's e-mail address
• PasswordQuestion - CiChar(255) Question used for password recovery
• PasswordAnswer - Char(50) Answer to password recovery question
• IsApproved - Logical Optional used to force manual verification of users
• IsLockedOut - Logical Determines locked out users
• CreationDate - TimeStamp Date and time user was created in the table
• LastLoginDate - TimeStamp Date and time user last successfully logged in
• LastPasswordChangedDate - TimeStamp Date and time user last changed their password
• LastLockedOutDate - TimeStamp Date and time user was last locked out
• FailedPasswordAttemptCount - Integer Number of failed password attempts
• FailedPasswordAttemptWindowStart - TimeStamp Date and time of first failed password attempt
• FailedPasswordAnswerAttemptCount - Integer Number of failed password recovery answer attempts
• FailedPasswordAnswerAttemptWindowStart - TimeStamp Date and time of first failed password recovery answer attempt
• Comment - Memo Allows for additional information to be stored about the user
• LastActivityDate - TimeStamp Date and time of last account activity

Indexes

• USERID - PrimaryKey based on UserID
• USERNAME - Used for locating users by username
• EMAIL - Used for looking up users by e-mail address
• APPLICATIONID - Foreign Key for Applications Table
• PASSWORD - Used for password verification

Profile Table

The profile table can be used to store additional information about users. These properties can be used to fill in default values or store other preferences.

Fields

• UserID - Char(36) stores a UserID from the Membership Table
• PropertyNames - Char(255) stores the property name
• PropertyValuesString - Char(255) stores the property value as a string
• PropertyValuesBinary - Binary stores property information as binary
• LastUpdateDate - Timestamp stores date and time of last update

Indexes

• USERID - PrimaryKey based on UserID

Roles Table

The Roles Table stores information about the roles specific to each application. These roles can be used to restrict access to areas of the web site through the use of Forms authentication. As with the Members Table roles are based on applications allowing the table to be used for multiple web sites as needed.

Fields

• RoleID - Char(36) GUID used as a unique identifier for each role
• ApplicationID - Char(36) foreign key value from Applications Table
• RoleName - CiChar(50) Name of the role
• Description - CiChar(255) Description for the role

Indexes

• ROLEID - PrimaryKey based on RoleID
• APPLICATIONID - ForeignKey ApplicationID from the Applications Table
• ROLENAME - Allows for optimized searches of role names

SiteMap Table

The SiteMap stores information about the pages in the web site. Each entry contains a unique integer value. Pages are associated with each other using the parent field.

Fields

• ID - Integer Unique value for each entry
• Title - CiChar(50) Page title
• URL - CiChar(100) Relative path to the page
• Description - CiChar(512) Description for the page
• Roles - CiChar(512) List of roles that can access the page
• Parent - Integer ID of parent page, used for sorting pages

Indexes

• ID - PrimaryKey based on ID
• PARENT - Allows for optimized searches and ordering of pages

UsersInRoles Table

The UsersInRoles Table creates a many-to-many relationship between the Membership Table and the Roles Table. This table is used to determine which roles individual users are members of for security purposes.

Fields

• UserID - Char(36) Unique UserID from Membership table
• RoleID - Char(36) Unique RoleID from Roles table

Indexes

• USERID - ForeignKey for Membership UserID
• ROLEID - ForeignKey for Roles RoleID

A rather long and detailed post I admit but I wanted to have the data structure available so you can refer to it as I discuss implementation of various providers which use Advantage as the data store.