WordPress’ design goals offer you flexibility, but the most efficient way to carry out a task is to do so through the dashboard. Almost every aspect of your installation is available to you here, often with an intuitive Graphical User Interface (GUI) to assist you. Sometimes though, using the dashboard might not be the best approach. For instance, you might want to add a new user via your MySQL database rather than use the dedicated screens within WordPress.
While we don’t recommend poking around in your MySQL database for everyday tasks, a simple ‘in-and-out’ to set up a user can save your bacon when you can’t access your dashboard. It’s an extreme method that solves a unique problem, but it will be your only solution if you need it.
For this post, we’re going to show you how to add a new user via the MySQL database. Before that, we should look at why you’d want to do this.
Why You’d Want to Add a New User Via the MySQL Database
We admit that you may be reading this article because you can’t access your WordPress website’s dashboard, and already understand the reason why. In most cases, you won’t have physical access to any other part of WordPress, so will have to ‘walk the long way round’ to get into your site.
As for why this might happen, the number one reason is user involvement. This can cover a few innocent and malicious use cases:
- You may have forgot and/or lost your user credentials, and don’t have another admin user to lean on.
- It could be that you’re more comfortable within your MySQL database’s interface (although this is likely rare).
- You’re the victim of malicious attacks that has compromised your site.
- You could also be fighting off a disgruntled ex admin user who exercises the ability to lock you out of your site.
A lot of these reasons are dramatic and extreme cases, of course. Regardless of the reason behind your lack of access, you can leverage the accessibility of your MySQL database to create a new WordPress user.
How to Access Your MySQL Database
Before you set up a new user, you’ll need to access the database itself. While you could do this through the command line if you’re brave enough, there’s a more straightforward way.
You can access your database through a management tool such as phpMyAdmin or Adminer. Depending on your host, you’ll find one or the other on offer; it’s rare that a host won’t offer one of these, or even an alternative database management solution.
For a long time, phpMyAdmin has been somewhat of a typical standard:
You’ll have a tree directory down the left-hand side, full databases. Expanding these will show you a list of ‘tables’. These are like a worksheet in Excel or a spreadsheet application, and hold individual elements of data. You can nest tables too, and this is a common way to build data structures for WordPress.
Each table contains a specific ‘slice’ of data, such as comments, content, and users. In fact, the database contains almost everything to do with your WordPress website. As such, it’s a fundamental element of your site, and also important.
We’ll get onto where to find your list of WordPress users later. For now, let’s give take a quick detour to talk about hacking attempts on your site.
What to Do If You Suspect You Have a Hacked Website
Before we move on, it’s crucial to talk about hacked websites. If you suspect that your website is subject to hacking, you should act fast. It’s a subject we talk about elsewhere on the blog, but in short, you’ll want to eradicate the threat before you do anything else.
If you have an issue with access to a hacked site, our advice is to contact a specialist company such as WP Tech Support, Sucuri, Wordfence, or MalCare. They will be able to help you get the situation under control, which will then give you chance to shore up your site for the future.
How to Add a New WordPress User Via the MySQL Database
Before you open up whichever database management tool you choose, remember to carry out a full backup of your site and database. If the worst happens and you need to restore your site, these files will be the most valuable to you.
When you’re ready, you’ll want to follow the following steps:
- Access your MySQL database using your preferred tool.
- Open the
wp_userstable. - Insert a user into the table.
- Add capabilities for the new user.
Each step is more straightforward than you may expect for an intricate task. For the first step, you’ll need to open up your MySQL database.
1. Log Into Your MySQL Database Using a Dedicated Management Tool
First, you should look at which tool your host offers. While there are lots of management solutions, most hosts work with either phpMyAdmin or Adminer. The process is the same for both, and while we’ll use Adminer in this post, we’ll also note when the instructions diverge for phpMyAdmin.
Note that you’ll need your database credentials to log in, rather than your site or hosting details. These will often be within your hosting control panel, and we advise you to contact your host if you struggle to find the details.
You might also have to hunt for your database access link, because hosts will often have custom dashboards that aren’t consistent between companies. This is more prevalent as hosting providers move away from cPanel. Though, even for this hosting dashboard, the link to phpMyAdmin is prominent:
Regardless of your host and database management tool, the login process is similar to almost every other ‘locked’ system. Simply enter your username and password, then click through and wait for the database information to load.
2. Locate and Access the wp_users Table
Once you’re in, you need to find the wp_users table, as this is where you’ll work. The process for most interfaces is the same: Take a look down the left-hand side for a table called wp_users. Though, the wrinkle here is that it might have a different name depending on whether you use a custom table prefix:
Regardless, the table should have the name “users” within. If you click on it in the left-hand column, this will open in the main area of the screen:
For phpMyAdmin, the screen should open showing a list of users. For Adminer, it will show the table structure, which is helpful to understand what information you’ll need to enter, and how. To see the list of current users, choose the Select data link:
From here, you can begin to add a new user.
3. Insert a New User Into the Table
Both Adminer and phpMyAdmin have links on the wp_users table screen to enter new data. For phpMyAdmin, you’ll choose the Insert link at the top, while in Adminer it’s New item:
Though, because you’re dealing with the same type of database, the fields you need to enter will be the same:
user_login: You can choose a login name here that you’ll type into the relevant WordPress fields.user_pass: You should type a password here, but make sure you select MD5 from the field’s drop-down menu. This helps to ‘hash’ your password and keep it safe from further malicious intent.user_nicename: For now, use the same name as the user_login, but remove all of the special characters and capitalization.user_email: This can be an email address you’re able to access.user_registered: This will act as your timestamp for registry, which should be the current data and time. You might be able to choose now from the drop-down menu here, to help you.user_status: You can set this value to zero.
Once you fill in these fields, click Save or Go, the list will update to show the new user. Though, this the end of the job. You have to make sure the new user has the right permissions.
4. Add Specific Capabilities For the New Created User
Before you can log in as a new user, you need to apply some capabilities in order to give the user administrative privileges. First though, jot down the user ID number from the wp_users table:
Next, find the wp_usermeta table from the left-hand list:
This will show you lots of specifics for each user, although none for your new addition. Much like the last step, your task here is to add a new row to the table. Here’s how to fill in the fields:
user_id: This should be the same ID you’ve just noted down.meta_key:wp_capabilitiesmeta_value:a:1:{s:13:"administrator";b:1;}
Again, click to continue, and you should be able to log into your site using the front end WordPress login form.
In Summary
It’s a rare situation that means you’ll need to crack open your MySQL database to work with your content. In most cases, the WordPress dashboard is a fantastic platform to work with your site’s elements. Though, if you can’t access your site for either innocent or malicious reasons, logging into your database will let you carry out almost the same number of tasks.
In this post, we’ve shown you how to add a new user via your MySQL database. The hardest part of the task is to add user capabilities, but the meta data required isn’t tough to write. Once you finish, you can log into your site through the usual GUI interface on the web.
Are you looking to add a new user via the MySQL database, and if so, for what reason? Share your stories in the comments section below!
Leave a Reply