Recovering WordPress Multisite Admin Access via MySQL Database
A practical guide to troubleshooting WordPress admin lockouts, navigating Linux shell quirks, and modifying serialized PHP arrays in MySQL to restore access.
I recently ran into an interesting edge case while managing a WordPress site hosted on a Linux VM. It started as a simple access management request but quickly escalated into a deep dive into WordPress database architecture, Linux shell quirks, and serialized PHP arrays.
Here is a breakdown of the troubleshooting steps I took to regain control of a locked-down environment.
The Inciting Incident: Granting Minimal Access
The initial goal was straightforward: grant minimal backend access to an external agent so they could review the site's plugins, themes, and settings to draft a technical proposal.
By default, WordPress strictly divides access between content management and site management. Standard roles like Editor, Author, or Contributor cannot see plugins or settings. The only built-in role with that level of access is the Administrator. To avoid handing over full administrative control, the standard procedure is to use a role editor plugin to create a custom "Read-Only Auditor" role.
However, when I logged into my own Administrator account to set this up, I hit a wall: The "Plugins" menu was completely missing. I also realized I couldn't add new users. Even though I was an Admin, I was effectively locked out of site management.
Troubleshooting Step 1: Checking Server-Level Blocks
When administrative menus disappear, it usually points to a configuration restriction or a rogue security plugin. Having root SSH access to the VM, I bypassed the frontend and went straight to the server.
My first thought was that a developer had locked down file modifications. I navigated to the WordPress root directory to check the wp-config.php file:
grep DISALLOW_FILE_MODS wp-config.php
If this is set to true, WordPress hides the Plugins and Appearance menus. However, that wasn't the culprit here. My next suspicion was a security plugin hiding the menus. I could have force-deactivated all plugins by renaming the wp-content/plugins folder to plugins_old, but I wanted a more surgical approach. It was time to look at the database.
Troubleshooting Step 2: Extracting Credentials and Shell Quirks
To query the database directly, I needed the MySQL credentials. Instead of opening the entire configuration file, I quickly grepped the credentials from the terminal:
grep DB_ wp-config.php
This output the database name, user, and password. I then attempted to log into MySQL, passing the password directly in the command:
mysql -u db_user -p db_name
Troubleshooting Step 3: Verifying Roles and Capabilities
Once inside MySQL, I needed the unfiltered truth about my user permissions. WordPress stores user roles across the wp_users and wp_usermeta tables. I ran a join query to see exactly what capabilities my account had:
SELECT
u.user_login,
u.user_email,
m.meta_value AS assigned_role
FROM
wp_users u
JOIN
wp_usermeta m ON u.ID = m.user_id
WHERE
m.meta_key = 'wp_capabilities';
The database output a block of serialized PHP array data. Looking at my user, the role was clearly defined: a:1:{s:13:"administrator";b:1;}. Digging deeper into the wp_user_roles options table, I confirmed that the Administrator role still possessed all 184 capabilities, including install_plugins and edit_themes.
My user was a full Administrator, and the role hadn't been stripped. So why was my dashboard restricted?
Identifying the Multisite Network
The fact that I was an Administrator but couldn't manage plugins or add users triggered a realization: This might be a WordPress Multisite network. In a Multisite setup, regular Administrators are restricted to managing content on their specific sub-site. Only a Super Admin has the overarching power to install plugins, modify themes, and add users across the network.
To prove this theory, I checked for the existence of Multisite-specific database tables:
SHOW TABLES LIKE '%sitemeta';
The query returned wp_sitemeta. The site was indeed configured as a network.
Elevating Privileges via Serialized Arrays
Now that I knew I was dealing with a Multisite, I needed to figure out who the Super Admin was, and more importantly, how to promote my own account (sysadmin) to that tier.
I queried the wp_sitemeta table to view the network administrators:
SELECT meta_value
FROM wp_sitemeta
WHERE meta_key = 'site_admins';
The output was another serialized array: a:1:{i:0;s:5:"admin";}.
Because this is a strict serialized string, you cannot simply replace the name or append a new one casually. The string format acts like a mathematical formula. If the character counts don't match perfectly, the array breaks, and nobody gets access.
a:1= 1 Super Admin in the array.i:0= Index 0 (the first item).s:5= The username is exactly 5 characters long."admin"= The username.
To elevate my account without locking out the original administrator, I needed to add my username alongside the existing one. My username, sysadmin, is exactly 8 characters long. I constructed the UPDATE statement to expand the array from one item to two (a:2), keeping the original admin at index 0 and adding my account at index 1 (i:1), while ensuring the string integer s matched my character count exactly:
UPDATE wp_sitemeta
SET meta_value = 'a:2:{i:0;s:5:"admin";i:1;s:8:"sysadmin";}'
WHERE meta_key = 'site_admins';
MySQL returned Query OK.
The Result
I refreshed my WordPress dashboard. Instantly, the "Network Admin" menu appeared at the top of the screen. The Plugins menu and the Users menu were fully restored. By understanding the underlying database structure and how WordPress serializes its permission arrays, I was able to safely grant myself dual Super Admin privileges alongside the original account, bypassing the dashboard restrictions entirely from the command line.
From there, I easily installed the role editor plugin, created the restricted auditor role, and successfully granted the external agent their minimal access.