List MySQL Users: How to manage access

MySQL is an open source relational database management system. MySQL is a component of the LAMP web application software stack, which is an acronym for Linux, Apache, MySQL, PHP. A web application using MySQL database have to authenticate via a MySQL user and password who has sufficient privileges on that particular database. So MySQL users play a key role in web application hosting. In this article, we will show you how you can list MySQL users and list all your existing MySQL users and how you can add new users via both cPanel and Command line interfaces.

How to list MySQL users via cPanel GUI?

Log in to your cPanel account and then on Databases section select MySQL Databases.

Scroll down to bottom, here you will see Current Users. All MySQL users currently existing on our system will be listed here. One example of user listing is shown below:

In our system, we added only one user at present.

How to list MySQL users via Command Line Interface?

Login to MySQL using the following command:

mysql -u user -p

Use appropriate username in place of ‘user’

It will be prompted to enter the password for the user.

Now you entered MySQL CLI.

All details about existing MySQL users are stored in a table named ‘mysql.user’. The ‘mysql.user’ table contains information about users that have permission to access the MySQL server and their global privileges.

To list all existing MySQL users on the server use the query given below:

SELECT User FROM mysql.user;

The above command trims off “User” column and lists only usernames.

If you want to list of MySQL user information, including user permission information and all users data. You could try below query.

SELECT * FROM mysql.user;

Now we have seen how to list MySQL users from both cPanel and Command Line.

How to add MySQL users via cPanel?

Log in to your cPanel account and then on Databases section select MySQL Databases.

Scroll down, you will see Add New User.

Enter the name of the user you want to add and create a password for this user.

Click on ‘Create User’ button.

You will get a confirmation dialogue on the next screen.

You can see the newly created user on ‘Current Users’ section:

How to add MySQL users via Command Line Interface?

Login to MySQL interface using the following command:

mysql -u user -p

To add a new MySQL user use the following query:

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

Replace ‘user_name’ and ‘password’ entries with actual username and password.

An example is shown below:

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.01 sec)

Now to list the created user use the following command:

SELECT User FROM mysql.user;

Conclusion

In this other article, we explain how to grant MySQL permissions. Note that whenever you list users in MySQL, you may want to confirm their permissions to access such databases. We also recommend this article about how to enable MySQL remote access.