/// <summary>
/// Takes, as input, a list of user names and a list of role names and removes the
/// specified users from the specified roles.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>RemoveUsersFromRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="usernames">The array of usernames.</param>
/// <param name="roleNames">The array of roles.</param>
internal void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
// Validate the input parameters
ValidationUtil.CheckArrayParameterIsOk(ref roleNames, true, true, true, 256, "roleNames");
ValidationUtil.CheckArrayParameterIsOk(ref usernames, true, true, true, 256, "usernames");
// Instantiate lists to hold the calculated user and role Ids.
List<string> userIdsList = new List<string>();
List<string> roleIdsList = new List<string>();
// Ensure that all of the roleNames are valid
foreach (string username in usernames)
{
string userId = this.GetUserIdByName(username);
if (userId == null)
{
throw new ProviderException(string.Format(Messages.UserWasNotFound, username));
}
userIdsList.Add(userId);
}
// Ensure that all of the roleNames are valid
foreach (string rolename in roleNames)
{
string roleId = this.GetRoleIdByName(rolename);
if (roleId == null)
{
throw new ProviderException(string.Format(Messages.RoleNotFound, rolename));
}
roleIdsList.Add(roleId);
}
// Ensure that the users are actually the the roles to begin with!
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
if (!this.IsUserInRole(username, rolename))
{
throw new ProviderException(string.Format(Messages.UserAlreadyNotInRole, username, rolename));
}
}
}
// Build up the SQL string
string sql = @"
DELETE FROM aspnet_UsersInRoles
WHERE
UserId = ?
AND RoleId = ?
";
// Create a new command and enrol in the current transaction.
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
IngresParameter userParm = cmd.Parameters.Add("UserId", DbType.String);
IngresParameter roleParm = cmd.Parameters.Add("RoleId", DbType.String);
// For each user
foreach (string userId in userIdsList)
{
// For each role
foreach (string roleId in roleIdsList)
{
userParm.Value = userId;
roleParm.Value = roleId;
// Remove the user from the role
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Takes, as input, a list of user names and a list of role names and adds the specified
/// users to the specified roles.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>AddUsersToRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="usernames">A list of user names.</param>
/// <param name="roleNames">A list of roles.</param>
internal void AddUsersToRoles(string[] usernames, string[] roleNames)
{
// Note: Most of this validation is also done by the .NET framework - however I will
// explicitly do it here also.
ValidationUtil.CheckArrayParameterIsOk(ref roleNames, true, true, true, 256, "roleNames");
ValidationUtil.CheckArrayParameterIsOk(ref usernames, true, true, true, 256, "usernames");
// Ensure that all of the roleNames are valid.
foreach (string rolename in roleNames)
{
if (!this.RoleExists(rolename))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, rolename));
}
}
// Ensure that all of the usernames are valid.
foreach (string username in usernames)
{
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserWasNotFound, username));
}
}
// Ensure that all of the users actually are in the roles.
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
if (this.IsUserInRole(username, rolename))
{
throw new ProviderException(string.Format(Messages.UserAlreadyInRole, username, rolename));
}
}
}
// Instantiate a string to hold the required SQL
string sql = @"
INSERT INTO aspnet_UsersInRoles
(UserId,
RoleId)
VALUES
(?,
?)
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
IngresParameter userParam = cmd.Parameters.Add("UserId", DbType.String);
IngresParameter roleParam = cmd.Parameters.Add("RoleId", DbType.String);
// Note: this if a bit "row at a time" style rather than "chunk at a time"
// processing - however it makes for easier to read and understand code and
// allows for a static parameterized SQL string. If performance is poor then
// this could be changed.
// for each user
foreach (string username in usernames)
{
// obtain the user id
string userId = this.GetUserIdByName(username);
// for each rolename
foreach (string rolename in roleNames)
{
// obtain role id
string roleId = this.GetRoleIdByName(rolename);
userParam.Value = userId;
roleParam.Value = roleId;
// try to add user to role
// The number of rows affected
int rows = cmd.ExecuteNonQuery();
// One row should be affected
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
}
}
}
/// <summary>
/// Takes, as input, a role name and a Boolean value that indicates whether to throw an
/// exception if there are users currently associated with the role, and then deletes the
/// specified role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>DeleteRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">The user name that we wish to delete.</param>
/// <param name="throwOnPopulatedRole">Whether we should throw an exception if the role
/// we wish to delete has any users in the role or not.</param>
/// <returns>Whether the role was successfully deleted.</returns>
internal bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
// Validate the rolename
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// The roleName should exist
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// If we have to throw errors for a poupulated roll then we must check if there are
// any users in the role.
if (throwOnPopulatedRole && (this.GetUsersInRole(roleName).Length > 0))
{
throw new ProviderException(string.Format(Messages.RoleNotEmpty));
}
string sqlDeleteUsersInRoles = @"
DELETE FROM aspnet_UsersInRoles
WHERE
RoleId = ?
";
// Create a new command and enrol in the current transactions
IngresCommand cmdDeleteUsersInRoles = new IngresCommand(sqlDeleteUsersInRoles, this.conn);
cmdDeleteUsersInRoles.Transaction = this.tran;
cmdDeleteUsersInRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required parameter
cmdDeleteUsersInRoles.Parameters.Add("RoleId", DbType.String).Value = this.GetRoleIdByName(roleName);
// Delete the users
cmdDeleteUsersInRoles.ExecuteNonQuery();
string sqlDeleteRoles = @"
DELETE FROM aspnet_Roles
WHERE
LoweredRolename = ?
AND ApplicationId = ?
";
// Create a new command and enrol in the current transaction
IngresCommand cmdDeleteRoles = new IngresCommand(sqlDeleteRoles, this.conn);
cmdDeleteRoles.Transaction = this.tran;
cmdDeleteRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required paramaters
cmdDeleteRoles.Parameters.Add("LoweredRolename", DbType.String).Value = roleName.ToLower();
cmdDeleteRoles.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Finally delete the role
int rows = cmdDeleteRoles.ExecuteNonQuery();
// If more than one row was effected then throw an error
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
return true;
}
/// <summary>
/// Takes, as input, the name of a new user, a password, and an email address and inserts
/// a new user for the application into the data source. The <c>CreateUser</c> method returns a
/// <c>MembershipUser</c> object populated with the information for the newly created user.
/// The <c>CreateUser</c> method also defines an out parameter that returns a
/// <c>MembershipCreateStatus</c> value that indicates whether the user was successfully created,
/// or a reason that the user was not successfully created.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>CreateUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">name of the new user.</param>
/// <param name="password">password for the new user.</param>
/// <param name="email">email address of the new user.</param>
/// <param name="passwordQuestion">password reset question for the new user.</param>
/// <param name="passwordAnswer">password reset answer for the new user.</param>
/// <param name="isApproved">a boolean indicating whether the user has been approved or not</param>
/// <param name="providerUserKey">the identifier/key for the user.</param>
/// <param name="status">membership creation status for the user.</param>
/// <returns>A MembershipUser object populated with the information for the newly created user.</returns>
internal MembershipUser CreateUser(
string username,
string password,
string email,
string passwordQuestion,
string passwordAnswer,
bool isApproved,
object providerUserKey,
out MembershipCreateStatus status)
{
// Ensure that the password is valid
if (!ValidationUtil.IsParameterValid(ref password, true, true, false, 128))
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Get a new salt and then encode the password
string salt = PasswordUtil.GetSalt(16);
string pass = this.EncodePassword(password, this.config.PasswordFormat, salt);
// Ensure that the password does not exceed the maximum allowed length
if (pass.Length > 128)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Encode the password answer and validate.
string encodedPasswordAnswer;
if (passwordAnswer != null)
{
passwordAnswer = passwordAnswer.Trim();
}
if (!string.IsNullOrEmpty(passwordAnswer))
{
if (passwordAnswer.Length > 128)
{
status = MembershipCreateStatus.InvalidAnswer;
return null;
}
encodedPasswordAnswer = this.EncodePassword(passwordAnswer.ToLower(CultureInfo.InvariantCulture), this.config.PasswordFormat, salt);
}
else
{
encodedPasswordAnswer = passwordAnswer;
}
if (!ValidationUtil.IsParameterValid(ref encodedPasswordAnswer, this.config.RequiresQuestionAndAnswer, true, false, 128))
{
status = MembershipCreateStatus.InvalidAnswer;
return null;
}
// Validate username.
if (!ValidationUtil.IsParameterValid(ref username, true, true, true, 256))
{
status = MembershipCreateStatus.InvalidUserName;
return null;
}
// Validate email
if (!ValidationUtil.IsParameterValid(
ref email,
this.config.RequiresUniqueEmail,
this.config.RequiresUniqueEmail,
false,
256))
{
status = MembershipCreateStatus.InvalidEmail;
return null;
}
// Validate password question.
if (!ValidationUtil.IsParameterValid(ref passwordQuestion, this.config.RequiresQuestionAndAnswer, true, false, 256))
{
status = MembershipCreateStatus.InvalidQuestion;
return null;
}
// Ensure that the key is valid (i.e. a valid Guid).
if (providerUserKey != null)
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
// Check that the password is of a sufficient length.
if (password.Length < this.config.MinRequiredPasswordLength)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Ensure that the password has the requires number of non-alphanumeric characters.
int count = 0;
for (int i = 0; i < password.Length; i++)
{
if (!char.IsLetterOrDigit(password, i))
{
count++;
}
}
if (count < this.config.MinRequiredNonAlphanumericCharacters)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Check that the password meets the required regex strength
if (this.config.PasswordStrengthRegularExpression.Length > 0)
{
if (!Regex.IsMatch(password, this.config.PasswordStrengthRegularExpression))
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
}
// Raise the 'on validating password' event.
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
this.provider.OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Make sure that if we require a unique email that there are
// not any existing users with the users email.
if (this.config.RequiresUniqueEmail && (this.GetUserNameByEmail(email) != string.Empty))
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
// Ensure the user doesn't exist
MembershipUser user = this.GetUser(username, false);
// The user doesn't exist so create him
if (user == null)
{
this.CreateUserForApplication(username, false, DateTime.Now, ref providerUserKey);
string sql = @"
INSERT INTO aspnet_Membership
(ApplicationId,
UserId,
Password,
PasswordFormat,
PasswordSalt,
MobilePIN,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailPwdAttemptCount,
FailPwdAttemptWindowStart,
FailPwdAnswerAttemptCount,
FailPwdAnswerAttemptWindowStart,
Comment)
VALUES
(?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?)
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("UserId", DbType.String).Value = providerUserKey.ToString();
cmd.Parameters.Add("Password", DbType.String).Value = pass;
cmd.Parameters.Add("PasswordFormat", DbType.Int32).Value = (int)this.config.PasswordFormat;
cmd.Parameters.Add("PasswordSalt", DbType.String).Value = salt;
cmd.Parameters.Add("MobilePIN", DbType.String).Value = "PIN";
cmd.Parameters.Add("Email", DbType.String).Value = email;
cmd.Parameters.Add("LoweredEmail", DbType.String).Value = email.ToLower();
cmd.Parameters.Add("PasswordQuestion", DbType.String).Value = passwordQuestion;
cmd.Parameters.Add("PasswordAnswer", DbType.String).Value = encodedPasswordAnswer;
cmd.Parameters.Add("IsApproved", DbType.String).Value = isApproved ? '1' : '0';
cmd.Parameters.Add("IsLockedOut", DbType.String).Value = '0';
cmd.Parameters.Add("CreateDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("LastLoginDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("LastPasswordChangedDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("LastLockoutDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("FailPwdAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("FailPwdAttemptWindowStart", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("FailPwdAnswerAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("FailPwdAnswerAttemptWindowStart", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("Comment", DbType.String).Value = "Created by the Ingres ASP.NET Membership Provider.";
int rows = cmd.ExecuteNonQuery();
if (rows > 1)
{
throw new Exception(Messages.MoreThanOneRowWasAffected);
}
status = rows > 0 ? MembershipCreateStatus.Success : MembershipCreateStatus.UserRejected;
return this.GetUser(username, false);
}
// The user existed so we return duplicate user name.
status = MembershipCreateStatus.DuplicateUserName;
return null;
}
/// <summary>
/// Takes, as input, the name of a user and deletes that user's information from the data
/// source. The <c>DeleteUser</c> method returns true if the user was successfully deleted;
/// otherwise, false. An additional Boolean parameter is included to indicate whether
/// related information for the user, such as role or profile information is also deleted.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>DeleteUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username to delete.</param>
/// <param name="deleteAllRelatedData">Whether to delete all related data or not.</param>
/// <returns>Returns true if the user was successfully deleted; otherwise, false.</returns>
internal bool DeleteUser(string username, bool deleteAllRelatedData)
{
Guid userId;
if (!this.GetUserIdByName(username, out userId))
{
throw new ProviderException(Messages.UserDoesNotExist);
}
// If the username is an empty string then throw an argument exception.
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
string sql = @"
DELETE FROM aspnet_Membership
WHERE
UserId = ?
";
// create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
// Execute the query and check that an appropriate number of rows have been affected.
int rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
return false;
}
if (rows != 1)
{
throw new Exception(Messages.MoreThanOneUserWouldHaveBeenDeleted);
}
if (deleteAllRelatedData)
{
// Delete from the Users in Roles table
sql =
@"
DELETE FROM aspnet_UsersInRoles
WHERE
UserId = ?";
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
// Execute the query.
cmd.ExecuteNonQuery();
// Delete from the Users table
sql = @"
DELETE FROM aspnet_Users
WHERE
UserId = ?";
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
// Execute the query.
rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new Exception(Messages.ASingleUserShouldHaveBeenDeleted);
}
// We would also delete from the profile table when the profile provider is implemented.
// We would also delete from the personalization table when the personalization provider is implemented.
}
// The user was successfully deleted and so return true.
return true;
}
/// <summary>
/// Helper method to create a user.
/// </summary>
/// <param name="username">The username that we wish to create.</param>
/// <returns>The Id for the newly created user.</returns>
private string CreateUser(string username)
{
// Validate the username
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
string id = Guid.NewGuid().ToString().ToLower();
string sql = @"
INSERT INTO aspnet_Users
(ApplicationId,
UserId,
UserName,
LoweredUserName,
MobileAlias,
IsAnonymous,
LastActivityDate)
VALUES
(?,
?,
?,
?,
NULL,
'0',
?)
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
cmd.Parameters.Add("UserId", DbType.String).Value = id;
cmd.Parameters.Add("UserName", DbType.String).Value = username;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LastActivity", DbType.Date).Value = DateTime.Now;
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
// the user has been successfully created and so we return the id.
return id;
}
/// <summary>
/// Takes, as input, a unique user identifier and a Boolean value indicating whether to
/// update the <c>LastActivityDate</c> value for the user to show that the user is currently
/// online. The <c>GetUser</c> method returns a <c>MembershipUser</c> object populated with current
/// values from the data source for the specified user. If the user name is not found in
/// the data source, the <c>GetUser</c> method returns null.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="providerUserKey">The unique indentifer for the user.</param>
/// <param name="userIsOnline">Whether the user is online.</param>
/// <returns>The membership user with the specified provider user key.</returns>
internal MembershipUser GetUser(object providerUserKey, bool userIsOnline)
{
// Validate the input. The providerUserKey should be a Guid
if (providerUserKey == null)
{
throw new ArgumentNullException("providerUserKey");
}
if (!(providerUserKey is Guid))
{
throw new ArgumentException(string.Format(Messages.InvalidProviderUserKey), "providerUserKey");
}
string sql = @"
SELECT
aspnet_Membership.Email,
aspnet_Membership.PasswordQuestion,
aspnet_Membership.Comment,
aspnet_Membership.IsApproved,
aspnet_Membership.CreateDate,
aspnet_Membership.LastLoginDate,
aspnet_Membership.LastPasswordChangedDate,
aspnet_Users.UserId,
aspnet_Users.UserName,
aspnet_Membership.IsLockedOut,
aspnet_Membership.LastLockoutDate,
aspnet_Users.LastActivityDate
FROM
aspnet_Users,
aspnet_Membership
WHERE
aspnet_Users.UserId = ?
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("UserId", DbType.String).Value = providerUserKey.ToString().ToLower();
MembershipUser user = null;
IngresDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
user = this.GetUserFromReader(reader);
if (userIsOnline)
{
sql = @"
UPDATE aspnet_Users
SET
LastActivityDate = ?
WHERE
UserId = ?
";
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("LastActivityDate", DbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("UserId", DbType.String).Value = providerUserKey.ToString().ToLower();
cmd.ExecuteNonQuery();
}
}
return user;
}
/// <summary>
/// Creates a new user in the asnet_Users table in the database.
/// </summary>
/// <param name="userName">The username for the user.</param>
/// <param name="isUserAnonymous">Whether the user is anonymous.</param>
/// <param name="lastActivityDate">The last activity date for the user,</param>
/// <param name="userId">The Id for the user that we wish to create.</param>
private void CreateUserForApplication(string userName, bool isUserAnonymous, DateTime lastActivityDate, ref object userId)
{
if (userId == null)
{
userId = Guid.NewGuid();
}
else
{
if (this.DoesUserIdExist((Guid)userId))
{
throw new Exception("Error!!!!");
}
}
string sql = @"
INSERT INTO aspnet_Users
(ApplicationId,
userId,
userName,
LoweredUserName,
IsAnonymous,
lastActivityDate)
VALUES
(?,
?,
?,
?,
?,
?)
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
cmd.Parameters.Add("UserName", DbType.String).Value = userName;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = userName.ToLower();
cmd.Parameters.Add("IsAnonymous", DbType.String).Value = isUserAnonymous.ToString();
cmd.Parameters.Add("LastActivityDate", DbType.DateTime).Value = lastActivityDate;
// Execute the command and ensure that the row was inserted
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new Exception(Messages.ErrorAttemptingToCreateAUser);
}
}
/// <summary>
/// A helper function to determine if a user exists with a given id exists.
/// </summary>
/// <param name="userId">The Id to check.</param>
/// <returns>Whether a user exists with the given Id.</returns>
private bool DoesUserIdExist(Guid? userId)
{
string sql = @"
SELECT
UserId
FROM
aspnet_Users
WHERE
UserId = ?
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
int rows = cmd.ExecuteNonQuery();
return rows > 0;
}
/// <summary>
/// Takes, as input, a <c>MembershipUser</c> object populated with user information and updates
/// the data source with the supplied values.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>Update</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="user">The membership user to update.</param>
internal void UpdateUser(MembershipUser user)
{
if (user == null)
{
throw new ArgumentNullException("user");
}
// Instatiate a string to hold the required sql.
string applicationId;
string userId;
string sql = @"
SELECT
aspnet_Users.UserId,
aspnet_Applications.ApplicationId
FROM
aspnet_Users,
aspnet_Applications,
aspnet_Membership
WHERE
LoweredUserName = ?
AND aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
// Instantiate a new Ingres command using the new connection and sql and add required parameters.
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = user.UserName.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
// Execute the reader and retrieve the user and application ids
IngresDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
userId = DBUtil.ColValAsString(reader, "UserId");
applicationId = DBUtil.ColValAsString(reader, "ApplicationId");
reader.Close();
}
else
{
throw new ProviderException("Could not find user.");
}
if (userId == null)
{
throw new ProviderException("Could not find user.");
}
// If we require a unique email then check that the email is unique.
if (this.config.RequiresUniqueEmail)
{
sql = @"SELECT
COUNT(*)
FROM
aspnet_Membership
WHERE
ApplicationId = ?
AND UserId <> ?
AND LoweredEmail = ?";
// Get a new Ingres command using the same connection and transaction
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = applicationId;
cmd.Parameters.Add("UserId", DbType.String).Value = user.ProviderUserKey;
cmd.Parameters.Add("LoweredEmail", DbType.String).Value = user.Email.ToLower();
// Ensure that we don't get anything returned - throw an exception if we do.
int records = (int)cmd.ExecuteScalar();
if (records != 0)
{
throw new ProviderException(Messages.UniqueEmailRequired);
}
}
// Update the last activity date
sql = @"UPDATE aspnet_Users
SET
LastActivityDate = ?
WHERE
UserId = ?";
// Get a new Ingres command using the same connection and transaction
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add required parameters.
cmd.Parameters.Add("LastActivityDate", DbType.DateTime).Value = user.LastActivityDate;
cmd.Parameters.Add("UserId", DbType.String).Value = userId;
// Execute the query.
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new ProviderException(Messages.FailedToUpdateTheLastActivityDate);
}
// Update the memebership details
sql = @"UPDATE aspnet_Membership
SET
Email = ?,
LoweredEmail = ?,
Comment = ?,
IsApproved = ?,
LastLoginDate = ?
WHERE
UserId =?";
// Get a new Ingres command using the same connection and transaction
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the parameters
cmd.Parameters.Add("Email", DbType.String).Value = user.Email;
cmd.Parameters.Add("LoweredEmail", DbType.String).Value = user.Email.ToLower();
cmd.Parameters.Add("Comment", DbType.String).Value = user.Comment;
cmd.Parameters.Add("IsApproved", DbType.String).Value = user.IsApproved;
cmd.Parameters.Add("LastLoginDate", DbType.String).Value = user.LastLoginDate;
cmd.Parameters.Add("UserId", DbType.String).Value = userId;
rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new ProviderException(Messages.FailedToUpdateMembershipDetails);
}
}
/// <summary>
/// Checks whether a supplied password for a user is valid or not.
/// </summary>
/// <param name="username">The username to check.</param>
/// <param name="password">The supplied password to check.</param>
/// <param name="updateLastLoginActivityDate">Whether to update the last login activity date.</param>
/// <param name="failIfNotApproved">Whether the operation should fail if the user is not approved.</param>
/// <param name="salt">The salt to use.</param>
/// <param name="passwordFormat">The password format to use.</param>
/// <returns>Whether the supplied password matches the password on the database.</returns>
private bool CheckPassword(string username, string password, bool updateLastLoginActivityDate, bool failIfNotApproved, out string salt, out MembershipPasswordFormat passwordFormat)
{
// Default the 'out' parameters.
salt = null;
passwordFormat = MembershipPasswordFormat.Clear;
// Assume that the user is not valid
bool valid = false;
// Build the required SQL string.
string sql = @"
SELECT
Password,
PasswordFormat,
PasswordSalt,
IsApproved
FROM
aspnet_Membership,
aspnet_Users,
aspnet_Applications
WHERE
aspnet_Users.LoweredUserName = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND IsLockedOut = 0
AND aspnet_Users.UserId = aspnet_Membership.UserId
AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
";
// Create a command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql);
cmd.Connection = this.conn;
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters.
cmd.Parameters.Add("LoweredUserNameUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
bool approved;
// Execute the command and determine the password and whether the user is locked out
IngresDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
string databasePassword;
if (reader.HasRows)
{
reader.Read();
databasePassword = DBUtil.ColValAsString(reader, "Password");
int tempPasswordFormat = DBUtil.ColValAsInt32(reader, "PasswordFormat");
switch (tempPasswordFormat)
{
case 0:
passwordFormat = MembershipPasswordFormat.Clear;
break;
case 1:
passwordFormat = MembershipPasswordFormat.Hashed;
break;
case 2:
passwordFormat = MembershipPasswordFormat.Encrypted;
break;
default:
throw new ProviderException(Messages.PasswordIsStoredInAUnrecognisedFormat);
}
salt = DBUtil.ColValAsString(reader, "PasswordSalt");
approved = (DBUtil.ColValAsString(reader, "IsApproved") == "1");
}
else
{
return false;
}
reader.Close();
if (this.CheckPassword(password, databasePassword, passwordFormat, salt))
{
if (approved)
{
valid = true;
sql = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET LastLoginDate = ?,
FailPwdAttemptCount = 0
WHERE
aspnet_Users.LoweredUserName = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
// Create a new command and enrol in the current transaction
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters,
cmd.Parameters.Add("LastLoginDate", DbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("Username", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
// Execute the query and ensure that only one row was updated
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new Exception(Messages.ErrorTryingToUpdateTheLastLoginDate);
}
}
}
else
{
this.UpdateFailureCount(username, FailureReason.Password);
}
return valid;
}
/// <summary>
/// Takes, as input, a user name, and updates the field in the data source that stores the
/// IsLockedOut property to false. The <c>UnlockUser</c> method returns true if the record for the
/// membership user is updated successfully; otherwise false.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>UnlockUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="userName">The userName to unlock.</param>
/// <returns>Whether the membership user was successfully unlocked.</returns>
internal bool UnlockUser(string userName)
{
// Che
ValidationUtil.CheckParameterIsOK(ref userName, true, true, true, 256, "userName");
Guid userId;
bool isValidUser = this.GetUserIdByName(userName, out userId);
if (!isValidUser)
{
throw new ProviderException(Messages.YouCanNotUnlockAUserWithoutSuplyingAValidUserName);
}
string sql = @"
UPDATE aspnet_Membership
SET IsLockedOut = '0',
FailPwdAttemptCount = 0,
FailPwdAttemptWindowStart = ?,
FailPwdAnswerAttemptCount = 0,
FailPwdAnswerAttemptWindowStart = ?,
LastLockoutDate = ?
WHERE
UserId = ?
";
// Create a new Ingres command.
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters.
cmd.Parameters.Add("FailPwdAttemptWindowStart", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("FailPwdAnswerAttemptWindowStart", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("LastLockoutDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
// Execute the query.
int rows = cmd.ExecuteNonQuery();
// Return whether the user was unlocked.
return rows > 0;
}
/// <summary>
/// Takes, as input, a user name and a password answer and generates a new, random password
/// for the specified user. The <c>ResetPassword</c> method updates the user information in the
/// data source with the new password value and returns the new password as a string. A
/// convenient mechanism for generating a random password is the <c>GeneratePassword</c> method of
/// the <c>Membership</c> class.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>ResetPassword</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username to reset the password for.</param>
/// <param name="answer">The answer to the users password question.</param>
/// <returns>Whether the membership user was successfully updated.</returns>
internal string ResetPassword(string username, string answer)
{
MembershipPasswordFormat passwordFormat;
string salt;
// Ensure that password reset is enabled.
if (!this.config.EnablePasswordReset)
{
throw new NotSupportedException(Messages.PasswordResetIsNotEnabled);
}
// Ensure that an answer has been supplied if appropriate.
if (answer == null && this.config.RequiresQuestionAndAnswer)
{
this.UpdateFailureCount(username, FailureReason.PasswordAnswer);
// If we got here then commit the transaction and then handle the exception
this.tran.Commit();
// Set the transaction to null so that we don't attempt to roll it back.
this.tran.Dispose();
throw new ProviderException(Messages.PasswordAnswerRequiredForPasswordReset);
}
// Generate a new password
string newPassword = Membership.GeneratePassword(this.config.NewPasswordLength, this.config.MinRequiredNonAlphanumericCharacters);
// Raise the 'on validating password' event.
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
this.provider.OnValidatingPassword(args);
if (args.Cancel)
{
if (args.FailureInformation != null)
{
throw args.FailureInformation;
}
throw new MembershipPasswordException(Messages.ResetPasswordCanceledDueToPasswordValidationFailure);
}
// Build up the required SQL.
string sql = @"
SELECT
PasswordAnswer,
PasswordFormat,
PasswordSalt,
IsLockedOut
FROM
aspnet_Membership,
aspnet_Users,
aspnet_Applications
WHERE
aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Membership.UserId = aspnet_Users.UserId
";
// Create a new Ingres command
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters.
cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
// Execute the SQL as a reader.
IngresDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
string passwordAnswer;
if (reader.HasRows)
{
reader.Read();
// Ensure that the user is not locked out.
if (DBUtil.ColValAsString(reader, "IsLockedOut") == "1")
{
throw new MembershipPasswordException(Messages.TheSuppliedUserIsLockedOut);
}
// Get the password answer and ensure that it is lowercased
passwordAnswer = DBUtil.ColValAsString(reader, "PasswordAnswer");
// Determine the password format.
int tempPasswordFormat = DBUtil.ColValAsInt32(reader, "PasswordFormat");
switch (tempPasswordFormat)
{
case 0:
passwordFormat = MembershipPasswordFormat.Clear;
break;
case 1:
passwordFormat = MembershipPasswordFormat.Hashed;
break;
case 2:
passwordFormat = MembershipPasswordFormat.Encrypted;
break;
default:
throw new ProviderException(Messages.PasswordIsStoredInAUnrecognisedFormat);
}
// Get the salt.
salt = DBUtil.ColValAsString(reader, "PasswordSalt");
}
else
{
throw new MembershipPasswordException(Messages.TheSuppliedUserNameIsNotFound);
}
if (answer == null)
{
answer = string.Empty;
}
reader.Close();
// Check that the correct password answer was supplied...
if (this.config.RequiresQuestionAndAnswer && !this.CheckPassword(answer.ToLower(CultureInfo.InvariantCulture), passwordAnswer, passwordFormat, salt))
{
// ... if not, update the failure count and throw an exception
this.UpdateFailureCount(username, FailureReason.PasswordAnswer);
// If we got here then commit the transaction and then handle the exception
this.tran.Commit();
// Set the transaction to null so that we don't attempt to roll it back.
this.tran.Dispose();
throw new MembershipPasswordException(Messages.IncorrectPasswordAnswer);
}
reader.Close();
// Build up the SQL to reset the password
sql = @"
UPDATE aspnet_Membership
FROM
aspnet_Users,
aspnet_Applications
SET Password = ?,
LastPasswordChangedDate = ?
WHERE
aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND IsLockedOut = 0
AND aspnet_Membership.UserId = aspnet_Users.UserId
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
";
// Create a new Ingres command
IngresCommand updateCmd = new IngresCommand(sql, this.conn);
updateCmd.Transaction = this.tran;
updateCmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters.
updateCmd.Parameters.Add("Password", DbType.String).Value = this.EncodePassword(newPassword, passwordFormat, PasswordUtil.GetSalt(16));
updateCmd.Parameters.Add("LastPasswordChangedDate", DbType.DateTime).Value = DateTime.Now;
updateCmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
updateCmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
// Execute the query and ensure that a row was updated.
int rowsAffected = updateCmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
return newPassword;
}
throw new MembershipPasswordException(Messages.PasswordNotReset);
}
/// <summary>
/// Takes, as input, a user name, a current password, and a new password, and updates the
/// password in the data source if the supplied user name and current password are valid.
/// The <c>ChangePassword</c> method returns true if the password was updated successfully;
/// otherwise, false.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>ChangePassword</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username.</param>
/// <param name="oldPassword">The old password.</param>
/// <param name="newPassword">The new password.</param>
/// <returns>Returns true if the password was updated successfully; otherwise, false.</returns>
internal bool ChangePassword(string username, string oldPassword, string newPassword)
{
// Validate the parameter details
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
ValidationUtil.CheckParameterIsOK(ref oldPassword, true, true, false, 128, "oldPassword");
ValidationUtil.CheckParameterIsOK(ref newPassword, true, true, false, 128, "newPassword");
// Declare the 'out' variables used when checking the password
string salt;
MembershipPasswordFormat passwordFormat;
// Ensure that the correct old password has been specified (and return salt and password format).
if (!this.CheckPassword(username, oldPassword, false, false, out salt, out passwordFormat))
{
return false;
}
// Ensure that the new password meets the minimum length requirement and that
// it is not null.
if ((newPassword.Length < this.config.MinRequiredPasswordLength) || (newPassword == null))
{
throw new ProviderException(string.Format(
Messages.PasswordTooShort,
"newPassword",
this.config.MinRequiredPasswordLength.ToString(CultureInfo.InvariantCulture)));
}
// Ensure that an appropriate number non-alphanumeric characters have been specified.
int count = 0;
for (int i = 0; i < newPassword.Length; i++)
{
if (!char.IsLetterOrDigit(newPassword, i))
{
count++;
}
}
if (count < this.config.MinRequiredNonAlphanumericCharacters)
{
throw new ArgumentException(string.Format(
Messages.NeedMoreNonAlphanumericChars,
"newPassword",
this.config.MinRequiredNonAlphanumericCharacters.ToString(CultureInfo.InvariantCulture)));
}
// Ensure that the password meets any regex strength requirements
if (this.config.PasswordStrengthRegularExpression.Length > 0)
{
if (!Regex.IsMatch(newPassword, this.config.PasswordStrengthRegularExpression))
{
throw new ArgumentException(string.Format(Messages.DoesNotMatchRegex, "newPassword"));
}
}
string pass = this.EncodePassword(newPassword, passwordFormat, salt);
// Ensure that our new password does not exceed the maximum length allowed in the database.
if (pass.Length > 128)
{
throw new ArgumentException(string.Format(Messages.PasswordTooLong), "newPassword");
}
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
// Raise the 'on validating password' event.
this.provider.OnValidatingPassword(args);
if (args.Cancel)
{
// The change password event is to be cancelled.
if (args.FailureInformation != null)
{
throw args.FailureInformation;
}
throw new ArgumentException(string.Format(Messages.CustomPasswordValidationFailed), "newPassword");
}
string sql = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET Password = ?,
PasswordFormat = ?,
PasswordSalt = ?,
LastPasswordChangedDate = ?
WHERE
aspnet_Users.LoweredUserName = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
// Create a new command and enrol in the current transaction.
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("Password", DbType.String).Value = pass;
cmd.Parameters.Add("PasswordFormat", DbType.Int32).Value = (int)passwordFormat;
cmd.Parameters.Add("PasswordSalt", DbType.String).Value = salt;
cmd.Parameters.Add("LastPasswordChangedDate", DbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
// Execute the command and ensure that only one row was updated.
int rowsAffected = cmd.ExecuteNonQuery();
// Ensure that no more than one row was updated.
if (rowsAffected > 1)
{
throw new Exception(Messages.MoreThanOneRowWasAffectedWhenAttemptingToChangeAPassword);
}
// Return whether any rows were affected or not.
return rowsAffected > 0;
}
/// <summary>
/// Creates an application in the database.
/// </summary>
/// <param name="name">The name of the application to create.</param>
/// <param name="id">The Id of the application to create.</param>
private void CreateApplication(string name, out string id)
{
// Ensure that the proposed rolename is of a valid form and does not already exist
if (name.IndexOf(',') > 0)
{
throw new ArgumentException(Messages.ApplicationNamesCannotContainCommas);
}
// Build up the command and connection details
id = null;
string sql = @"
SELECT
ApplicationId
FROM
aspnet_Applications
WHERE
LoweredApplicationName = ?
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = name.ToLower();
IngresDataReader datareader = cmd.ExecuteReader();
if (datareader != null)
{
if (datareader.HasRows)
{
datareader.Read();
id = datareader.GetString(0);
datareader.Close();
return;
}
id = Guid.NewGuid().ToString();
}
if (datareader != null)
{
datareader.Close();
}
sql = @"
INSERT INTO aspnet_Applications
(ApplicationId,
ApplicationName,
LoweredApplicationName)
VALUES
(?,
?,
?)
";
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("ApplicationId", DbType.String).Value = id;
cmd.Parameters.Add("ApplicationName", DbType.String).Value = name;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = name.ToLower();
cmd.ExecuteNonQuery();
return;
}
/// <summary>
/// A helper method that performs the checks and updates associated with password failure
/// tracking.
/// </summary>
/// <param name="username">The username to update the failure count for.</param>
/// <param name="reason">The reason why we wish to update the failure count.</param>
private void UpdateFailureCount(string username, FailureReason reason)
{
// The required SQL
string sql = @"
SELECT
FailPwdAttemptCount,
FailPwdAttemptWindowStart,
FailPwdAnswerAttemptCount,
FailPwdAnswerAttemptWindowStart
FROM
aspnet_Membership,
aspnet_Users,
aspnet_Applications
WHERE
aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Users.UserId = aspnet_Membership.UserId
AND aspnet_Membership.ApplicationId = aspnet_Applications.ApplicationId
";
// Instantiate an Ingres command (using the current connection and transaction) with the SQL
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
DateTime windowStart = new DateTime();
int failureCount = 0;
// Execute the command as a reader
IngresDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
if (reason == FailureReason.Password)
{
failureCount = DBUtil.ColValAsInt32(reader, "FailPwdAttemptCount");
try
{
windowStart = DBUtil.ColValAsDateTime(reader, "FailPwdAttemptWindowStart");
}
catch
{
windowStart = DateTime.Now;
}
}
if (reason == FailureReason.PasswordAnswer)
{
failureCount = DBUtil.ColValAsInt32(reader, "FailPwdAnswerAttemptCount");
windowStart = DBUtil.ColValAsDateTime(reader, "FailPwdAnswerAttemptWindowStart");
}
}
// Close the reader and build up a new command
reader.Close();
cmd = new IngresCommand();
cmd.Connection = this.conn;
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
DateTime windowEnd = windowStart.AddMinutes(this.config.PasswordAttemptWindow);
if (failureCount == 0 || DateTime.Now > windowEnd)
{
// Start a new failure count and window
if (reason == FailureReason.Password)
{
// For password failed attempts:
cmd.CommandText = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET FailPwdAttemptCount = ?,
FailPwdAttemptWindowStart = ?
WHERE
LoweredUsername = ?
AND LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Membership.UserId = aspnet_Users.UserId
";
}
if (reason == FailureReason.PasswordAnswer)
{
// For password answer failed attempts:
cmd.CommandText = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET FailPwdAnswerAttemptCount = ?,
FailPwdAnswerAttemptWindowStart = ?
WHERE
aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Membership.UserId = aspnet_Users.UserId
";
}
// Ad the required parameters
cmd.Parameters.Add("Count", DbType.Int32).Value = 1;
cmd.Parameters.Add("WindowStart", DbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
// Execute the query and ensure that one row was updated
if (cmd.ExecuteNonQuery() != 1)
{
throw new ProviderException(Messages.UnableToUpdateFailureCountAndWindowStart);
}
}
else
{ // Not the first failure or still inside the window
// If the failure accout is now greater than or equal to the max attempts allowed
// then lock the user out
if (failureCount++ >= this.config.MaxInvalidPasswordAttempts)
{
// Password attempts have exceeded the failure threshold. Lock out
// the user.
cmd.CommandText = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET IsLockedOut = ?,
LastLockoutDate = ?
WHERE
aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Membership.UserId = aspnet_Users.UserId
";
cmd.Parameters.Clear();
cmd.Parameters.Add("IsLockedOut", DbType.String).Value = '1';
cmd.Parameters.Add("LastLockoutDate", DbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
if (cmd.ExecuteNonQuery() < 0)
{
throw new ProviderException("Unable to lock out user.");
}
}
else
{
// Otherwise just save the incremented failure count.
if (reason == FailureReason.Password)
{
cmd.CommandText = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET FailPwdAttemptCount = ?
WHERE
aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Membership.UserId = aspnet_Users.UserId
";
}
if (reason == FailureReason.PasswordAnswer)
{
cmd.CommandText = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET FailPwdAnswerAttemptCount = ?
WHERE
aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Membership.UserId = aspnet_Users.UserId
";
}
cmd.Parameters.Clear();
cmd.Parameters.Add("Count", DbType.Int32).Value = failureCount;
cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
if (cmd.ExecuteNonQuery() != 1)
{
throw new ProviderException(Messages.UnableToUpdateFailureCount);
}
}
}
}
/// <summary>
/// Creates a role in the database.
/// </summary>
/// <param name="roleName">The rolename to create.</param>
/// <param name="roleid">The role id.</param>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
private void CreateRole(string roleName, out string roleid, IngresConnection conn, IngresTransaction tran)
{
// Validate the roleName
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Ensure that the proposed roleName does not already exist
if (this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleAlreadyExists, roleName));
}
string sql = @"
INSERT INTO aspnet_Roles
(ApplicationId,
RoleId,
RoleName,
LoweredRoleName,
Description)
VALUES
(?,
?,
?,
?,
NULL)
";
// Create the command with the current connection and enrol in the transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Generate a new role Id - this will be the sent out
roleid = Guid.NewGuid().ToString().ToLower();
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleid;
cmd.Parameters.Add("RoleName", DbType.String).Value = roleName;
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
// Execute the query
int rows = cmd.ExecuteNonQuery();
// Validate that the query affected the correct numbber of rows.
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
}
/// <summary>
/// Takes, as input, a user name, a password, a password question, and a password answer,
/// and updates the password question and answer in the data source if the supplied user
/// name and password are valid. The <c>ChangePasswordQuestionAndAnswer</c> method returns true if
/// the password question and answer are updated successfully; otherwise, false.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>ChangePasswordQuestionAndAnswer</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username.</param>
/// <param name="password">The password.</param>
/// <param name="newPasswordQuestion">The new password question.</param>
/// <param name="newPasswordAnswer">The new password answer.</param>
/// <returns>Returns true if the password question and answer are updated successfully; otherwise, false.</returns>
internal bool ChangePasswordQuestionAndAnswer(
string username,
string password,
string newPasswordQuestion,
string newPasswordAnswer)
{
// Check the parameters
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
ValidationUtil.CheckParameterIsOK(ref password, true, true, false, 128, "password");
string salt;
MembershipPasswordFormat passwordFormat;
// Check the password
if (!this.CheckPassword(username, password, false, false, out salt, out passwordFormat))
{
return false;
}
ValidationUtil.CheckParameterIsOK(ref newPasswordQuestion, this.config.RequiresQuestionAndAnswer, this.config.RequiresQuestionAndAnswer, false, 256, "newPasswordQuestion");
// validate and encode the password answer
string encodedPasswordAnswer;
if (newPasswordAnswer != null)
{
newPasswordAnswer = newPasswordAnswer.Trim();
}
ValidationUtil.CheckParameterIsOK(ref newPasswordAnswer, this.config.RequiresQuestionAndAnswer, this.config.RequiresQuestionAndAnswer, false, 128, "newPasswordAnswer");
if (!string.IsNullOrEmpty(newPasswordAnswer))
{
encodedPasswordAnswer = this.EncodePassword(newPasswordAnswer.ToLower(CultureInfo.InvariantCulture), passwordFormat, salt);
}
else
{
encodedPasswordAnswer = newPasswordAnswer;
}
ValidationUtil.CheckParameterIsOK(ref encodedPasswordAnswer, this.config.RequiresQuestionAndAnswer, this.config.RequiresQuestionAndAnswer, false, 128, "newPasswordAnswer");
// build up the required SQL
string sql = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET PasswordQuestion = ?,
PasswordAnswer = ?
WHERE
aspnet_Users.LoweredUserName = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("PasswordQuestion", DbType.String).Value = newPasswordQuestion;
cmd.Parameters.Add("PasswordAnswer", DbType.String).Value = encodedPasswordAnswer;
cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
int rowsAffected = cmd.ExecuteNonQuery();
// Ensure that no more than one row was updated.
if (rowsAffected > 1)
{
throw new Exception(Messages.MoreThanOneRowWasAffectedWhenAttemptingToChangeAPassword);
}
return rowsAffected > 0;
}
/// <summary>
/// Helper method to check that the Ingres database used for testing does not have any
/// data in the Roles tables prior to commencing set up of a test case and after we
/// tear down a test case.
/// </summary>
private void ValidateDatabaseIsEmpty()
{
// Validate that the aspnet_UsersInRoles table is empty
IngresCommand cmd = new IngresCommand();
cmd.Connection = this.Connection;
cmd = new IngresCommand();
cmd.Connection = this.Connection;
string sql = @"SELECT
COUNT(*)
FROM
aspnet_UsersInRoles";
cmd.CommandText = sql;
int rows = (int)cmd.ExecuteScalar();
cmd.ExecuteNonQuery();
if (rows != 0)
{
throw new Exception("The aspnet_UsersInRoles table is not empty.");
}
// Validate that the aspnet_Users table is empty
cmd = new IngresCommand();
cmd.Connection = this.Connection;
sql = @"SELECT
COUNT(*)
FROM
aspnet_Users";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
rows = (int)cmd.ExecuteScalar();
if (rows != 0)
{
throw new Exception("The aspnet_Users table is not empty.");
}
// Validate that the aspnet_Roles table is empty
cmd = new IngresCommand();
cmd.Connection = this.Connection;
sql = @"SELECT
COUNT(*)
FROM
aspnet_Roles";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
rows = (int)cmd.ExecuteScalar();
if (rows != 0)
{
throw new Exception("The aspnet_Roles table is not empty.");
}
// Validate that the aspnet_Application table is empty.
sql = @"SELECT
COUNT(*)
FROM
aspnet_Applications";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
rows = (int)cmd.ExecuteScalar();
// The aspnet_applications table may contain 0 or 1 rows, depending where we are
// in the test fixture.
if (rows != 0)
{
throw new Exception("The aspnet_Applications is not empty.");
}
}
/// <summary>
/// Takes, as input, a user name and a Boolean value indicating whether to update the
/// <c>LastActivityDate</c> value for the user to show that the user is currently online. The
/// <c>GetUser</c> method returns a <c>MembershipUser</c> object populated with current values from the
/// data source for the specified user. If the user name is not found in the data source,
/// the <c>GetUser</c> method returns <c>null</c>.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username.</param>
/// <param name="userIsOnline">Whether the user us currently online.</param>
/// <returns>The membership user with the specified username.</returns>
internal MembershipUser GetUser(string username, bool userIsOnline)
{
// Ensure that a username has been provided
if (string.IsNullOrEmpty(username))
{
throw new ProviderException(Messages.UsernameMustBeSuppliedToGetAUser);
}
string sql = @"
SELECT TOP 1
aspnet_Membership.Email,
aspnet_Membership.PasswordQuestion,
aspnet_Membership.Comment,
aspnet_Membership.IsApproved,
aspnet_Membership.CreateDate,
aspnet_Membership.LastLoginDate,
aspnet_Membership.LastPasswordChangedDate,
aspnet_Users.UserId,
aspnet_Users.UserName,
aspnet_Membership.IsLockedOut,
aspnet_Membership.LastLockoutDate,
aspnet_Users.LastActivityDate
FROM
aspnet_Applications,
aspnet_Users,
aspnet_Membership
WHERE
aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId
AND aspnet_Users.LoweredUserName = ?
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
// Innocuously initialise the user
MembershipUser user = null;
IngresDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
user = this.GetUserFromReader(reader);
reader.Close();
if (userIsOnline)
{
sql = @"
UPDATE aspnet_Users
FROM
aspnet_Applications,
aspnet_Membership
SET LastActivityDate = ?
WHERE
aspnet_Users.UserId = aspnet_Membership.UserId
AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId
AND aspnet_Users.LoweredUsername = ?
AND aspnet_Applications.LoweredApplicationName = ?
";
IngresCommand updateCmd = new IngresCommand(sql, this.conn);
updateCmd.Transaction = this.tran;
updateCmd.CommandTimeout = this.config.CommandTimeout;
updateCmd.Parameters.Add("ActivityDate", DbType.DateTime).Value = DateTime.Now;
updateCmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
updateCmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
int rows = updateCmd.ExecuteNonQuery();
if (rows != 1)
{
throw new Exception(Messages.FailedToUpdateLastActivityDate);
}
}
}
if (!reader.IsClosed)
{
reader.Close();
}
return user;
}