[patch] GM Access per realm

[Auntie Mangos]

Here is a new version for actual release:http://getmangos.eu/community/viewtopic.php?id=9794

[charlie2025]

What bug does the patch fix? What features does the patch add?

Add 2 columns into account table. access_realm and gmlevel2. If access_realm is -1, gmlevel value is used at all realms, in other way, access_realm sign on which realm this "gmlevel" is used, on other realms is used gmlevel2.

For which SubVersion revision was the patch created?

Newest git revs.

Who has been writing this patch? Please include either forum user names or email addresses.

charlie

diff --git a/src/game/AccountMgr.cpp b/src/game/AccountMgr.cpp
index 459cdbe..b36e2a7 100644
--- a/src/game/AccountMgr.cpp
+++ b/src/game/AccountMgr.cpp
@@ -22,6 +22,7 @@
#include "Player.h"
#include "Policies/SingletonImp.h"
#include "Util.h"
+#include "Config/ConfigEnv.h"

#ifdef DO_POSTGRESQL
extern DatabasePostgre loginDatabase;
@@ -168,11 +169,16 @@ uint32 AccountMgr::GetId(std::string username)

uint32 AccountMgr::GetSecurity(uint32 acc_id)
{
-    QueryResult *result = loginDatabase.PQuery("SELECT gmlevel FROM account WHERE id = '%u'", acc_id);
+    QueryResult *result = loginDatabase.PQuery("SELECT gmlevel, gmlevel2, access_realm FROM account WHERE id = '%u'", acc_id);
    if(result)
-    {
-        uint32 sec = (*result)[0].GetUInt32();
-        delete result;
+    {   
+        uint32 realmID = sConfig.GetIntDefault("RealmID", 0);
+        uint32 sec;
+        if(realmID == (*result)[2].GetInt32() || (*result)[2].GetInt32() == -1)
+             sec = (*result)[0].GetUInt32();
+        else
+             sec = (*result)[1].GetUInt32();
+        delete result;
        return sec;
    }

SQL

ALTER TABLE `account` ADD `gmlevel2` tinyint(3) unsigned NOT NULL default '0'  AFTER `gmlevel`;
ALTER TABLE `account` ADD `access_realm` tinyint(3) unsigned NOT NULL default '-1'  AFTER `gmlevel2`;

[Endlesskiss]

I'm not going to use it but I think its a great patch for multi realm servers!

[tissle]

QueryResult *result = loginDatabase.PQuery("SELECT gmlevel, gmlevel2, access_realm FROM account WHERE id = '%u'", acc_id);

if(result)

{

uint32 realmID = sConfig.GetIntDefault("RealmID", 0);

uint32 sec;

if(realmID == (*result)[2].GetInt32() || (*result)[2].GetInt32() == -1)

sec = (*result)[0].GetUInt32();

else

sec = (*result)[1].GetUInt32();

delete result;

return sec;

}

I think `access_realm` should be a list of realms instead of just one. So you could have `access_realm` = "1,2,3" and if the user logged into realm 1,2,or 3, then they would get the gm level of `gmlevel`

Its still great as is. And I might give this patch a try.

[charlie2025]

I think `access_realm` should be a list of realms instead of just one. So you could have `access_realm` = "1,2,3" and if the user logged into realm 1,2,or 3, then they would get the gm level of `gmlevel`

Its still great as is. And I might give this patch a try.

try this.. I didn't tested it, only compiled fine. - access_realm are now numbers, divided by , .. so 1,2,3 etc.

diff --git a/src/game/AccountMgr.cpp b/src/game/AccountMgr.cpp
index 459cdbe..8938252 100644
--- a/src/game/AccountMgr.cpp
+++ b/src/game/AccountMgr.cpp
@@ -22,6 +22,7 @@
#include "Player.h"
#include "Policies/SingletonImp.h"
#include "Util.h"
+#include "Config/ConfigEnv.h"

#ifdef DO_POSTGRESQL
extern DatabasePostgre loginDatabase;
@@ -168,11 +169,38 @@ uint32 AccountMgr::GetId(std::string username)

uint32 AccountMgr::GetSecurity(uint32 acc_id)
{
-    QueryResult *result = loginDatabase.PQuery("SELECT gmlevel FROM account WHERE id = '%u'", acc_id);
+    QueryResult *result = loginDatabase.PQuery("SELECT gmlevel, gmlevel2, access_realm FROM account WHERE id = '%u'", acc_id);
    if(result)
-    {
-        uint32 sec = (*result)[0].GetUInt32();
-        delete result;
+    {   
+        uint32 realmID = sConfig.GetIntDefault("RealmID", 0); // Get current realmID
+        uint32 sec = 0; 
+        bool set = false;
+        char* splited;
+         
+         if((*result)[2].GetInt32() == -1)  // If access_realm is -1, set gmlevel as security
+             {
+                 sec = (*result)[0].GetUInt32();
+                 set = true;
+             }
+         else    // If no, check if current realm is in acces_realm
+             {   
+                 splited = strtok((char*)(*result)[2].GetCppString().c_str(),",");
+                 
+                 while (splited != NULL)
+                 {
+                     if(atoi(splited) == realmID)
+                         {
+                             sec = (*result)[0].GetUInt32();
+                             set = true;
+                         }
+                     splited = strtok(NULL,",");          
+                 }
+
+              }
+          if(!set)  // If didn't set anything, set gmlevel2 as security
+             sec = (*result)[1].GetUInt32();
+                 
+        delete result;
        return sec;
    }

[amki]

Would be a nice idea for people wanting to test their changes with others on a seperate realm, granting them only rights to a second realm.

To the implementation: Why don't you use positive and negative Values in the old `gmlevel` value if you just want to separate for two types of realms?

It would possibly be better to implement it in a way, every realm is able to have it's own accountlevel :lol:

[charlie2025]

Would be a nice idea for people wanting to test their changes with others on a seperate realm, granting them only rights to a second realm.

To the implementation: Why don't you use positive and negative Values in the old `gmlevel` value if you just want to separate for two types of realms?

It would possibly be better to implement it in a way, every realm is able to have it's own accountlevel :lol:

Becose if I would do gmevel and gmlevel2 columns, choise woul be only for two realms.. in way how is in first post, you can choose one gmlevel for one realm and for other realms other gmlevel.

In fourth post is patch, which allow to grant gmacces for more then two realms.

[alexluana]

I agree with the idea of a new table for acces realm rights....

a new table needed ..

like

realmd_accounts_acces

by default everyone is like in account table...

but ... if u want somebody else to be a mod for 1 of your realms

just have

userid realmnumber acces

1 5 2

etc....

Just my 2 cents thought...

Cheers

[charlie2025]

I agree with the idea of a new table for acces realm rights....

a new table needed ..

like

realmd_accounts_acces

by default everyone is like in account table...

but ... if u want somebody else to be a mod for 1 of your realms

just have

userid realmnumber acces

1 5 2

etc....

Just my 2 cents thought...

Cheers

As you wish.. :-D

diff --git a/src/game/AccountMgr.cpp b/src/game/AccountMgr.cpp
index 459cdbe..a7f3614 100644
--- a/src/game/AccountMgr.cpp
+++ b/src/game/AccountMgr.cpp
@@ -22,6 +22,7 @@
#include "Player.h"
#include "Policies/SingletonImp.h"
#include "Util.h"
+#include "Config/ConfigEnv.h"

#ifdef DO_POSTGRESQL
extern DatabasePostgre loginDatabase;
@@ -168,7 +169,9 @@ uint32 AccountMgr::GetId(std::string username)

uint32 AccountMgr::GetSecurity(uint32 acc_id)
{
-    QueryResult *result = loginDatabase.PQuery("SELECT gmlevel FROM account WHERE id = '%u'", acc_id);
+    uint32 realmID =  sConfig.GetIntDefault("RealmID", 0); // Get current realmID
+
+    QueryResult *result = loginDatabase.PQuery("SELECT gmlevel FROM account_access WHERE id = '%u' and realmid = '%u'", acc_id, realmID);
    if(result)
    {
        uint32 sec = (*result)[0].GetUInt32();

SQL for realmd

3[/size]

INSERT INTO `account_access` (`id`,`gmlevel`,`realmid`)  SELECT `acc`.`id`,`acc`.`gmlevel`,`rchar`.`realmid`  FROM `account` as `acc` inner join `realmcharacters` as `rchar` on `rchar`.`acctid` = `acc`.`id` where `rchar`.`numchars` >  0;

--  and maybe drop column gmlevel from account table

Now there are three versions of patch.. LOL

[kisjaksi]

hah im going to test the last one:P

[VladimirMangos]

Instead store realm level info in common for all realms realmd DB better add new table in characters DB and access similar character_tutorial that also per account per realm data.

Possible in like case common GM level in realmd set _max_ possible gm level for account

and per-realm setting set real gm level in specific realm.

In like case will be possible check second data only if first (global GM level > 0) and then avoid slowdown for normal player login.

In anycase this seldom used feature and then for including to mangos it don't must slowdown server work if not used for most oftent cases.

At this moment i not comment is this part good for

[charlie2025]

Instead store realm level info in common for all realms realmd DB better add new table in characters DB and access similar character_tutorial that also per account per realm data.

Possible in like case common GM level in realmd set _max_ possible gm level for account

and per-realm setting set real gm level in specific realm.

In like case will be possible check second data only if first (global GM level > 0) and then avoid slowdown for normal player login.

In anycase this seldom used feature and then for including to mangos it don't must slowdown server work if not used for most oftent cases.

At this moment i not comment is this part good for

Ok. I had also replaced all old calling of gmlevel from account table..

diff --git a/src/game/AccountMgr.cpp b/src/game/AccountMgr.cpp
index 459cdbe..97e2827 100644
--- a/src/game/AccountMgr.cpp
+++ b/src/game/AccountMgr.cpp
@@ -168,14 +168,14 @@ uint32 AccountMgr::GetId(std::string username)

uint32 AccountMgr::GetSecurity(uint32 acc_id)
{
-    QueryResult *result = loginDatabase.PQuery("SELECT gmlevel FROM account WHERE id = '%u'", acc_id);
+       QueryResult *result = CharacterDatabase.PQuery("SELECT gmlevel FROM account_access WHERE account_id = '%u' AND gmlevel > 0", acc_id);
    if(result)
    {
        uint32 sec = (*result)[0].GetUInt32();
        delete result;
        return sec;
    }
-
+ 
    return 0;
}

diff --git a/src/game/Level2.cpp b/src/game/Level2.cpp
index 8fa4b21..8d92a04 100644
--- a/src/game/Level2.cpp
+++ b/src/game/Level2.cpp
@@ -1804,13 +1804,15 @@ bool ChatHandler::HandlePInfoCommand(const char* args)
    uint32 security = 0;
    std::string last_login = GetMangosString(LANG_ERROR);

-    QueryResult* result = loginDatabase.PQuery("SELECT username,gmlevel,last_ip,last_login FROM account WHERE id = '%u'",accId);
+    QueryResult* result = loginDatabase.PQuery("SELECT username,last_ip,last_login FROM account WHERE id = '%u'",accId);
    if(result)
    {
        Field* fields = result->Fetch();
        username = fields[0].GetCppString();
-        security = fields[1].GetUInt32();
-
+    
+    QueryResult* securitysql = CharacterDatabase.PQuery("SELECT gmlevel FROM account_access WHERE account_id = '%i'",accId);
+    security = (*securitysql)[0].GetUInt32();
+ 
        if(!m_session || m_session->GetSecurity() >= security)
        {
            last_ip = fields[2].GetCppString();
diff --git a/src/game/Level3.cpp b/src/game/Level3.cpp
index 4e84316..8916bd9 100644
--- a/src/game/Level3.cpp
+++ b/src/game/Level3.cpp
@@ -737,7 +737,7 @@ bool ChatHandler::HandleAccountSetGmLevelCommand(const char* args)
    }

    PSendSysMessage(LANG_YOU_CHANGE_SECURITY, targetAccountName.c_str(), gm);
-    loginDatabase.PExecute("UPDATE account SET gmlevel = '%i' WHERE id = '%u'", gm, targetAccountId);
+    CharacterDatabase.PExecute("UPDATE account_access SET gmlevel = '%i' WHERE account_id = '%u'", gm, targetAccountId);

    return true;
}
@@ -6006,7 +6006,7 @@ bool ChatHandler::HandleInstanceSaveDataCommand(const char * /*args*/)
bool ChatHandler::HandleGMListFullCommand(const char* /*args*/)
{
    ///- Get the accounts with GM Level >0
-    QueryResult *result = loginDatabase.Query( "SELECT username,gmlevel FROM account WHERE gmlevel > 0" );
+    QueryResult *result = CharacterDatabase.Query( "SELECT account_id, gmlevel FROM account_access WHERE gmlevel > 0" );
    if(result)
    {
        SendSysMessage(LANG_GMLIST);
@@ -6018,7 +6018,11 @@ bool ChatHandler::HandleGMListFullCommand(const char* /*args*/)
        do
        {
            Field *fields = result->Fetch();
-            PSendSysMessage("|%15s|%6s|", fields[0].GetString(),fields[1].GetString());
+                 
+            QueryResult *result2 = loginDatabase.PQuery("SELECT username FROM account WHERE id = '%u'",fields[0].GetUInt32() );
+            if(result2)
+                PSendSysMessage("|%15s|%6s|", (*result)[0].GetCppString().c_str() ,fields[1].GetString());
+
        }while( result->NextRow() );

        PSendSysMessage("========================");
diff --git a/src/game/WorldSocket.cpp b/src/game/WorldSocket.cpp
index fdf1d5e..2bade9c 100644
--- a/src/game/WorldSocket.cpp
+++ b/src/game/WorldSocket.cpp
@@ -43,6 +43,7 @@
#include "WorldSocketMgr.h"
#include "Log.h"
#include "WorldLog.h"
+#include "AccountMgr.h"

#if defined( __GNUC__ )
#pragma pack(1)
@@ -684,16 +685,15 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)
    QueryResult *result =
          loginDatabase.PQuery ("SELECT "
                                "id, " //0
-                                "gmlevel, " //1
-                                "sessionkey, " //2
-                                "last_ip, " //3
-                                "locked, " //4
-                                "sha_pass_hash, " //5
-                                "v, " //6
-                                "s, " //7
-                                "expansion, " //8
-                                "mutetime, " //9
-                                "locale " //10
+                                "sessionkey, " //1
+                                "last_ip, " //2
+                                "locked, " //3
+                                "sha_pass_hash, " //4
+                                "v, " //5
+                                "s, " //6
+                                "expansion, " //7
+                                "mutetime, " //8
+                                "locale " //9
                                "FROM account "
                                "WHERE username = '%s'",
                                safe_account.c_str ());
@@ -712,11 +712,11 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)

    Field* fields = result->Fetch ();

-    expansion = fields[8].GetUInt8 () && sWorld.getConfig (CONFIG_EXPANSION) > 0;
+    expansion = fields[7].GetUInt8 () && sWorld.getConfig (CONFIG_EXPANSION) > 0;

    N.SetHexStr ("894B645E89E1535BBDAD5B8B290650530801B18EBFBF5E8FAB3C82872A3E9BB7");
    g.SetDword (7);
-    I.SetHexStr (fields[5].GetString ());
+    I.SetHexStr (fields[4].GetString ());

    //In case of leading zeros in the I hash, restore them
    uint8 mDigest[sHA_DIGEST_LENGTH];
@@ -727,7 +727,7 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)

    std::reverse (mDigest, mDigest + SHA_DIGEST_LENGTH);

-    s.SetHexStr (fields[7].GetString ());
+    s.SetHexStr (fields[6].GetString ());
    sha1.UpdateData (s.AsByteArray (), s.GetNumBytes ());
    sha1.UpdateData (mDigest, SHA_DIGEST_LENGTH);
    sha1.Finalize ();
@@ -736,7 +736,7 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)

    const char* sStr = s.AsHexStr (); //Must be freed by OPENSSL_free()
    const char* vStr = v.AsHexStr (); //Must be freed by OPENSSL_free()
-    const char* vold = fields[6].GetString ();
+    const char* vold = fields[5].GetString ();

    DEBUG_LOG ("WorldSocket::HandleAuthSession: (s,v) check s: %s v_old: %s v_new: %s",
                sStr,
@@ -767,9 +767,9 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)
    OPENSSL_free ((void*) vStr);

    ///- Re-check ip locking (same check as in realmd).
-    if (fields[4].GetUInt8 () == 1) // if ip is locked
+    if (fields[3].GetUInt8 () == 1) // if ip is locked
    {
-        if (strcmp (fields[3].GetString (), GetRemoteAddress ().c_str ()))
+        if (strcmp (fields[2].GetString (), GetRemoteAddress ().c_str ()))
        {
            packet.Initialize (SMSG_AUTH_RESPONSE, 1);
            packet << uint8 (AUTH_FAILED);
@@ -782,12 +782,15 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)
    }

    id = fields[0].GetUInt32 ();
-    security = fields[1].GetUInt16 ();
+
+    QueryResult* securitysql = CharacterDatabase.PQuery("SELECT gmlevel FROM account_access WHERE account_id = '%i'",id);
+    security = (*securitysql)[0].GetUInt32();
+
    K.SetHexStr (fields[2].GetString ());

-    time_t mutetime = time_t (fields[9].GetUInt64 ());
+    time_t mutetime = time_t (fields[8].GetUInt64 ());

-    locale = LocaleConstant (fields[10].GetUInt8 ());
+    locale = LocaleConstant (fields[9].GetUInt8 ());
    if (locale >= MAX_LOCALE)
        locale = LOCALE_enUS;

diff --git a/src/mangosd/CliRunnable.cpp b/src/mangosd/CliRunnable.cpp
index 77d87fc..08cab8b 100644
--- a/src/mangosd/CliRunnable.cpp
+++ b/src/mangosd/CliRunnable.cpp
@@ -191,14 +191,14 @@ bool ChatHandler::HandleAccountOnlineListCommand(const char* args)

        ///- Get the username, last IP and GM level of each account
        // No SQL injection. account is uint32.
-        //                                                      0         1        2        3
-        QueryResult *resultLogin = loginDatabase.PQuery("SELECT username, last_ip, gmlevel, expansion FROM account WHERE id = '%u'",account);
+        //                                                      0          1         2
+        QueryResult *resultLogin = loginDatabase.PQuery("SELECT username, last_ip expansion FROM account WHERE id = '%u'",account);

        if(resultLogin)
        {
            Field *fieldsLogin = resultLogin->Fetch();
            PSendSysMessage("|%15s| %20s | %15s |%4d|%5d|",
-                fieldsLogin[0].GetString(),name.c_str(),fieldsLogin[1].GetString(),fieldsLogin[2].GetUInt32(),fieldsLogin[3].GetUInt32());
+                fieldsLogin[0].GetString(),name.c_str(),fieldsLogin[1].GetString(),fieldsLogin[2].GetUInt32());

            delete resultLogin;
        }
diff --git a/src/mangosd/RASocket.cpp b/src/mangosd/RASocket.cpp
index f3bdf4f..59e28ba 100644
--- a/src/mangosd/RASocket.cpp
+++ b/src/mangosd/RASocket.cpp
@@ -151,8 +151,10 @@ void RASocket::OnRead()

                    ///- Escape the Login to allow quotes in names
                    loginDatabase.escape_string(login);
-
-                    QueryResult* result = loginDatabase.PQuery("SELECT gmlevel FROM account WHERE username = '%s'",login.c_str());
+                    
+                    QueryResult* accid = loginDatabase.PQuery("SELECT id FROM account WHERE username = '%s'", login.c_str());
+                    
+                    QueryResult* result = CharacterDatabase.PQuery("SELECT gmlevel FROM account_access WHERE account_id = '%u'", (*accid)[0].GetUInt32());

                    ///- If the user is not found, deny access
                    if(!result)

sql

3[/size]

INSERT INTO `account_access` (`account_id`,`gmlevel`)  SELECT `id`,`gmlevel` FROM `realmd`.`account`;

-- Drop gmlevel from account table

[VladimirMangos]

Last suggested version drop som eusefull functionality (like show gm level in online list)

and will not correctly work in result not updated realmd code.

Table can't be in character DB and _required_ realmв id field as i write early.

I will post rewrited patch.

[VladimirMangos]

This is http://paste2.org/p/111951

ALTER TABLE `realmcharacters`
 ADD COLUMN `gmlevel` tinyint(3) unsigned NOT NULL default '0' AFTER `acctid`;
UPDATE TABLE `realmcharacters`
 SET `realmcharacters`.`gmlevel` = `account`.`gmlevel` WHERE `account`.`id` = `realmcharacters`.`acctid`;

ALTER TABLE `account`
 DROP COLUMN `gmlevel`;

Also not tested. Adding this feature will slowdown server so i not sure that this good for adding.

[briskcz]

Hello, I would like to ask whether someone you can not update the patch files to the current revision mangos pls?

[karol]

its needed feature for multirealms servers well the possible slow down... what about config option to completly ignore it by mangos? for ppl that dont need it. or some code workout to make it harmless

[donline]

Hello

I´m need access gm

pls update

thank you

[naxpweb]

This is http://paste2.org/p/111951

ALTER TABLE `realmcharacters`
 ADD COLUMN `gmlevel` tinyint(3) unsigned NOT NULL default '0' AFTER `acctid`;
UPDATE TABLE `realmcharacters`,
 SET `realmcharacters`.`gmlevel` = `account`.`gmlevel` WHERE `account`.`id` = `realmcharacters`.`acctid`;

ALTER TABLE `account`
 DROP COLUMN `gmlevel`;

Also not tested. Adding this feature will slowdown server so i not sure that this good for adding.

Im getting a syntax error when running the above query other than that , the performance does not seem to be affected , i had to manually assign the gm levels since I could not figure out what was wrong with the sql above , I thinking somethings out of date but its probably something silly I just dont see it

Other than that works like a charm except in the case you ahve a realm locked to a certain level , it will only allow you in if all your characters on all realms have gm levels...

Any updates would be appreciated.

[VladimirMangos]

UPDATE TABLE `realmcharacters`, <---- redundent comma

[yu_raider]

Vlad, this patch of yours won't work as it should. I've setup 2 realms to test and both realms take gm rights from the same realm. Sometimes they take gm rights from realm 1, sometimes from realm 2. Looks like there is a problem with resolving realmid. And some commands like .pinfo do not work.

[yu_raider]

It looks that the problem comes from this line under src/realmd/AuthSocket.cpp:

result = dbRealmServer.PQuery( "SELECT gmlevel, numchars FROM realmcharacters WHERE realmid = '%d' AND acctid='%u'", i->second.m_ID, id);

Since it is located under for cycle, it seems that i takes a wrong realm id value and therefore gm rights are randomly selected from one of the realms.

[sanzzes120]

problem with this patch i cant delet gmlevel in account my realmlist wouldnt start

[Method]

could anyone renew this patch for latest revisions? :-)

[sanzzes120]

could anyone renew this patch for latest revisions? :-)

yeaahh don`t work more erros as fun

[OSIRIS]

i think this solution is not needed.