From 3045f727f246147a1733c5377a6c49007aea9a1f Mon Sep 17 00:00:00 2001 From: stuconnolly Date: Sun, 25 Mar 2012 19:46:10 +0000 Subject: Add support for renaming users on MySQL versions less than 5.0.2 by directly updating the mysql.user table. Fixes issue #1285. --- Source/SPServerSupport.h | 6 +++ Source/SPServerSupport.m | 5 ++ Source/SPUserManager.m | 135 +++++++++++++++++++++++++++++++++++------------ 3 files changed, 113 insertions(+), 33 deletions(-) diff --git a/Source/SPServerSupport.h b/Source/SPServerSupport.h index fd4535a2..ee75af31 100644 --- a/Source/SPServerSupport.h +++ b/Source/SPServerSupport.h @@ -59,6 +59,7 @@ // User account related BOOL supportsCreateUser; + BOOL supportsRenameUser; BOOL supportsDropUser; BOOL supportsFullDropUser; BOOL supportsUserMaxVars; @@ -152,6 +153,11 @@ */ @property (readonly) BOOL supportsCreateUser; +/** + * @property supportsRenameUser Indicates if the server supports the RENAME USER statement + */ +@property (readonly) BOOL supportsRenameUser; + /** * @property supportsDropUser Indicates if the server supports the DROP USER statement */ diff --git a/Source/SPServerSupport.m b/Source/SPServerSupport.m index 3d38ff79..5ddfbaf6 100644 --- a/Source/SPServerSupport.m +++ b/Source/SPServerSupport.m @@ -50,6 +50,7 @@ @synthesize supportsCharacterSetDatabaseVar; @synthesize supportsPost41CharacterSetHandling; @synthesize supportsCreateUser; +@synthesize supportsRenameUser; @synthesize supportsDropUser; @synthesize supportsFullDropUser; @synthesize supportsUserMaxVars; @@ -141,6 +142,9 @@ // The CREATE USER statement wasn't added until MySQL 5.0.2 supportsCreateUser = [self isEqualToOrGreaterThanMajorVersion:5 minor:0 release:2]; + // The RENAME USER statement wasn't added until MySQL 5.0.2 + supportsRenameUser = [self isEqualToOrGreaterThanMajorVersion:5 minor:0 release:2]; + // The DROP USER statement wasn't added until MySQL 4.1.1 supportsDropUser = [self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:1]; @@ -249,6 +253,7 @@ supportsCharacterSetDatabaseVar = NO; supportsPost41CharacterSetHandling = NO; supportsCreateUser = NO; + supportsRenameUser = NO; supportsDropUser = NO; supportsFullDropUser = NO; supportsUserMaxVars = NO; diff --git a/Source/SPUserManager.m b/Source/SPUserManager.m index 6df49239..aa59af45 100644 --- a/Source/SPUserManager.m +++ b/Source/SPUserManager.m @@ -29,7 +29,9 @@ #import "SPConnectionController.h" #import "SPServerSupport.h" #import "SPAlertSheets.h" -#import "SPMySQL.h" + +#import +#import #import static const NSString *SPTableViewNameColumnID = @"NameColumn"; @@ -51,6 +53,7 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; - (NSArray *)_fetchPrivsWithUser:(NSString *)username schema:(NSString *)selectedSchema host:(NSString *)host; - (void)_setSchemaPrivValues:(NSArray *)objects enabled:(BOOL)enabled; - (void)_initializeAvailablePrivs; +- (void)_renameUserFrom:(NSString *)originalUser host:(NSString *)originalHost to:(NSString *)newUser host:(NSString *)newHost; @end @@ -999,6 +1002,9 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; if (!isInitializing) [outlineView reloadData]; } +/** + * Updates the supplied array of users. + */ - (BOOL)updateUsers:(NSArray *)updatedUsers { for (NSManagedObject *user in updatedUsers) @@ -1010,20 +1016,16 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; else if (![user parent]) { NSArray *hosts = [user valueForKey:@"children"]; - // If the user has been changed, update the username on all hosts. Don't check for errors, as some - // hosts may be new. + // If the user has been changed, update the username on all hosts. + // Don't check for errors, as some hosts may be new. if (![[user valueForKey:@"user"] isEqualToString:[user valueForKey:@"originaluser"]]) { for (NSManagedObject *child in hosts) { - NSString *renameUserStatement = [NSString stringWithFormat: - @"RENAME USER %@@%@ TO %@@%@", - [[user valueForKey:@"originaluser"] tickQuotedString], - [([child valueForKey:@"originalhost"]?[child valueForKey:@"originalhost"]:[child host]) tickQuotedString], - [[user valueForKey:@"user"] tickQuotedString], - [[child host] tickQuotedString]]; - - [self.mySqlConnection queryString:renameUserStatement]; + [self _renameUserFrom:[user valueForKey:@"originaluser"] + host:[child valueForKey:@"originalhost"] ? [child valueForKey:@"originalhost"] : [child host] + to:[user valueForKey:@"user"] + host:[child user]]; } } @@ -1044,17 +1046,13 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; } } else { - - // If the hostname has changed, remane the detail before editing details. + // If the hostname has changed, remane the detail before editing details if (![[user valueForKey:@"host"] isEqualToString:[user valueForKey:@"originalhost"]]) { - NSString *renameUserStatement = [NSString stringWithFormat: - @"RENAME USER %@@%@ TO %@@%@", - [[[user parent] valueForKey:@"originaluser"] tickQuotedString], - [[user valueForKey:@"originalhost"] tickQuotedString], - [[[user parent] valueForKey:@"user"] tickQuotedString], - [[user valueForKey:@"host"] tickQuotedString]]; - [self.mySqlConnection queryString:renameUserStatement]; + [self _renameUserFrom:[[user parent] valueForKey:@"originaluser"] + host:[user valueForKey:@"originalhost"] + to:[[user parent] valueForKey:@"user"] + host:[user valueForKey:@"host"]]; } if ([serverSupport supportsUserMaxVars]) [self updateResourcesForUser:user]; @@ -1189,8 +1187,10 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; for (NSString *key in self.privsSupportedByServer) { if (![key hasSuffix:@"_priv"]) continue; + NSString *privilege = [key stringByReplacingOccurrencesOfString:@"_priv" withString:@""]; - @try { + + NS_DURING if ([[schemaPriv valueForKey:key] boolValue] == YES) { [grantPrivileges addObject:[privilege replaceUnderscoreWithSpace]]; } @@ -1199,15 +1199,22 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; [revokePrivileges addObject:[privilege replaceUnderscoreWithSpace]]; } } - } - @catch (NSException * e) { } + NS_HANDLER + NS_ENDHANDLER + } // Grant privileges - [self _grantPrivileges:grantPrivileges onDatabase:dbName forUser:[schemaPriv valueForKeyPath:@"user.parent.user"] host:[schemaPriv valueForKeyPath:@"user.host"]]; + [self _grantPrivileges:grantPrivileges + onDatabase:dbName + forUser:[schemaPriv valueForKeyPath:@"user.parent.user"] + host:[schemaPriv valueForKeyPath:@"user.host"]]; // Revoke privileges - [self _revokePrivileges:revokePrivileges onDatabase:dbName forUser:[schemaPriv valueForKeyPath:@"user.parent.user"] host:[schemaPriv valueForKeyPath:@"user.host"]]; + [self _revokePrivileges:revokePrivileges + onDatabase:dbName + forUser:[schemaPriv valueForKeyPath:@"user.parent.user"] + host:[schemaPriv valueForKeyPath:@"user.host"]]; return YES; } @@ -1229,6 +1236,7 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; [self.mySqlConnection queryString:updateResourcesStatement]; [self _checkAndDisplayMySqlError]; } + return YES; } @@ -1250,26 +1258,32 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; // Check the value of the priv and assign to grant or revoke query as appropriate; do this // in a try/catch check to avoid exceptions for unhandled privs - @try { + NS_DURING if ([[user valueForKey:key] boolValue] == YES) { [grantPrivileges addObject:[privilege replaceUnderscoreWithSpace]]; } else { [revokePrivileges addObject:[privilege replaceUnderscoreWithSpace]]; } - } - @catch (NSException * e) { - } + NS_HANDLER + NS_ENDHANDLER } // Grant privileges - [self _grantPrivileges:grantPrivileges onDatabase:nil forUser:[[user parent] valueForKey:@"user"] host:[user valueForKey:@"host"]]; + [self _grantPrivileges:grantPrivileges + onDatabase:nil + forUser:[[user parent] valueForKey:@"user"] + host:[user valueForKey:@"host"]]; // Revoke privileges - [self _revokePrivileges:revokePrivileges onDatabase:nil forUser:[[user parent] valueForKey:@"user"] host:[user valueForKey:@"host"]]; + [self _revokePrivileges:revokePrivileges + onDatabase:nil + forUser:[[user parent] valueForKey:@"user"] + host:[user valueForKey:@"host"]]; } - for (NSManagedObject *priv in [user valueForKey:@"schema_privileges"]) { + for (NSManagedObject *priv in [user valueForKey:@"schema_privileges"]) + { [self grantDbPrivilegesWithPrivilege:priv]; } @@ -1401,7 +1415,8 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; if ([self.mySqlConnection queryErrored]) { if (isSaving) { [errorsString appendFormat:@"%@\n", [self.mySqlConnection lastErrorMessage]]; - } else { + } + else { SPBeginAlertSheet(NSLocalizedString(@"An error occurred", @"mysql error occurred message"), NSLocalizedString(@"OK", @"OK button"), nil, nil, [self window], self, nil, nil, [NSString stringWithFormat:NSLocalizedString(@"An error occurred whilst trying to perform the operation.\n\nMySQL said: %@", @"mysql error occurred informative message"), [self.mySqlConnection lastErrorMessage]]); @@ -1575,6 +1590,60 @@ static const NSString *SPTableViewNameColumnID = @"NameColumn"; } } +#pragma mark - +#pragma mark Private API + +/** + * Renames a user account using the supplied parameters. + * + * @param originalUser The user's original user name + * @param originalHost The user's original host + * @param newUser The user's new user name + * @param newHost The user's new host + */ +- (void)_renameUserFrom:(NSString *)originalUser host:(NSString *)originalHost to:(NSString *)newUser host:(NSString *)newHost +{ + NSString *renameQuery; + + if ([serverSupport supportsRenameUser]) { + renameQuery = [NSString stringWithFormat:@"RENAME USER %@@%@ TO %@@%@", + [originalUser tickQuotedString], + [originalHost tickQuotedString], + [newUser tickQuotedString], + [newHost tickQuotedString]]; + } + else { + // mysql.user is keyed on user and host so there should only ever be one result, + // but double check before we do the update. + QKQuery *query = [QKQuery selectQueryFromTable:@"user"]; + + [query setDatabase:SPMySQLDatabase]; + [query addField:@"COUNT(1)"]; + + [query addParameter:@"User" operator:QKEqualityOperator value:originalUser]; + [query addParameter:@"Host" operator:QKEqualityOperator value:originalHost]; + + SPMySQLResult *result = [mySqlConnection queryString:[query query]]; + + if ([[[result getRowAsArray] objectAtIndex:0] integerValue] == 1) { + QKQuery *updateQuery = [QKQuery queryTable:@"user"]; + + [updateQuery setQueryType:QKUpdateQuery]; + [updateQuery setDatabase:SPMySQLDatabase]; + + [updateQuery addFieldToUpdate:@"User" toValue:newUser]; + [updateQuery addFieldToUpdate:@"Host" toValue:newHost]; + + [updateQuery addParameter:@"User" operator:QKEqualityOperator value:originalUser]; + [updateQuery addParameter:@"Host" operator:QKEqualityOperator value:originalHost]; + + renameQuery = [updateQuery query]; + } + } + + [mySqlConnection queryString:renameQuery]; +} + #pragma mark - /** -- cgit v1.2.3