Plesk Container Not Accessible After Deleting Admin MySQL User

Whilst recently carrying out a security audit, one of the tasks on my list was to refine the permissions that MySQL users have. One of the users in the list was called ‘admin’. It had full permissions to all databases which struck me as a bit unsafe so, seeing as I wasn’t using this MySQL user on any of my sites, I struck (maybe a bit too hastily) and deleted it. From here on in things went downhill…

It’s probably worth mentioning at this point that I use the Plesk control panel to manage various aspects of my server; from database access to setting up cronjobs. I know this could all be done via the command line but by doing it through the Plesk GUI it makes carrying out tasks generally quicker.

It was at the moment I deleted the MySQL admin user that I started to receive the following error when trying to access Plesk:

The service is not available now.
Some Container resources are overused. Click to see the details.

The error was definitely misleading and didn’t relate in anyway to the deleted MySQL user. Regardless, I was certain the two were linked so my first point of call was to reinstate the admin user I had just deleted. But how do you add a MySQL user with full permissions when you a) don’t have any other MySQL users available or b) don’t have another MySQL user available to log in as with high enough privileges to create more users.

The Solution

After lots of digging around I found the solution to add the admin user to MySQL and managed to get access back into Plesk by following the steps below:

2. Navigate to your MySQL configuration file and open it in an editor. For me this was located at /etc/my.cnf:

$ nano /etc/my.cnf

3. Add the following line to my.cnf on a blank line:

skip-grant-tables

By adding ‘skip-grant-tables’ we are telling MySQL that permissions are out of the window and anyone can get access to everything. It goes without saying that we’ll need to remember to remove this later on.

4. Save and close my.cnf and restart MySQL so the updated configuration comes into effect:

$ /etc/init.d/mysqld restart

5. Once the MySQL service has restarted the next step is to add our ‘admin’ user back in. First, lets log onto the MySQL command line like so:

$ mysql

Notice how we don’t need to add any user credentials? This is the configuration option we added above in effect.

6. Next lets change to the ‘mysql’ database:

mysql> use mysql;

The ‘mysql’ database is where MySQL stores it’s configuration settings, as well as user information and permissions.

7. Now it’s time to add the ‘admin’ user back in where it belongs. We can achieve this by running the following query:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

9. And finally, to bring our reinstated user and all it’s permission into effect we need to flush the privileges:

mysql> FLUSH PRIVILEGES;

At this point in the process you should now be able to log into Plesk successfully. If you’re already logged in I recommend you log out and then log back in again. Fingers crossed it should all be back to normal.

Remember…

The last thing to remember to do is to undo what we did in step 3 above. Simply repeat steps 1-4 and remove or comment out the option.