Managing User accounts using 3rd party apps

For most Third Party utilities and Applications there is normally some sort of Account management functionality required.

The way MaNGOS handles this is fairly simple and painless

The account information is held in the account table of the realm Database

The `sha_pass_hash` field is nothing else but the SHA1 hash of "USERNAME:PASSWORD", the upper-case username, a ":" and the upper-case password.

As the password-field contains the username, you cant just change the username in the `account` table, you always have to change the password-hash too but don't worry, it sounds harder then it is - Luckily mysql provides all we need.

Change password:

SQL:

UPDATE `account` 
SET `sha_pass_hash` = SHA1(CONCAT(UPPER(`usernameabc`),':',UPPER('passwordxyz'))), `v`='', `s`='' 
WHERE `id` = x; 

Where 'usernameabc' is the username, 'passwordxyz' is the password and 'x' is the account id of the existing entry.

Change username:

SQL:

UPDATE `account` 
SET `username` = 'usernameabc', `sha_pass_hash` = SHA1(CONCAT(UPPER('usernameabc'),':',UPPER('passwordxyz'))), `v`='', `s`=''
WHERE `id` = x; 

Where 'usernameabc' is the username, 'passwordxyz' is the password and 'x' is the account id of the existing entry.

Create new account:

SQL:

INSERT INTO `account` (`username`,`sha_pass_hash`) 
VALUES ('usernameabc', SHA1(CONCAT(UPPER('usernameabc'),':',UPPER('passwordxyz')))); 

Where 'usernameabc' is the username, 'passwordxyz' is the password.

Listing all characters belonging to an account:

SQL:

SELECT character0.characters.* 
FROM realmd.account
LEFT JOIN character0.characters ON realmd.account.id = character0.characters.account
WHERE username='player'

Where character0 is your characters DB, realmd is your realm DB. player is the name of the account you want to find the characters for.