/// <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; }