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:
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:
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:
You can also replace ‘localhost’ with ‘%’ as a wildcard and grant multiple privileges in a single command by separating the privileges:
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:
Unlike the grant syntax, when revoking privileges, you replace ‘TO’ with ‘FROM’. To see what privileges a user has, run the command:
And, like you delete databases using the keyword, DROP, you can also delete users the same way:
The last step after creating any new users is testing out their accounts by logging out as root:
and logging back in as the new user:
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 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:
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:
There are also other variations of the MySQL show users statement to list different user details as we can see below:
By using the DISTINCT statement, the engine will only display the unique user names without repetition.
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:
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.