In this short and simple tutorial, I’m going to show you how to show all the users in a MySQL/MariaDB database.
There are many SHOW commands in MySQL: SHOW DATABASES, SHOW TABLES, SHOW VARIABLES, etc. Naturally, people expect there to be a SHOW USERS command. However, there’s no “SHOW USERS” in MySQL. There’s a different method to showing/listing all the users in a database in MySQL.
I’ve wondered why there’s no SHOW USERS command, I even tried running SHOW USERS; in my early days. The actual command to show all the users in MySQL is still pretty simple and easy to remember.
How to SHOW USERS in a MySQL database
It’s pretty simple, the command you need to run to “SHOW USERS” in MySQL is:
SELECT User FROM mysql.user;
This command shows you the usernames from the users table in your MySQL database. Example results:
User debian-sys-maint mysql.infoschema mysql.session mysql.sys root
If you want to show all the data in the User table, run the following command:
SELECT * FROM mysql.user;
These are the 2 most common commands for showing all users in MySQL.
There are other similar commands that may help you. I’ll explain them below.
How to show the current users in MySQL
To show the current user (the one you’re logged in with), run:
SELECT user();
To show all the currently logged in users, run:
SELECT User FROM information_schema.processlist;
That’s pretty much it for this tutorial meant for beginners. Short and simple.
