List Users in MySQL

[Linux Hint]

MySQL is the most widely used, free open-source DBMS (Database Management System). It is used by some of the famous organizations like NASA, US NAVY, Tesla, Twitter, Spotify, and a lot more. If your work is related to database administration tasks for huge organizations and corporations, responsible for data integrity, and care for the privileges that users have over too many databases, then this article is for you. It will guide you in listing the users, look at the privileges they have, and list the users on behalf of a database. This is because we can list the users using MySQL’s own built mysql.user table.

To list down the users in MySQL, we first need to login to the MySQL server.

We can log in to the MySQL server as a root user by running the following command:

sudo mysql -u root -p

If you are unable to login, there is a possibility that your system’s mysql.service is not active and running. So, to start the service, run the following command:

sudo systemctl start mysql

To check the status of the service, use the following command:

sudo systemctl status mysql

If it is active and running, try to log in now.

After logging in to the MySQL server, we can list all the users regardless of the access they have over the different databases by using the SELECT statement and MySQL’s build mysql.user table.

SELECT * FROM mysql.user;

As you can see in the screenshot, we got a lot of information. MySQL has a table for the list of users. So, if we want to trim down the columns to have a few columns instead of using an asterisk, MySQL provides the DESC statement to get all the column names and fields of the user’s table.

To get the user’s table run the following command:

DESC mysql.user;

After having a look at the table, we can list a limited amount of information and have a few columns about the user. Instead of using an asterisk sign in the SELECT statement, we can provide the column names and list the users by running the  command in the example below:

SELECT user, host FROM mysql.user;

As you can see in the screenshot attached, we have a list of only two columns now.

Conclusion

This article contains a very basic and easy to follow concept to list the users in MySQL using different techniques. We have learned how we can use the column names of myqsl.user to trim the list and have a better insight.

So, for more useful content and a better understanding of database concepts, keep visiting linuxhint.com.

View the full article