MySQL is one of the most popular database managers in the world. It has many features that make it reliable, robust and efficient. However, like any program, its maximum reliability is achieved if we use it correctly.
An easy and reliable way to increase MySQL security is to create users with limited permissions on the database. In this tutorial, you’ll learn how to show users in MySQL to manage MySQL databases more efficiently in a Linux terminal environment on your VPS.
Why Create Users in MySQL?
When installing MySQL the first user to be created is the root user – the MySQL administrator. The root user has permission to do everything on the MySQL database, so it is not convenient for other people to access your databases using this account.
On the other hand, hackers always try to log in as a root user to steal the hosted information. Or worse, to destroy the service and the data with it.
With this in mind, a sysadmin has to create users with specific permissions on some databases and in turn on some tables. With this, if the security of that user is compromised the impact is minimal or manageable.
How to Show Users in MySQL on Linux
Here you’ll learn the easiest method out there:
Login Using SSH
First, you need to access our server using SSH. We have a tutorial detailing the process!
Make Sure You Have Root Privileges
Once we have access to our server, we have to enter the MySQL console. To do it, we need root privileges. Enter this into the command line:
sudo mysql -u root -p
Type in Your MySQL Root Password
Then, we will have to type in our MySQL root password. It should be different from the system root password.
Once we are in the MySQL console as the root user, we can run sentences and commands.
Show MySQL Users
Now we can list all users created in MySQL through the following MySQL command:
mysql> SELECT user FROM mysql.user;
As a result, we will be able to see all the users that have been created in MySQL.
Add a Host Column (Optional)
There might be duplicate users. This is because MySQL filters access to a server according to the IP address it comes from. So you can also add a host column.
Mysql> SELECT user,host FROM mysql.user;
With this, we will be able to see the MySQL users and from which host or IP address, they have permission to access.
The administration of a database server is not always an easy task. So we must be careful with the creation and administration of user permissions. Now you know how to show MySQL users tied to a database.
You can consult MySQL’s official documentation regarding permissions and user creation to broaden your knowledge!