This is more of a proof of concept. I never automated this.
Steps to reproduce
- Find a newuser log entry of a globally suppressed user.
There are probably multiple ways of doing this. One way is to use the log on loginwiki:
MariaDB [loginwiki_p]> select * from logging where log_deleted>7 order by log_id desc limit 10; +----------+----------+------------+----------------+-----------+---------------+-----------+----------------+------------+-------------+----------+ | log_id | log_type | log_action | log_timestamp | log_actor | log_namespace | log_title | log_comment_id | log_params | log_deleted | log_page | +----------+----------+------------+----------------+-----------+---------------+-----------+----------------+------------+-------------+----------+ | 33790398 | newusers | NULL | 20211005205654 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33789838 | newusers | NULL | 20211005194103 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33780782 | newusers | NULL | 20211004221045 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33723340 | newusers | NULL | 20210928131321 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33718768 | newusers | NULL | 20210928011022 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33702716 | newusers | NULL | 20210926071222 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33700891 | newusers | NULL | 20210925233523 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33648877 | newusers | NULL | 20210920081317 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33619486 | newusers | NULL | 20210916190637 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | | 33619478 | newusers | NULL | 20210916190554 | 0 | NULL | NULL | 11 | NULL | 13 | NULL | +----------+----------+------------+----------------+-----------+---------------+-----------+----------------+------------+-------------+----------+ 10 rows in set (0.32 sec) MariaDB [loginwiki_p]>
Let's choose 33619486 for this example.
- Find the global user id of that suppressed user.
We need to trick a little bit here. A way of doing this is to first get the context of that log entry. What I mean is basically to find out which user got created before and/or after that globally suppressed user. For this this example:
MariaDB [loginwiki_p]> select * from logging where log_id between 33619481 and 33619491 order by log_id desc;
+----------+----------+------------+----------------+-----------+---------------+----------------+----------------+-----------------------------------+-------------+----------+
| log_id | log_type | log_action | log_timestamp | log_actor | log_namespace | log_title | log_comment_id | log_params | log_deleted | log_page |
+----------+----------+------------+----------------+-----------+---------------+----------------+----------------+-----------------------------------+-------------+----------+
| 33619491 | newusers | autocreate | 20210916190717 | 34171769 | 2 | Estebanibou | 11 | a:1:{s:9:"4::userid";i:71853295;} | 0 | 0 |
| 33619490 | newusers | autocreate | 20210916190659 | 34171768 | 2 | SmartINFS315 | 11 | a:1:{s:9:"4::userid";i:71853288;} | 0 | 0 |
| 33619489 | newusers | autocreate | 20210916190648 | 34171767 | 2 | Jazzfan15 | 11 | a:1:{s:9:"4::userid";i:71853287;} | 0 | 0 |
| 33619488 | newusers | autocreate | 20210916190647 | 34171766 | 2 | Jgolf35 | 11 | a:1:{s:9:"4::userid";i:71853286;} | 0 | 0 |
| 33619487 | newusers | autocreate | 20210916190640 | 34171765 | 2 | MStearns21 | 11 | a:1:{s:9:"4::userid";i:71853285;} | 0 | 0 |
| 33619486 | newusers | NULL | 20210916190637 | 0 | NULL | NULL | 11 | NULL | 13 | NULL |
| 33619485 | newusers | autocreate | 20210916190637 | 34171763 | 2 | Vip_kuatrai | 11 | a:1:{s:9:"4::userid";i:71853282;} | 0 | 0 |
| 33619484 | newusers | autocreate | 20210916190637 | 34171762 | 2 | Informacionvip | 11 | a:1:{s:9:"4::userid";i:71853277;} | 0 | 0 |
| 33619483 | newusers | autocreate | 20210916190636 | 34171761 | 2 | Deedo7 | 11 | a:1:{s:9:"4::userid";i:71853272;} | 0 | 0 |
| 33619482 | newusers | autocreate | 20210916190630 | 34171760 | 2 | Bluehairedb7 | 11 | a:1:{s:9:"4::userid";i:71853267;} | 0 | 0 |
| 33619481 | newusers | autocreate | 20210916190629 | 34171759 | 2 | Efe_Çelebi | 11 | a:1:{s:9:"4::userid";i:71853266;} | 0 | 0 |
+----------+----------+------------+----------------+-----------+---------------+----------------+----------------+-----------------------------------+-------------+----------+
11 rows in set (0.00 sec)
MariaDB [loginwiki_p]>The globally suppressed user is clearly visible in the middle. We know take the user that has been created before the globally suppressed user has been created, Vip kuatrai. Now we abuse the fact that global user ids are incremental (Note, however, that we have several wikis and so it will not be the exact same order as in the loginwiki), so the global user id of the globally suppressed user should be within a small range of the global user id of Vip kuatrai. Let's get the global user id of Vip kuatrai.
MariaDB [centralauth_p]> select gu_id, gu_name from globaluser where gu_name="Vip kuatrai"; +----------+-------------+ | gu_id | gu_name | +----------+-------------+ | 67601590 | Vip kuatrai | +----------+-------------+ 1 row in set (0.01 sec) MariaDB [centralauth_p]>
Now let's take a look of a range of global user ids around 67601590.
MariaDB [centralauth_p]> select gu_id, gu_name from globaluser where gu_id between 67601585 and 67601595; +----------+----------------------------+ | gu_id | gu_name | +----------+----------------------------+ | 67601585 | عمر تقي الرسام | | 67601586 | Alebrex99 | | 67601587 | Bluehairedb7 | | 67601588 | Efe Çelebi | <------- Note that 67601589 is missing | 67601590 | Vip kuatrai | | 67601591 | Deedo7 | | 67601592 | Informacionvip | | 67601593 | MStearns21 | | 67601594 | Jazzfan15 | | 67601595 | Jgolf35 | +----------+----------------------------+ 10 rows in set (0.00 sec) MariaDB [centralauth_p]>
We can see that 67601589 must be the global user id of the globally suppressed user since it is missing here.
- Find out the username of the globally suppressed user from the global user id (the actual issue here)
MariaDB [centralauth_p]> select * from localuser where lu_global_id=67601589; +------------+------------+-----------------------+--------------------+-------------+--------------+ | lu_wiki | lu_name | lu_attached_timestamp | lu_attached_method | lu_local_id | lu_global_id | +------------+------------+-----------------------+--------------------+-------------+--------------+ | enwiki | <redacted> | 20210916190628 | new | 42513886 | 67601589 | | loginwiki | <redacted> | 20210916190637 | login | 71853284 | 67601589 | | metawiki | <redacted> | 20210916190640 | login | 33138860 | 67601589 | | simplewiki | <redacted> | 20210916191633 | login | 1137097 | 67601589 | +------------+------------+-----------------------+--------------------+-------------+--------------+ 4 rows in set (1 min 13.33 sec) MariaDB [centralauth_p]>
The <redacted> here is placed by me in order to prevent this task getting PermanentlyPrivate. I'm going to publish the query result in a permanently private counterpart of this task (T292595).
- Repeat the same thing for all other globally suppressed users. (In theory to get all of them an attacker would need to go to other wikis aswell). I think this can be automated, which makes this kinda scary.