[PATCH] realm specific GM permissions

[Auntie Mangos]

http://getmangos.eu/community/viewtopic.php?id=9794

that thread gave me the idea on what needed to be done, and where it needed to be done at. but the code in the thread is incorrect, permissions set in the account_forcepermission table aren't being used ingame on the listed realm(s). also, you were limited to one forced permission per account even if you had multiple realms

code to create the table in your login database with a couple changes to the original one linked above:

CREATE TABLE `account_forcepermission` (
 `AccountID` int(11) unsigned NOT NULL DEFAULT '0',
 `realmID` int(3) unsigned NOT NULL DEFAULT '0',
 `Security` int(3) unsigned NOT NULL DEFAULT '0',
 `Comment` varchar(32) NOT NULL DEFAULT '',
 PRIMARY KEY (`AccountID`,`realmID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

hope this fulfills the need for ya'll as it does for me

*updated for 8971*

http://pastebin.org/63648

pasted the updated code into pastebin

force_permission.patch

fixed:

• * players getting admin permissions if they don't have a permission forced on the realm
  * allowed you to not have to set "regular permissions" in the forced permission table for anyone that you've forced a permission for
  

[yasuno]

You are awesome, ill be implementing/testing this later today.

[putra]

just noticed something with it that i didn't realize earlier

on the select statements to the login database it's requiring the realmid, meaning that if you don't have anything set in the forced permissions table for that realm you can't log onto the account due to it not finding the information and you get a "WorldSocket::HandleAuthSession: Sent Auth Response (unknown account)." error

i edited my original post with the correct code, making it check the realm id directly before applying the forced permissions instead of in the select statements

[yasuno]

Alright thanks, with the recent changed to the core though does this patch still work??

[fikimichal]

i have newest mangos and patch not work

[yasuno]

Putra are you still working on this?

Edit: I dont see why this isnt implemented by default and written into the core in the first place. Its not like Retail gms are a gm on what ever realm they feel like Gm'n on today. I mean it makes since to "assign" people to there realm to work not to let there permission roam freely, that only makes since for an administrator. And not to bash D: but almost every core has this function built in but us. X.x

And dont attack me with you shouldn't have a public server anyways, Even if it is just a lan or whatever, or for you and your friends my statement still makes since that on retail there most likely assigned not just roaming freely. That is blizz like after all.

[putra]

yeah sorry about not answering sooner, i've been sorta busy at my work during the day and finishing up a complete conversion from aspire to mangos for 2 realms that are over 2 1/2 years old at nights/weekends

anyway, i noticed after some extra testing a few weeks ago on it that it was applying whatever permission was the last forced permission (if it was 1, then everyone that didn't already have a forced permission assigned in that table would get 1 for example) and noticed why

you'd need to change

level2.cpp

        if( fields[4].GetUInt32() != NULL )                    // checking to see if there is any data in the account_forcepermission for account
           if( fields[6].GetUint32() == realmID )            // checks to see if there are forced permissions on the realm
               security = fields[5].GetUInt16();            // sets forced permissions

and

worldsocket.cpp

    if ( fields[10].GetUInt32 () != NULL )                    // check to see if data is in account_forcepermission table for account
       if ( fields[11].GetUint32 () == realmID )            // check to see if the realm has any forced permissions
           security = fields[12].GetUInt16 ();                // set forced permission if so

to have {}'s around the 2nd if statement in each one, making it

level2.cpp

        if( fields[4].GetUInt32() != NULL )                    // checking to see if there is any data in the account_forcepermission for account
       {
           if( fields[6].GetUint32() == realmID )            // checks to see if there are forced permissions on the realm
               security = fields[5].GetUInt16();            // sets forced permissions
       }

and

worldsocket.cpp

    if ( fields[10].GetUInt32 () != NULL )                    // check to see if data is in account_forcepermission table for account
   {
       if ( fields[11].GetUint32 () == realmID )            // check to see if the realm has any forced permissions
           security = fields[12].GetUInt16 ();                // set forced permission if so
   }

after changing those 4 lines i haven't had any problems at all, though i'm not entirely sure if anything has changed in the authentication in the past couple weeks as i haven't even had time to look at all updates that have been done (i know i've got a bit of catching up to do, i'm running revision 8720 and mangos is on revision 8862 ). if the authentication handlers haven't changed any, then all you have to do is manually type in the code (copy and paste each line ftw right?)

and as yasuno says, blizzard gm's don't have commands on every server that's up. they're actually forbidden from playing on the realms they gm on as a safety/precautionary measure on blizzard's behalf. instead, they're assigned to a battlegroup. in the private server community with the way server mapping is handled a battlegroup is essentially a specific realm on that server, so a server with multiple realms basically has multiple battlegroups . also with the way the code i wrote handles things, accounts only have their permissions force changed on a realm if they are in that table in your logon database and putting data in that table is even optional when assigning permissions.

[yasuno]

Alright so i should apply the patch like normal, and also change the lines of code you mentioned in the above post right?

[Thyros]

Alright so i should apply the patch like normal, and also change the lines of code you mentioned in the above post right?

i have an better version

[yasuno]

Then why not share it? =[

[bloutix]

i have an better version

Please share your better patch if exist

Best regards

[yasuno]

They probably wont post it, or they don't even have one not sure lol. I hate when people post useless comments and reply's no one cares what you have if your not willing to help or share it. Whats the point of bringing it up, no one likes a wanna be gloater.

[Feel the Power]

Here are a few misprints.

+        {
+            if( fields[6].GetU[color="Red"]I[/color]nt32() == realmID )            // checks to see if there are forced permissions on the realm
+                security = fields[5].GetUInt16();            // sets forced permissions
+        }

+    {
+        if ( fields[11].GetU[color="Red"]I[/color]nt32 () == realmID )            // check to see if the realm has any forced permissions
+            security = fields[12].GetUInt16 ();                // set forced permission if so
+    }

It was GetUint32 instead of GetUInt32.

And I also corrected the mistake that I had in sql query during the login.

"[color="Red"]ON a_fp.AccountID = a.id[/color] WHERE [color="Red"]`[/color]username[color="Red"]`[/color] = '%s'  "

Here's the full patch. It's works for me at 8887.

From e460a2939ec40bab15a67c96e25944a59fe38c0b Mon Sep 17 00:00:00 2001
From: unknown <Feel_the_Power@.(none)>
Date: Mon, 30 Nov 2009 15:39:13 +0200
Subject: [PATCH] realm account permission

---
sql/realm_account_permission.sql |    7 +++++++
src/game/Level2.cpp              |   18 ++++++++++++++----
src/game/WorldSocket.cpp         |   38 +++++++++++++++++++++++++-------------
3 files changed, 46 insertions(+), 17 deletions(-)
create mode 100644 sql/realm_account_permission.sql

diff --git a/sql/realm_account_permission.sql b/sql/realm_account_permission.sql
new file mode 100644
index 0000000..e96b393
--- /dev/null
+++ b/sql/realm_account_permission.sql
@@ -0,0 +1,7 @@
+CREATE TABLE `account_forcepermission` (
+  `AccountID` int(11) unsigned NOT NULL DEFAULT '0',
+  `realmID` int(3) unsigned NOT NULL DEFAULT '0',
+  `Security` int(3) unsigned NOT NULL DEFAULT '0',
+  `Comment` varchar(32) NOT NULL DEFAULT '',
+  PRIMARY KEY (`AccountID`,`realmID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
\\ No newline at end of file
diff --git a/src/game/Level2.cpp b/src/game/Level2.cpp
index f928709..200a5c3 100644
--- a/src/game/Level2.cpp
+++ b/src/game/Level2.cpp
@@ -42,6 +42,7 @@
#include <fstream>
#include <map>
#include "GlobalEvents.h"
+#include "Config/ConfigEnv.h"

#include "TargetedMovementGenerator.h"                      // for HandleNpcUnFollowCommand

@@ -2122,7 +2123,8 @@ bool ChatHandler::HandlePInfoCommand(const char* args)
    uint32 total_player_time = 0;
    uint32 level = 0;
    uint32 latency = 0;
-
+    uint32 realmID = sConfig.GetIntDefault("RealmId", 0);
+    
    // get additional information from Player object
    if(target)
    {
@@ -2160,13 +2162,21 @@ bool ChatHandler::HandlePInfoCommand(const char* args)
    std::string last_ip = GetMangosString(LANG_ERROR);
    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 a.username, a.gmlevel, a.last_ip, a.last_login, a_fp.accountid, a_fp.security, a_fp.realmid FROM account AS a LEFT JOIN account_forcepermission AS a_fp on a.id = a_fp.accountid WHERE a.id = '%u'", accId);
    if(result)
    {
        Field* fields = result->Fetch();
        username = fields[0].GetCppString();
-        security = fields[1].GetUInt32();
+
+        if( fields[4].GetUInt32() != NULL )                    // checking to see if there is any data in the account_forcepermission for account
+    {
+      if( fields[6].GetUInt32() == realmID )            // checks to see if there are forced permissions on the realm
+                security = fields[5].GetUInt16();            // sets forced permissions
+        }
+        else
+      security = fields[1].GetUInt32();                // otherwise sets account permissions
+ 

        if(!m_session || m_session->GetSecurity() >= security)
        {
diff --git a/src/game/WorldSocket.cpp b/src/game/WorldSocket.cpp
index f93804e..e20474e 100644
--- a/src/game/WorldSocket.cpp
+++ b/src/game/WorldSocket.cpp
@@ -767,18 +767,22 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)

    QueryResult *result =
          loginDatabase.PQuery ("SELECT "
-                                "id, "                      //0
-                                "gmlevel, "                 //1
-                                "sessionkey, "              //2
-                                "last_ip, "                 //3
-                                "locked, "                  //4
-                                "v, "                       //5
-                                "s, "                       //6
-                                "expansion, "               //7
-                                "mutetime, "                //8
-                                "locale "                   //9
-                                "FROM account "
-                                "WHERE username = '%s'",
+                                "a.id, "                      //0
+                                "a.gmlevel, "                 //1
+                                "a.sessionkey, "              //2
+                                "a.last_ip, "                 //3
+                                "a.locked, "                  //4
+                                "a.v, "                       //5
+                                "a.s, "                       //6
+                                "a.expansion, "               //7
+                                "a.mutetime, "                //8
+                                "a.locale, "                  //9
+                                "a_fp.accountid, "              //10
+                                "a_fp.realmID, "              //11
+                                "a_fp.security "              //12
+                                "FROM account AS a "
+                                "LEFT JOIN account_forcepermission AS a_fp "
+                                "ON a_fp.AccountID = a.id WHERE `username` = '%s'  ",
                                safe_account.c_str ());

    // Stop if the account is not found
@@ -829,7 +833,15 @@ int WorldSocket::HandleAuthSession (WorldPacket& recvPacket)
    }

    id = fields[0].GetUInt32 ();
-    security = fields[1].GetUInt16 ();
+
+    if ( fields[10].GetUInt32 () != NULL )                    // check to see if data is in account_forcepermission table for account
+    {
+        if ( fields[11].GetUInt32 () == realmID )            // check to see if the realm has any forced permissions
+            security = fields[12].GetUInt16 ();                // set forced permission if so
+    }
+    else
+        security = fields[1].GetUInt16 ();                    // otherwise set the account's permission
+
    if(security > SEC_ADMINISTRATOR)                        // prevent invalid security settings in DB
        security = SEC_ADMINISTRATOR;

[yasuno]

Oh alright sweet, thanks man ^

:]

[putra]

And I also corrected the mistake that I had in sql query during the login.

"[color="Red"]ON a_fp.AccountID = a.id[/color] WHERE [color="Red"]`[/color]username[color="Red"]`[/color] = '%s'  "

how is this correct over what i had mate? it's doing a left join between account (table 1, primary table that has to have data) and account_forcepermission (table 2, secondary table that doesn't have to have data). correct me if i'm wrong, but when doing a left join it pulls it from the one on the left of the = in your ON... the table that has to have the data to begin with.... which in the case of what i had would be the account table and in the case of what you have is the account_forcepermission table

granted, i haven't done joins on a daily basis since my oracle cource back in highschool until the past few months since i haven't had a real need for joins until i started working on trying to get the forced permission functionality back

i even checked out w3's stuff to see if i'm correct in what i said:

http://www.w3schools.com/sql/sql_join_left.asp

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

edit - also, i've noticed that if you have more than 1 realm with forced permissions on an account that the code will completely ignore your 2nd, 3rd, 4th, etc realm's specific permissions and assign the account to have whatever your administrator level is... that it's only using the first record returned if there are more than 1 on an account.

of course you can't add in on the where condition to check for the realm's id when it's pulling the authentication data, since that will make the account "not exist" for any account that doesn't have a forced permission on that realm

since it only reads the first record returned on the sql (within the 12 array elements) you can order the sql by the realm id retrieved from the config files:

 order by field(a_fp.realmid,#) desc;

replace the # with '%u' and make it use the realm number given from the config and that will make the realm you're trying to log into with a forced account be the first record. i'll do some code testing tonight when i get home from work to see if that will actually work correctly with it, the sql returns the values in the order that we'd want it but that doesn't necessarily mean that the c++ will interpret it the way we want it.

[Feel the Power]

how is this correct over what i had mate? it's doing a left join between account (table 1, primary table that has to have data) and account_forcepermission (table 2, secondary table that doesn't have to have data). correct me if i'm wrong, but when doing a left join it pulls it from the one on the left of the = in your ON... the table that has to have the data to begin with.... which in the case of what i had would be the account table and in the case of what you have is the account_forcepermission table

granted, i haven't done joins on a daily basis since my oracle cource back in highschool until the past few months since i haven't had a real need for joins until i started working on trying to get the forced permission functionality back

i even checked out w3's stuff to see if i'm correct in what i said:

http://www.w3schools.com/sql/sql_join_left.asp

edit - also, i've noticed that if you have more than 1 realm with forced permissions on an account that the code will completely ignore your 2nd, 3rd, 4th, etc realm's specific permissions and assign the account to have whatever your administrator level is... that it's only using the first record returned if there are more than 1 on an account.

of course you can't add in on the where condition to check for the realm's id when it's pulling the authentication data, since that will make the account "not exist" for any account that doesn't have a forced permission on that realm

since it only reads the first record returned on the sql (within the 12 array elements) you can order the sql by the realm id retrieved from the config files:

 order by field(a_fp.realmid,#) desc;

replace the # with '%u' and make it use the realm number given from the config and that will make the realm you're trying to log into with a forced account be the first record. i'll do some code testing tonight when i get home from work to see if that will actually work correctly with it, the sql returns the values in the order that we'd want it but that doesn't necessarily mean that the c++ will interpret it the way we want it.

I had errors in the console and so I added it to the query.

"ON a_fp.AccountID = a.id WHERE `username` = '%s'  "

I do not have sufficient knowledge of SQL, so do not quite understand you. Please show how to be more correct.

[putra]

what i was thinking yesterday with that order by clause worked, it's putting the permission for that realm if there are multiple on an account as the first record returned. the sql can probably be cleaned up a little more by limiting it to return only a single row but it works. testing yesterday i also added in a couple lines to make it so that you don't have to assign the account's gmlevel if you don't have any forced on the realm they're trying to log into but do on other realms. i'll be busy tonight and tomorrow as well, but i'll post the code changes on sunday for ya'll to see what i'm talking about here

I had errors in the console and so I added it to the query.

"ON a_fp.AccountID = a.id WHERE `username` = '%s'  "

I do not have sufficient knowledge of SQL, so do not quite understand you. Please show how to be more correct.

having

"ON a.id = a_fp.AccountID where `username` = '%s' "

would be the correct way, the reason you'd probably be getting errors in your console over it is if someone doesn't have permissions being forced. if they don't have permissions being forced they'd just have a NULL value being returned for the 3 values that are in the account_forcepermission table and an int value can't equal NULL as you can guess. though this has no impact on data being returned.

a quick way to see what i mean, is to open up your preferred database editor (navicat, sqlyog, command line, etc) and use the following as your query:

SELECT a.id, a.gmlevel, a.sessionkey, a.last_ip, a.locked, a.v, a.s, a.expansion, a.mutetime, a.locale, a_fp.accountid, a_fp.realmID, a_fp.security FROM account as a LEFT JOIN account_forcepermission as a_fp ON a.id = a_fp.AccountId WHERE a.username = '%s' ORDER BY FIELD(a_fp.realmid,'%u') DESC

replace %s (where clause) with a username that doesn't have a forced permission in the table, and replace %u (order by clause) with an id from one of your realms. if someone has permissions being forced upon multiple realms then you can see how it will be returned by replacing the %u with a specific realm id (all that will happen is that realm's specific permissions for the user will be first in the list).

[yasuno]

So... Can you update the first patch in your first post with the proper working one because with all these posts every where im confused sadly.. X.x

[putra]

*updated for 8971*

http://pastebin.org/63648

pasted the updated code into pastebin

force_permission.patch

fixed:

• * players getting admin permissions if they don't have a permission forced on the realm
  * allowed you to not have to set "regular permissions" in the forced permission table for anyone that you've forced a permission for
  

i made the sql pull the record from the account_forcepermission table that contains the realmid of the realm you are logging in first putting the sql return fields into the proper array elements solving the issues listed above

put the code on pastebin, and uploaded the patch file to filebeam (if needed, i can put the patch file onto my rapidshare so it can have a more permanent home). i made the patch file using revision 8971. if you are having problems applying the patch through git bash, just look at the changes in code in pastebin

[yasuno]

I will try this and let you know how it goes...

Im glad i havent tried it yet i would have had people running around as admins thats a catastrophe waiting to happen lol.

[putra]

I will try this and let you know how it goes...

Im glad i havent tried it yet i would have had people running around as admins thats a catastrophe waiting to happen lol.

hehe yeah, sadly i had to find it out about my code the hard way but it's been running live for the past few days now and there haven't been any incidents at all happening (i also did semi-extensive testing on it on this last change with the order of values returned)

[yasuno]

So you haven't had any problems or hiccups with the current patch i suppose?

[putra]

So you haven't had any problems or hiccups with the current patch i suppose?

that would be correct, i've had the current one running live with people actually on the realms and haven't had any issues at all with it....all the hiccups seem to have been worked out

[yasuno]

Alright, thats awesome to know then.

Thanks Putra for working this out. :]

Once i apply it, ill let you know how it works out.

[BloodLord]

Any recent changes?