[RELEASE - Trinity] Stored Procedures for Creating Accounts

[Valkryst]

Hey,

After setting up an account creation page for my server and writing up this stored procedure to create the accounts, I thought that it would be a good idea to release the query along with a few other queries that you can use for account creation.
You should only use one of these procedures unless you know SQL and can alter them to work as you wish. They need to be executed on the auth database.

Stored Procedure #1 - Creates a player account for the WotLK expansion.

Code: [Select]

DELIMITER //

DROP PROCEDURE IF EXISTS create_account;

CREATE PROCEDURE create_account (usrName VARCHAR(12), pssWd VARCHAR(32))
BEGIN
SET @shaPass := SHA1(CONCAT(UPPER(usrName),':',UPPER(pssWd)));
INSERT INTO account(`username`, `sha_pass_hash`, `expansion`) VALUES(usrName, @shaPass, 2);
SELECT @accID := `id` FROM account WHERE username = `usrName`;
INSERT INTO rbac_account_permissions(`accountId`, `permissionId`, `granted`, `realmId`) VALUES(@accId, 195, 1, -1);
END//

DELIMITER :
Stored Procedure #2 - Creates a player account for the specified expansion.

Code: [Select]

DELIMITER //

DROP PROCEDURE IF EXISTS create_account;

CREATE PROCEDURE create_account (usrName VARCHAR(12), pssWd VARCHAR(32), expacType INTEGER)
BEGIN
SET @shaPass := SHA1(CONCAT(UPPER(usrName),':',UPPER(pssWd)));
INSERT INTO account(`username`, `sha_pass_hash`, `expansion`) VALUES(usrName, @shaPass, expacType);
SELECT @accID := `id` FROM account WHERE username = `usrName`;
INSERT INTO rbac_account_permissions(`accountId`, `permissionId`, `granted`, `realmId`) VALUES(@accId, 195, 1, -1);
END//

DELIMITER :
Stored Procedure #3 - Creates an account, with the specified GM level on all realms, for the WotLK expansion.

Code: [Select]

DELIMITER //

DROP PROCEDURE IF EXISTS create_account;

CREATE PROCEDURE create_account (usrName VARCHAR(12), pssWd VARCHAR(32), rbacGMLevel INTEGER)
BEGIN
SET @shaPass := SHA1(CONCAT(UPPER(usrName),':',UPPER(pssWd)));
INSERT INTO account(`username`, `sha_pass_hash`, `expansion`) VALUES(usrName, @shaPass, 2);
SELECT @accID := `id` FROM account WHERE username = `usrName`;
INSERT INTO rbac_account_permissions(`accountId`, `permissionId`, `granted`, `realmId`) VALUES(@accId, rbacGMLevel, 1, -1);
END//

DELIMITER :
Stored Procedure #4 - Creates an account, with the specified GM level on all realms, for the specified expansion.

Code: [Select]

DELIMITER //

DROP PROCEDURE IF EXISTS create_account;

CREATE PROCEDURE create_account (usrName VARCHAR(12), pssWd VARCHAR(32), expacType INTEGER, rbacGMLevel INTEGER)
BEGIN
SET @shaPass := SHA1(CONCAT(UPPER(usrName),':',UPPER(pssWd)));
INSERT INTO account(`username`, `sha_pass_hash`, `expansion`) VALUES(usrName, @shaPass, expacType);
SELECT @accID := `id` FROM account WHERE username = `usrName`;
INSERT INTO rbac_account_permissions(`accountId`, `permissionId`, `granted`, `realmId`) VALUES(@accId, rbacGMLevel, 1, -1);
END//

DELIMITER :
I haven't tested procedures 2-4, but I don't see anything wrong with them so they should work. Just post below if they don't and I'll fix them up.

If you want to stay up-to-date with my latest tutorials or if you just want an easier way to view all of my tutorials and releases in one place then take a look at my blog.