How to list all database users using proper MySQL show users statements

There are some functions, statements, and queries that you are bound to run in MySQL as long as you are managing a MySQL database. Two of these are the MySQL show users and the MySQL show all users statements, which to be fair are often common assumptions.

For amateur MySQL database users, and those with experience using other databases, it is often surprising that the MySQL show users command does not work on MySQL. But, there are acceptable statements in MySQL that function the same was as these commonly misinterpreted phrases, and the right statements are what you will be learning in this post.

But before we get into the acceptable MySQL show users syntax and commands, we need to have the users we will be listing. And what better way to kick us off than to learn how to create users and allocate privileges?

So without further ado, let us get into creating MySQL database users.

How to create MySQL database users

To start off, let us understand what MySQL is. It is one of the most popular and best database management software, and to make it better, MySQL is open-source. There is an Enterprise paid edition, but there is also a free community version that gives you all the privileges you need to run a fully functional database.

MySQL helps you store, organize, and retrieve data and affords you a number of permission granting options for database users. Below, we look at some of the options you have when creating a MySQL database user. The assumption here is that you are the root user.

Creating a new database user

The root user in MySQL has full access to every database in a MySQL Server. At times, you may require adding more users and restricting their access to the database or customizing their permissions.

First, in the MySQL shell, start by creating a new user:

Mysql> CREATE USER 'newdbuser'@'localhost' IDENTIFIED BY 'password';

The specification of the user’s host as localhost allows the user to connect to the local server via SSH. The first task is over and done with, but as you can tell, we only have a new user but without any permissions.

At this point, even if the new user tries to log into the MySQL server with the details we have created above, they will not be able to access the MySQL shell. SO, now we allocate them permissions to access the database:

Mysql> GRANT ALL PRIVILEGES ON * . * 'newdbuser'@'localhost';

In the command above, we use the asterisks to specify the respective database and the tables that the user will have access to. Because the command above does not specifically state any database, our new user will have read, edit, execute, and any other privileges over all existing databases and tables in our Server.

Essentially, the new user essentially gets root-like privileges with the command above. But, if you want a secure database, allocating users all these privileges is a bad idea. Practically, all other users should have specific permissions depending on their needs, and only the database administrator should have root access.

Granting different user permissions

There are a number of different privileges you can grant your MySQL database users. They include:

ALL PRIVILEGES – this permission grants the user all permissions to manipulate all databases and tables on your Server.

CREATE – grants users permission to make new tables and databases.

DROP – grants users delete privileges on tables and databases.

DELETE – grants users the permission to remove rows from tables.

INSERT – grants users permission to add rows into tables.

SELECT – grants users permission to use the SELECT command to view data from the database.

UPDATE – allows users to update rows in a database table.

GRANT OPTION – this privilege gives users the ability to grant and revoke other users’ privileges.

‍

Below is the general syntax for granting user privileges:

Mysql> GRANT permission_type ON dbname.table_name TO 'username'@'localhost';

You can also replace ‘localhost’ with ‘%’ as a wildcard and grant multiple privileges in a single command by separating the privileges:

Mysql> GRANT permission_type ON dbname.table_name TO 'username'@'%';

Should you want to grant the user a specific permission type to any table or database, replace the database or table name with an asterisk(*). Sometimes, you also need to revoke permission. The general syntax for revoking permissions is:

Mysql> REVOKE permission_type ON dbname.table_name FROM 'username'@'localhost';

Unlike the grant syntax, when revoking privileges, you replace ‘TO’ with ‘FROM’. To see what privileges a user has, run the command:

SHOW GRANTS username;

And, like you delete databases using the keyword, DROP, you can also delete users the same way:

Mysql> DROP USER 'username'@'localhost';

The last step after creating any new users is testing out their accounts by logging out as root:

Mysql> quit

and logging back in as the new user:

$mysql-u [username] -p

After allocating new privileges to users, it is imperative that you reload all user privileges for the changes to take effect. You do this by running the command:

Mysql> FLUSH PRIVILEGES

MySQL show all users not working? No worries

MySQL provides the equivalent of MySQL show all users statement as we will see below. Although it is not as straightforward as most novice MySQL users think on their first try, the commands to list users are fairly straightforward and easy to recall.

For instance, once you are on your MySQL shell, you only need to run the command below to get a list of all user names from the ‘User’ table:

Mysql> SELECT user FROM mysql.user;

But that’s just one of MySQL’s equivalents of MySQL show users statements. To see more information about the existing database users, run the command below, which will give you a list of users and even their permissions:

Mysql> SELECT * FROM mysql.user;

There are also other variations of the MySQL show users statement to list different user details as we can see below:

Mysql> SELECT DISTINCT user FROM mysql.user;

By using the DISTINCT statement, the engine will only display the unique user names without repetition.

Mysql> SELECT User, Host FROM mysql.user;
Mysql> SELECT User, Host, Password FROM mysql.user;

The last statement above will show you a list of database users, their hosts, and their passwords. Basically, you can adjust the query to extract and return any user information you want from the ‘User’ database.

Last, you might be wondering if there is a way you can find out all the different columns that make up the user table. With this knowledge, you can better decide what user information you want to list in the future. To show all the columns in the user table, simply enter the following command in the mysql> prompt:

Mysql> desc mysql.user;

While the lack of MySQL show users command comes as a surprise to first-time MySQL users, as you can see above, MySQL has an easy to use and easy to remember workaround to list MySQL database users.