How to View a List of MySQL and MariaDB Database Users

Colleagues hello to all.

In today's article, we'll talk about how you can display all users in a Mysql database. By the way, in MariaDB, these commands are also suitable, which I will show you.

As we all know, the main function of any database is of course to store some information in tables in a structured way. We get access to this information by logging into the database, and only then can we request some information from it. Sooner or later, the moment comes when we have created a lot of users and we need to review them all. All users in Mysql are stored in the mysql system database in the user table.

I'll show you some simple queries that will show you valuable information about users and help prevent possible problems.

If we want to view users through the console in Linux, then we need to connect to the Mysql console with the command:

$. mysql -u root -p

We will log in to the database from the root account, it is the most important in the Mysql database. After entering the command next, you will be asked to enter the password for the root account, if you enter the password correctly, the DBMS will let you through, otherwise you will receive an authorization error.

With the very first query, we will simply query all users by running a query to the mysql system database and the user table in it.

mysql> SELECT user FROM mysql.user;

The user field in the result indicates what the user name is called in the database.

With this query, we're just looking at what users we have in the database. The users mysql.infoschema, mysql.session, mysql.sys and root  are created for us by the DBMS itself during its initial installation, and therefore I strongly do not recommend that you do anything with them.

But if we have too many users, and we want to know their number, then we can use the special COUNT() function, which will count all users for us.

mysql> SELECT COUNT(user) FROM mysql.user;

As you can see, as a result, we currently have only 4 users in the Mysql database.

If anyone does not know, then in the Mysql database there are not just users, but there are both local users and external ones. To find out which user is local and which is external, we need to execute the same query, but also add an additional field named host to the selection.

mysql> SELECT user, host FROM mysql.user;

The percent sign % means that the user is external, and the localhost value means that the user is local. There is a big difference between local and external.

For example, we now have a root user. We under this user connected to the database through the console on the server itself, where we have the DBMS itself installed. Let's say I want to connect to the database under the same user, but from my laptop, the database will not let me do this because we have a local root.

And if I log in to the database from my laptop and use the it-inzhener account, the database will let me through, because we have explicitly indicated that this account is external. And from  if I connect to the database via the console on the server itself where we have Mysql DBMS installed, then the database will not let me through because only local users can connect to the database locally.

That's the whole difference, although not big, but it can create a lot of problems with access to the database.

In the following example, let's say we want to find out which users are locked and which are not, for this we need to execute the same query, but also add an additional parameter account_locked to the selection.

mysql> SELECT user, account_locked FROM mysql.user;

As you can see, as a result, we have some Y and N. So, from the value of N, it just means that the user is not blocked, but Y is blocked, which means that if there is N next to the account, then users will be able to log into database, and the one who has Y cannot.

For example, we have 1000 users in the database and counting how many are blocked and how many are not is very difficult and inconvenient. For this purpose, we can group our users using the additional GROUP BY command.

mysql> SELECT account_locked, COUNT(user) FROM mysql.user GROUP BY account_locked;

As a result, we see that we have 4 users now blocked and they will not be able to log into the database. And we have two active users and they will be able to enter the database.

With the following simple example, we can see which user we are currently logged into the database with.

mysql> SELECT user();

or

mysql> SELECT current_user();

In both cases, the database tells us that we are currently logged in as root.

In the following example, we can see which users are currently logged into the database. This provides us with important information when monitoring our Mysql database for unauthorized access. Such information is stored in the system database information_schema in the processlist table.

mysql> SELECT user, host,db, command FROM information_schema.processlist;

Field descriptions.

• user - Username.
• host - Local user or external.
• db - The name of the database the user is connected to.
• command -  The SQL query the user is currently executing.

As a result, colleagues using the commands presented in the article you can now easily structure information about users.

Thank you all, I hope that my article helped you in some way.