One of the first things that any database driven website seems to need is a way to manage users. .NET 2.0 has a model of Providers that includes one for managing users. I thought I would try to create a MembershipProvider using Firebird Embedded as the database. To try to make it a bit more generic implementation, I decided to use the Data Access Block portion of the Microsoft Enterprise Library.
I think I made some slight modifications to code I'd written for earlier projects, so I will include it here for reference. It includes Firebird specific classes for the Database and CommandWrapper, as well as a couple of helper classes for Data Definition and Data Access.
BALFirebirdEmbeddedDatabase.zip (9.66 KB)
I started with a sample ODBC Membership Provider. After working with the sample some, I decided to break it up more. I did not like having to type in the column names so many times, and I did not like having all the database queries spread out all over the place in the application. Instead of having the Membership Provider I was writing access the database directly, or be a consumer of the Microsoft Enterprise Library Data Access Block, I broke the it down some more so that the Membership Provider only has to access a pretty generic Data Access Class.
-> Data Definition (Column Names, DataTable)
MembershipProvider -> Data Access Class -
| -> Data Manipulation (Parameterized Queries)
V
Microsoft Enterprise Library Data Access Block
|
V
Firebird Embedded Database
The ODBC Membership Provider listed out the SQL Statement used to create the table as a comment. I wanted to have this definition in the code, so that I could use the definition instead of all the column names (which I figured I was prone to mistype). Here is the data definition class:
using System;
using System.Data;
namespace BALConsultingNet.EnterpriseLibrary.Providers.Data
{
/// <summary>
/// Class that holds the column names and a DataTable so the data can
/// be accessed in a consistent manner.
/// </summary>
public class BALMembershipProviderDataDefinition
{
public string id { get { return "id"; } }
public string Username { get { return "Username"; } }
public string ApplicationName { get { return "ApplicationName"; } }
public string Email { get { return "Email"; } }
public string Comment { get { return "Comment"; } }
public string Password { get { return "Password"; } }
public string PasswordQuestion { get { return "PasswordQuestion"; } }
public string PasswordAnswer { get { return "PasswordAnswer"; } }
public string IsApproved { get { return "IsApproved"; } }
public string LastActivityDate { get { return "LastActivityDate"; } }
public string LastLoginDate { get { return "LastLoginDate"; } }
public string LastPasswordChangedDate { get { return "LastPasswordChangedDate"; } }
public string CreationDate { get { return "CreationDate"; } }
public string IsOnLine { get { return "IsOnLine"; } }
public string IsLockedOut { get { return "IsLockedOut"; } }
public string LastLockedOutDate { get { return "LastLockedOutDate"; } }
public string FailedPasswordCount { get { return "FailedPasswordCount"; } }
public string FailedPasswordStart { get { return "FailedPasswordStart"; } }
public string FailedPasswordAnswerCount { get { return "FailedPasswordAnswerCount"; } }
public string FailedPasswordAnswerStart { get { return "FailedPasswordAnswerStart"; } }
public DataTable oDataTable;
public BALMembershipProviderDataDefinition()
{
oDataTable = new DataTable();
oDataTable.TableName = "BALUsers";
//Define a column in the table (name it "id" and have it be an Integer)
DataColumn loColumn = new DataColumn(id, DbType.Int32.GetType());
loColumn.AllowDBNull = false;
loColumn.AutoIncrement = true;
oDataTable.Columns.Add(loColumn);
UniqueConstraint loPrimaryKey = new UniqueConstraint("PK", loColumn);
oDataTable.Constraints.Add(loPrimaryKey);
oDataTable.Columns.Add(Username, Type.GetType("System.String"));
oDataTable.Columns.Add(ApplicationName, Type.GetType("System.String"));
oDataTable.Columns.Add(Email, Type.GetType("System.String"));
oDataTable.Columns.Add(Comment, Type.GetType("System.String"));
oDataTable.Columns.Add(Password, Type.GetType("System.String"));
oDataTable.Columns.Add(PasswordQuestion, Type.GetType("System.String"));
oDataTable.Columns.Add(PasswordAnswer, Type.GetType("System.String"));
oDataTable.Columns.Add(IsApproved, Type.GetType("System.Int16"));
oDataTable.Columns.Add(LastActivityDate, Type.GetType("System.DateTime"));
oDataTable.Columns.Add(LastLoginDate, Type.GetType("System.DateTime"));
oDataTable.Columns.Add(LastPasswordChangedDate, Type.GetType("System.DateTime"));
oDataTable.Columns.Add(CreationDate, Type.GetType("System.DateTime"));
oDataTable.Columns.Add(IsOnLine, Type.GetType("System.Int16"));
oDataTable.Columns.Add(IsLockedOut, Type.GetType("System.Int16"));
oDataTable.Columns.Add(LastLockedOutDate, Type.GetType("System.DateTime"));
oDataTable.Columns.Add(FailedPasswordCount, Type.GetType("System.Int32"));
oDataTable.Columns.Add(FailedPasswordStart, Type.GetType("System.DateTime"));
oDataTable.Columns.Add(FailedPasswordAnswerCount, Type.GetType("System.Int32"));
oDataTable.Columns.Add(FailedPasswordAnswerStart, Type.GetType("System.DateTime"));
}
}
}
The data manipulation class is mostly just a bunch of parameterized queries, so I won't fill up the screen with it.
BALConsultingNet.EnterpriseLibrary.Providers.BALMembershipProviderDataManipulation.cs.txt (15.23 KB)
The data access class mostly just mimics some commands from the Data Access Block.
using System;
using System.Collections.Specialized;
using System.Configuration.Provider;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using BALConsultingNet.EnterpriseLibrary.Data.Firebird;
namespace BALConsultingNet.EnterpriseLibrary.Providers.Data
{
public class BALMembershipProviderData
{
public BALMembershipProviderDataDefinition oDD;
public BALMembershipProviderDataManipulation oDM;
private string _sInstanceName;
private Database _oDatabase;
public BALMembershipProviderData(string lsInstanceName)
{
_sInstanceName = lsInstanceName;
oDD = new BALMembershipProviderDataDefinition();
try
{
_oDatabase = DatabaseFactory.CreateDatabase(this._sInstanceName);
}
catch (Exception loE)
{
throw new ProviderException("Cannot create Enterprise Database using Instance Name of " + _sInstanceName + "[" + loE.ToString() + "]");
}
if (_oDatabase.GetType().ToString() == "BALConsultingNet.EnterpriseLibrary.Data.Firebird.DatabaseFirebird")
{
DatabaseFirebird loD = (DatabaseFirebird)_oDatabase;
loD.CreateDatabase();
//throw new ProviderException("DatabaseCreated");
loD.CreateTable(oDD.oDataTable);
//throw new ProviderException("TableCreated");
}
oDM = new BALMembershipProviderDataManipulation(oDD);
}
public DBCommandWrapper GetCommandWrapper(string lsQuery)
{
if (lsQuery.StartsWith("SELECT") || lsQuery.StartsWith("UPDATE") || lsQuery.StartsWith("DELETE"))
{
return _oDatabase.GetSqlStringCommandWrapper(lsQuery);
}
else
{
return _oDatabase.GetStoredProcCommandWrapper(lsQuery);
}
}
public object ExecuteScalar(string lsQuery, ListDictionary loParameters)
{
DBCommandWrapper loCommand = GetCommand(lsQuery, loParameters);
return _oDatabase.ExecuteScalar(loCommand);
}
public object ExecuteScalar(string lsQuery, ListDictionary loParameters, string lsOutParam)
{
DBCommandWrapper loCommand = GetCommand(lsQuery, loParameters, lsOutParam);
_oDatabase.ExecuteNonQuery(loCommand);
return loCommand.GetParameterValue(lsOutParam);
}
public void ExecuteNonQuery(string lsQuery, ListDictionary loParameters)
{
DBCommandWrapper loCommand = GetCommand(lsQuery, loParameters);
_oDatabase.ExecuteNonQuery(loCommand);
}
public IDataReader ExecuteReader(string lsQuery, ListDictionary loParameters)
{
DBCommandWrapper loCommand = GetCommand(lsQuery, loParameters);
return _oDatabase.ExecuteReader(loCommand);
}
private DBCommandWrapper GetCommand(string lsQuery, ListDictionary loParameters)
{
DBCommandWrapper loCommand = GetCommandWrapper(lsQuery);
foreach (object loKey in loParameters.Keys)
{
loCommand.AddInParameter(loKey.ToString(), GetDbType(loParameters[loKey]), loParameters[loKey]);
}
return loCommand;
}
private DBCommandWrapper GetCommand(string lsQuery, ListDictionary loParameters,string lsOutParam)
{
DBCommandWrapper loCommand = GetCommandWrapper(lsQuery);
foreach (object loKey in loParameters.Keys)
{
try
{
if (loKey.ToString() == lsOutParam)
{
loCommand.AddOutParameter(loKey.ToString(), GetDbType(loParameters[loKey]), 1);
}
else
{
loCommand.AddInParameter(loKey.ToString(), GetDbType(loParameters[loKey]), loParameters[loKey]);
}
}
catch (Exception loE)
{
throw new ProviderException("Error getting type of " + loKey.ToString() + "[" + loE.ToString() + "]");
}
}
return loCommand;
}
private DbType GetDbType(object loValue)
{
if (Type.GetType("System.String") == loValue.GetType()) return DbType.String;
if (Type.GetType("System.Int16") == loValue.GetType()) return DbType.Int16;
if (Type.GetType("System.Int32") == loValue.GetType()) return DbType.Int32;
if (Type.GetType("System.Int64") == loValue.GetType()) return DbType.Int64;
if (Type.GetType("System.Single") == loValue.GetType()) return DbType.Single;
if (Type.GetType("System.Double") == loValue.GetType()) return DbType.Double;
if (Type.GetType("System.DateTime") == loValue.GetType()) return DbType.DateTime;
if (Type.GetType("System.Guid") == loValue.GetType()) return DbType.Guid;
return DbType.String;
}
}
}
Finally, the actual Membership Provider class that uses the others.
using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Web.Configuration;
using System.Web.Security;
using Microsoft.Practices.EnterpriseLibrary.Data;
using BALConsultingNet.EnterpriseLibrary.Data.Firebird;
using BALConsultingNet.EnterpriseLibrary.Providers.Data;
namespace BALConsultingNet.EnterpriseLibrary.Providers
{
public sealed class BALMembershipProvider : MembershipProvider
{
//
// Global connection string, generated password length, generic exception message, event log info.
//
private int _nNewPasswordLength = 8;
private string _sInstanceName;
public BALMembershipProviderData oData;
//
// Used when determining encryption key values.
//
private MachineKeySection _oMachineKey;
//
// If false, exceptions are thrown to the caller. If true,
// exceptions are written to the event log.
//
private bool _bWriteExceptionsToEventLog;
public bool WriteExceptionsToEventLog
{
get { return _bWriteExceptionsToEventLog; }
set { _bWriteExceptionsToEventLog = value; }
}
//
// System.Configuration.Provider.ProviderBase.Initialize Method
//
public override void Initialize(string lsName, NameValueCollection loConfig)
{
// Verify that the provider has sufficient trust to operate. The built-in
// providers tend to be less stringent here, simply ensuring that
// they're running with at least low trust.
// Verify that config isn't null
if (loConfig == null)
throw new ArgumentNullException("loConfig");
// Assign "name" a default value if it currently has no value
// or is an empty string
if (lsName == null || lsName.Length == 0)
lsName = "BALMembershipProvider";
// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (String.IsNullOrEmpty(loConfig["description"]))
{
loConfig.Remove("description");
loConfig.Add("description", "BAL Consulting MembershipProvider using BAL Data Access Layer");
}
// Call the base class's Initialize method
base.Initialize(lsName, loConfig);
_sApplicationName = GetConfigValue(loConfig["applicationName"],
System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
loConfig.Remove("applicationName");
_nMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(loConfig["maxInvalidPasswordAttempts"], "5"));
loConfig.Remove("maxInvalidPasswordAttempts");
_nPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(loConfig["passwordAttemptWindow"], "10"));
loConfig.Remove("passwordAttemptWindow");
_nMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(loConfig["minRequiredNonAlphanumericCharacters"], "1"));
loConfig.Remove("minRequiredNonAlphanumericCharacters");
_nMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(loConfig["minRequiredPasswordLength"], "7"));
loConfig.Remove("minRequiredPasswordLength");
_sPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(loConfig["passwordStrengthRegularExpression"], ""));
loConfig.Remove("passwordStrengthRegularExpression");
_bEnablePasswordReset = Convert.ToBoolean(GetConfigValue(loConfig["enablePasswordReset"], "true"));
loConfig.Remove("enablePasswordReset");
_bEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(loConfig["enablePasswordRetrieval"], "true"));
loConfig.Remove("enablePasswordRetrieval");
_bRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(loConfig["requiresQuestionAndAnswer"], "false"));
loConfig.Remove("requiresQuestionAndAnswer");
_bRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(loConfig["requiresUniqueEmail"], "true"));
loConfig.Remove("requiresUniqueEmail");
string lsFormat = loConfig["passwordFormat"];
loConfig.Remove("passwordFormat");
if (lsFormat == null)
{
lsFormat = "Clear";
}
switch (lsFormat)
{
case "Hashed":
_oPasswordFormat = MembershipPasswordFormat.Hashed;
break;
case "Encrypted":
_oPasswordFormat = MembershipPasswordFormat.Encrypted;
break;
case "Clear":
_oPasswordFormat = MembershipPasswordFormat.Clear;
break;
default:
throw new ProviderException("Password format of " + lsFormat + " not supported.");
}
_sInstanceName = loConfig["instanceName"];
if (_sInstanceName == null) _sInstanceName = "MembershipProvider";
loConfig.Remove("instanceName");
// Get encryption and decryption key information from the configuration.
Configuration loConfiguration =
WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
_oMachineKey = (MachineKeySection)loConfiguration.GetSection("system.web/machineKey");
if (_oMachineKey.ValidationKey.Contains("AutoGenerate"))
if (PasswordFormat != MembershipPasswordFormat.Clear)
throw new ProviderException("Hashed or Encrypted passwords " +
"are not supported with auto-generated keys.");
// Throw an exception if unrecognized attributes remain
if (loConfig.Count > 0)
{
string lsKey = loConfig.GetKey(0);
if (!String.IsNullOrEmpty(lsKey))
throw new ProviderException("Unrecognized attribute: " + lsKey);
}
//Instantiate the data provider
oData = new BALMembershipProviderData(this._sInstanceName);
}
//
// A helper function to retrieve config values from the configuration file.
//
private string GetConfigValue(string lsConfigValue, string lsDefaultValue)
{
if (String.IsNullOrEmpty(lsConfigValue))
return lsDefaultValue;
return lsConfigValue;
}
//
// System.Web.Security.MembershipProvider properties.
//
private string _sApplicationName;
private bool _bEnablePasswordReset;
private bool _bEnablePasswordRetrieval;
private bool _bRequiresQuestionAndAnswer;
private bool _bRequiresUniqueEmail;
private int _nMaxInvalidPasswordAttempts;
private int _nPasswordAttemptWindow;
private MembershipPasswordFormat _oPasswordFormat;
public override string ApplicationName
{
get { return _sApplicationName; }
set { _sApplicationName = value; }
}
public override bool EnablePasswordReset
{
get { return _bEnablePasswordReset; }
}
public override bool EnablePasswordRetrieval
{
get { return _bEnablePasswordRetrieval; }
}
public override bool RequiresQuestionAndAnswer
{
get { return _bRequiresQuestionAndAnswer; }
}
public override bool RequiresUniqueEmail
{
get { return _bRequiresUniqueEmail; }
}
public override int MaxInvalidPasswordAttempts
{
get { return _nMaxInvalidPasswordAttempts; }
}
public override int PasswordAttemptWindow
{
get { return _nPasswordAttemptWindow; }
}
public override MembershipPasswordFormat PasswordFormat
{
get { return _oPasswordFormat; }
}
private int _nMinRequiredNonAlphanumericCharacters;
public override int MinRequiredNonAlphanumericCharacters
{
get { return _nMinRequiredNonAlphanumericCharacters; }
}
private int _nMinRequiredPasswordLength;
public override int MinRequiredPasswordLength
{
get { return _nMinRequiredPasswordLength; }
}
private string _sPasswordStrengthRegularExpression;
public override string PasswordStrengthRegularExpression
{
get { return _sPasswordStrengthRegularExpression; }
}
//
// System.Web.Security.MembershipProvider methods.
//
//
// MembershipProvider.ChangePassword
//
public override bool ChangePassword(string lsUsername, string lsPassword, string lsPasswordNew)
{
if (!ValidateUser(lsUsername, lsPassword)) return false;
ValidatePasswordEventArgs loPasswordEventArgs =
new ValidatePasswordEventArgs(lsUsername, lsPasswordNew, true);
OnValidatingPassword(loPasswordEventArgs);
if (loPasswordEventArgs.Cancel) {
if (loPasswordEventArgs.FailureInformation != null)
{
throw loPasswordEventArgs.FailureInformation;
}
else
{
throw new MembershipPasswordException("Change password canceled due to new password validation failure.");
}
}
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.Password, EncodePassword(lsPasswordNew));
loParameters.Add(oData.oDD.LastPasswordChangedDate, DateTime.Now);
oData.ExecuteNonQuery(oData.oDM.UpdatePassword, loParameters);
return true;
}
//
// MembershipProvider.ChangePasswordQuestionAndAnswer
//
public override bool ChangePasswordQuestionAndAnswer(string lsUsername,
string lsPassword,
string lsPasswordQuestionNew,
string lsPasswordAnswerNew)
{
if (!ValidateUser(lsUsername, lsPassword)) return false;
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.PasswordQuestion, lsPasswordQuestionNew);
loParameters.Add(oData.oDD.PasswordAnswer, lsPasswordAnswerNew);
oData.ExecuteNonQuery(oData.oDM.UpdatePasswordQuestionAndAnswer, loParameters);
return true;
}
//
// MembershipProvider.CreateUser
//
public override MembershipUser CreateUser(string lsUsername,
string lsPassword,
string lsEmail,
string lsPasswordQuestion,
string lsPasswordAnswer,
bool lbIsApproved,
object loProviderUserKey,
out MembershipCreateStatus loStatus)
{
ValidatePasswordEventArgs loEventArgs =
new ValidatePasswordEventArgs(lsUsername, lsPassword, true);
OnValidatingPassword(loEventArgs);
if (loEventArgs.Cancel)
{
loStatus = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && GetUserNameByEmail(lsEmail) != "")
{
loStatus = MembershipCreateStatus.DuplicateEmail;
return null;
}
MembershipUser loUser = GetUser(lsUsername, false);
if (loUser == null)
{
DateTime ldCreateDate = DateTime.Now;
if (lsPasswordQuestion == null) lsPasswordQuestion = "";
if (lsPasswordAnswer == null) lsPasswordAnswer = "";
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.Email, lsEmail);
loParameters.Add(oData.oDD.Comment, "");
loParameters.Add(oData.oDD.Password, EncodePassword(lsPassword));
loParameters.Add(oData.oDD.PasswordQuestion, lsPasswordQuestion);
loParameters.Add(oData.oDD.PasswordAnswer, lsPasswordAnswer);
loParameters.Add(oData.oDD.IsApproved, Convert.ToInt16(lbIsApproved));
loParameters.Add(oData.oDD.LastActivityDate, ldCreateDate);
loParameters.Add(oData.oDD.LastLoginDate, ldCreateDate);
loParameters.Add(oData.oDD.LastPasswordChangedDate, ldCreateDate);
loParameters.Add(oData.oDD.CreationDate, ldCreateDate);
loParameters.Add(oData.oDD.IsOnLine, Convert.ToInt16(false));
loParameters.Add(oData.oDD.IsLockedOut, Convert.ToInt16(false));
loParameters.Add(oData.oDD.LastLockedOutDate, ldCreateDate);
loParameters.Add(oData.oDD.FailedPasswordCount, 0);
loParameters.Add(oData.oDD.FailedPasswordStart, ldCreateDate);
loParameters.Add(oData.oDD.FailedPasswordAnswerCount, 0);
loParameters.Add(oData.oDD.FailedPasswordAnswerStart, ldCreateDate);
loParameters.Add(oData.oDD.id, 1);
int lnID = (int)oData.ExecuteScalar(oData.oDM.InsertUser, loParameters,"id");
if (lnID > 0)
{
loStatus = MembershipCreateStatus.Success;
}
else
{
loStatus = MembershipCreateStatus.UserRejected;
}
return GetUser(lsUsername, false);
}
else
{
loStatus = MembershipCreateStatus.DuplicateUserName;
}
return null;
}
//
// MembershipProvider.DeleteUser
//
public override bool DeleteUser(string lsUsername, bool lbDeleteAllRelatedData)
{
if (lbDeleteAllRelatedData)
{
// Process commands to delete all data for the user in the database.
}
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
oData.ExecuteNonQuery(oData.oDM.DeleteUser, loParameters);
return true;
}
//
// MembershipProvider.GetAllUsers
//
public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
ListDictionary loParametersApplicationName = new ListDictionary();
loParametersApplicationName.Add(oData.oDD.ApplicationName, _sApplicationName);
int lnUserCount = (int)oData.ExecuteScalar(oData.oDM.SelectUserCount, loParametersApplicationName);
totalRecords = lnUserCount;
MembershipUserCollection loUserCollection = new MembershipUserCollection();
if (lnUserCount <= 0) return loUserCollection;
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectUserList,loParametersApplicationName);
int lnCurrent = 0;
int lnStart = pageSize * pageIndex;
int lnEnd = lnStart + pageSize - 1;
while (loReader.Read())
{
if (lnCurrent >= lnStart)
{
MembershipUser loUser = GetUserFromReader(loReader);
loUserCollection.Add(loUser);
}
if (lnCurrent >= lnEnd) { loReader.Close(); }
lnCurrent++;
}
return loUserCollection;
}
//
// MembershipProvider.GetNumberOfUsersOnline
//
public override int GetNumberOfUsersOnline()
{
TimeSpan ldOnlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
DateTime ldCompareTime = DateTime.Now.Subtract(ldOnlineSpan);
ListDictionary loParamaters = new ListDictionary();
loParamaters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParamaters.Add(oData.oDD.LastActivityDate, ldCompareTime);
int lnNumOnline = 0;
lnNumOnline = (int)oData.ExecuteScalar(oData.oDM.SelectUserOnlineCount, loParamaters);
return lnNumOnline;
}
//
// MembershipProvider.GetPassword
//
public override string GetPassword(string lsUsername, string lsAnswer)
{
if (!EnablePasswordRetrieval)
{
throw new ProviderException("Password Retrieval Not Enabled.");
}
if (PasswordFormat == MembershipPasswordFormat.Hashed)
{
throw new ProviderException("Cannot retrieve Hashed passwords.");
}
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.Username, lsUsername);
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectUserPassword, loParameters);
string lsPassword = "";
string lsPasswordAnswer = "";
if (loReader.Read())
{
lsPassword = loReader.GetString(0);
lsPasswordAnswer = loReader.GetString(2);
if (loReader.GetBoolean(3))
{
throw new MembershipPasswordException("The supplied user is locked out.");
}
}
else
{
throw new MembershipPasswordException("The supplied user name is not found.");
}
if (RequiresQuestionAndAnswer && !CheckPassword(lsAnswer, lsPasswordAnswer))
{
UpdateFailureCount(lsUsername, "passwordAnswer");
throw new MembershipPasswordException("Incorrect password answer.");
}
if (PasswordFormat == MembershipPasswordFormat.Encrypted)
{
lsPassword = UnEncodePassword(lsPassword);
}
return lsPassword;
}
private MembershipUser GetUser(string lsUsername)
{
MembershipUser loUser = null;
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectUser,loParameters);
if (loReader.Read()){
loUser = GetUserFromReader(loReader);
}
return loUser;
}
//
// MembershipProvider.GetUser(string, bool)
//
public override MembershipUser GetUser(string lsUsername, bool lbUserIsOnline)
{
MembershipUser loUser = GetUser(lsUsername);
if (lbUserIsOnline && loUser != null)
{
UpdateLast(lsUsername, oData.oDD.LastActivityDate);
}
return loUser;
}
private void UpdateLast(string lsUsername,string lsDateField)
{
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(lsDateField, DateTime.Now);
oData.ExecuteNonQuery(oData.oDM.GetUpdateDate(lsDateField), loParameters);
}
//
// MembershipProvider.GetUser(object, bool)
//
public override MembershipUser GetUser(object loProviderUserKey, bool lbUserIsOnline)
{
string lsUsername = "";
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.id, (int)loProviderUserKey);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
lsUsername = (string)oData.ExecuteScalar(oData.oDM.SelectUsername, loParameters);
if (lsUsername == null) lsUsername = "";
return GetUser(lsUsername, lbUserIsOnline);
}
//
// GetUserFromReader
// A helper function that takes the current row from the OdbcDataReader
// and hydrates a MembershiUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(IDataReader loReader)
{
MembershipUser loUser;
object loProviderUserKey = loReader.GetValue(0);
string lsUsername = loReader.GetString(1);
string lsEmail = loReader.GetString(2);
string lsPasswordQuestion = "";
if (loReader.GetValue(3) != DBNull.Value)
lsPasswordQuestion = loReader.GetString(3);
string lsComment = "";
if (loReader.GetValue(4) != DBNull.Value)
lsComment = loReader.GetString(4);
bool lbIsApproved = loReader.GetBoolean(5);
bool lbIsLockedOut = loReader.GetBoolean(6);
DateTime ldCreationDate = loReader.GetDateTime(7);
DateTime ldLastLoginDate = new DateTime();
if (loReader.GetValue(8) != DBNull.Value)
ldLastLoginDate = loReader.GetDateTime(8);
DateTime ldLastActivityDate = loReader.GetDateTime(9);
DateTime ldLastPasswordChangedDate = loReader.GetDateTime(10);
DateTime ldLastLockedOutDate = new DateTime();
if (loReader.GetValue(11) != DBNull.Value)
ldLastLockedOutDate = loReader.GetDateTime(11);
loUser = new MembershipUser(this.Name,
lsUsername,
loProviderUserKey,
lsEmail,
lsPasswordQuestion,
lsComment,
lbIsApproved,
lbIsLockedOut,
ldCreationDate,
ldLastLoginDate,
ldLastActivityDate,
ldLastPasswordChangedDate,
ldLastLockedOutDate);
return loUser;
}
//
// MembershipProvider.UnlockUser
//
public override bool UnlockUser(string lsUsername)
{
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.LastLockedOutDate, DateTime.Now);
loParameters.Add(oData.oDD.IsLockedOut, 0);
oData.ExecuteNonQuery(oData.oDM.UpdateUserLockout, loParameters);
return true;
}
//
// MembershipProvider.GetUserNameByEmail
//
public override string GetUserNameByEmail(string lsEmail)
{
string lsUsername = "";
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Email, lsEmail);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
lsUsername = (string)oData.ExecuteScalar(oData.oDM.SelectUsernameFromEmail, loParameters);
if (lsUsername == null) lsUsername = "";
return lsUsername;
}
//
// MembershipProvider.ResetPassword
//
public override string ResetPassword(string lsUsername, string lsAnswer)
{
if (!EnablePasswordReset)
{
throw new NotSupportedException("Password reset is not enabled.");
}
if (lsAnswer == null && RequiresQuestionAndAnswer)
{
UpdateFailureCount(lsUsername, "passwordAnswer");
throw new ProviderException("Password answer required for password reset.");
}
string lsPasswordNew =
System.Web.Security.Membership.GeneratePassword(_nNewPasswordLength, MinRequiredNonAlphanumericCharacters);
ValidatePasswordEventArgs loEventArgs =
new ValidatePasswordEventArgs(lsUsername, lsPasswordNew, true);
OnValidatingPassword(loEventArgs);
if (loEventArgs.Cancel)
if (loEventArgs.FailureInformation != null)
throw loEventArgs.FailureInformation;
else
throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectUserPassword, loParameters);
string lsPasswordAnswer;
if (loReader.Read())
{
if (loReader.GetBoolean(3))
throw new MembershipPasswordException("The supplied user is locked out.");
lsPasswordAnswer = loReader.GetString(2);
}
else
{
throw new MembershipPasswordException("The supplied user name is not found.");
}
if (RequiresQuestionAndAnswer && !CheckPassword(lsAnswer, lsPasswordAnswer))
{
UpdateFailureCount(lsUsername, "passwordAnswer");
throw new MembershipPasswordException("Incorrect password answer.");
}
else
{
loParameters.Add(oData.oDD.Password, EncodePassword(lsPasswordNew));
loParameters.Add(oData.oDD.LastPasswordChangedDate, DateTime.Now);
oData.ExecuteNonQuery(oData.oDM.UpdatePassword, loParameters);
return lsPasswordNew;
}
}
//
// MembershipProvider.UpdateUser
//
public override void UpdateUser(MembershipUser loUser)
{
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, loUser.UserName);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.Comment, loUser.Comment);
loParameters.Add(oData.oDD.IsApproved, loUser.IsApproved);
loParameters.Add(oData.oDD.Email, loUser.Email);
oData.ExecuteNonQuery(oData.oDM.UpdateUser, loParameters);
}
//
// MembershipProvider.ValidateUser
//
public override bool ValidateUser(string lsUsername, string lsPasswordCheck)
{
bool lbIsValid = false;
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectUserPassword, loParameters);
string lsPassword = "";
bool lbIsApproved = false;
if (loReader.Read())
{
lsPassword = loReader.GetString(0);
lbIsApproved = loReader.GetBoolean(1);
}
if (CheckPassword(lsPasswordCheck, lsPassword))
{
if (lbIsApproved)
{
lbIsValid = true;
UpdateLast(lsUsername, oData.oDD.LastLoginDate);
}
}
else
{
UpdateFailureCount(lsUsername, "password");
}
return lbIsValid;
}
//
// UpdateFailureCount
// A helper method that performs the checks and updates associated with
// password failure tracking.
//
private void UpdateFailureCount(string username, string failureType)
{
string lsUsername = username;
string lsFailureType = failureType;
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.Username, lsUsername);
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectPasswordFailCount, loParameters);
int lnFailureCount = 0;
DateTime ldWindowStart = new DateTime();
if (loReader.Read())
{
if (lsFailureType == "password")
{
lnFailureCount = loReader.GetInt32(0);
ldWindowStart = loReader.GetDateTime(1);
}
else if (lsFailureType == "passwordAnswer")
{
lnFailureCount = loReader.GetInt32(2);
ldWindowStart = loReader.GetDateTime(3);
}
DateTime ldWindowEnd = ldWindowStart.AddMinutes(PasswordAttemptWindow);
if (lnFailureCount == 0 || DateTime.Now > ldWindowEnd)
{
// First password failure or outside of PasswordAttemptWindow.
// Start a new password failure count from 1 and a new window starting now.
//UpdatePasswordAttemptFailureWindow
if (lsFailureType == "password")
{
loParameters.Add(oData.oDD.FailedPasswordCount, 1);
loParameters.Add(oData.oDD.FailedPasswordStart, ldWindowStart);
oData.ExecuteNonQuery(oData.oDM.UpdatePasswordAttemptFailureWindow, loParameters);
}
else if (lsFailureType == "passwordAnswer")
{
loParameters.Add(oData.oDD.FailedPasswordAnswerCount, 1);
loParameters.Add(oData.oDD.FailedPasswordAnswerStart, ldWindowStart);
oData.ExecuteNonQuery(oData.oDM.UpdatePasswordAnswerAttemptFailureWindow, loParameters);
}
} else if (lnFailureCount++ >= MaxInvalidPasswordAttempts){
// Password attempts have exceeded the failure threshold. Lock out
// the user.
loParameters.Add(oData.oDD.IsLockedOut,1);
oData.ExecuteNonQuery(oData.oDM.UpdateUserLockout,loParameters);
} else {
// Password attempts have not exceeded the failure threshold. Update
// the failure counts. Leave the window the same.
if (lsFailureType == "password")
{
loParameters.Add(oData.oDD.FailedPasswordCount, lnFailureCount);
oData.ExecuteNonQuery(oData.oDM.UpdatePasswordAttemptFailure, loParameters);
}
else if (lsFailureType == "passwordAnswer")
{
loParameters.Add(oData.oDD.FailedPasswordAnswerCount, lnFailureCount);
oData.ExecuteNonQuery(oData.oDM.UpdatePasswordAnswerAttemptFailure, loParameters);
}
}
}
}
//
// CheckPassword
// Compares password values based on the MembershipPasswordFormat.
//
private bool CheckPassword(string lsPasswordCheck, string lsPassword)
{
string pass1 = lsPasswordCheck;
string pass2 = lsPassword;
switch (PasswordFormat)
{
case MembershipPasswordFormat.Encrypted:
pass2 = UnEncodePassword(lsPassword);
break;
case MembershipPasswordFormat.Hashed:
pass1 = EncodePassword(lsPasswordCheck);
break;
default:
break;
}
if (pass1 == pass2)
{
return true;
}
return false;
}
//
// EncodePassword
// Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
//
private string EncodePassword(string lsPassword)
{
string lsPasswordEncoded = lsPassword;
switch (PasswordFormat)
{
case MembershipPasswordFormat.Clear:
break;
case MembershipPasswordFormat.Encrypted:
lsPasswordEncoded =
Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(lsPassword)));
break;
case MembershipPasswordFormat.Hashed:
HMACSHA1 loHash = new HMACSHA1();
loHash.Key = HexToByte(_oMachineKey.ValidationKey);
lsPasswordEncoded =
Convert.ToBase64String(loHash.ComputeHash(Encoding.Unicode.GetBytes(lsPassword)));
break;
default:
throw new ProviderException("Unsupported password format.");
}
return lsPasswordEncoded;
}
//
// UnEncodePassword
// Decrypts or leaves the password clear based on the PasswordFormat.
//
private string UnEncodePassword(string lsPasswordEncoded)
{
string lsPassword = lsPasswordEncoded;
switch (PasswordFormat)
{
case MembershipPasswordFormat.Clear:
break;
case MembershipPasswordFormat.Encrypted:
lsPassword =
Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(lsPassword)));
break;
case MembershipPasswordFormat.Hashed:
throw new ProviderException("Cannot unencode a hashed password.");
default:
throw new ProviderException("Unsupported password format.");
}
return lsPassword;
}
//
// HexToByte
// Converts a hexadecimal string to a byte array. Used to convert encryption
// key values from the configuration.
//
private byte[] HexToByte(string lsHexString)
{
byte[] loReturnBytes = new byte[lsHexString.Length / 2];
for (int lnB = 0; lnB < loReturnBytes.Length; lnB++)
loReturnBytes[lnB] = Convert.ToByte(lsHexString.Substring(lnB * 2, 2), 16);
return loReturnBytes;
}
//
// MembershipProvider.FindUsersByName
//
public override MembershipUserCollection FindUsersByName(string lsUsername, int lnPageIndex, int lnPageSize, out int lnTotalRecords)
{
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.Username, lsUsername);
lnTotalRecords = (int)oData.ExecuteScalar(oData.oDM.SelectUserCountByName, loParameters);
MembershipUserCollection laUsers = new MembershipUserCollection();
if (lnTotalRecords <= 0) return laUsers;
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectUserListByName, loParameters);
int lnCounter = 0;
int lnStartIndex = lnPageSize * lnPageIndex;
int lnEndIndex = lnStartIndex + lnPageSize - 1;
while (loReader.Read() && lnCounter < lnEndIndex)
{
if (lnCounter >= lnStartIndex)
{
MembershipUser loUser = GetUserFromReader(loReader);
laUsers.Add(loUser);
}
lnCounter++;
}
return laUsers;
}
//
// MembershipProvider.FindUsersByEmail
//
public override MembershipUserCollection FindUsersByEmail(string lsEmail, int lnPageIndex, int lnPageSize, out int lnTotalRecords)
{
ListDictionary loParameters = new ListDictionary();
loParameters.Add(oData.oDD.ApplicationName, _sApplicationName);
loParameters.Add(oData.oDD.Email, lsEmail);
lnTotalRecords = (int)oData.ExecuteScalar(oData.oDM.SelectUserCountByEmail, loParameters);
MembershipUserCollection laUsers = new MembershipUserCollection();
if (lnTotalRecords <= 0) return laUsers;
IDataReader loReader = oData.ExecuteReader(oData.oDM.SelectUserListByEmail, loParameters);
int lnCounter = 0;
int lnStartIndex = lnPageSize * lnPageIndex;
int lnEndIndex = lnStartIndex + lnPageSize - 1;
while (loReader.Read() && lnCounter < lnEndIndex)
{
if (lnCounter >= lnStartIndex)
{
MembershipUser loUser = GetUserFromReader(loReader);
laUsers.Add(loUser);
}
lnCounter++;
}
return laUsers;
}
}
}