Как показать / составить список пользователей в MySQL
Вам когда-нибудь нужно было получить список всех пользователей на вашем сервере MySQL? Есть команды для отображения баз данных и таблиц, но нет команды MySQL show users .
В этом руководстве объясняется, как вывести список всех учетных записей пользователей на сервере базы данных MySQL с помощью командной строки. Мы также покажем вам, как узнать, какие пользователи имеют доступ к данной базе данных.
Подготовка
Мы предполагаем, что в вашей системе уже установлен сервер MySQL или MariaDB.
Все команды выполняются внутри оболочки MySQL от имени пользователя root. Чтобы получить доступ к оболочке MySQL, введите следующую команду и при появлении запроса введите пароль root-пользователя MySQL:
Если вы не установили пароль для своего пользователя root MySQL, вы можете опустить опцию -p .
Показать всех пользователей MySQL
MySQL хранит информацию о пользователях в таблице с именем user в базе данных mysql .
Чтобы получить список всех учетных записей пользователей MySQL, используйте SELECT для извлечения всех строк из таблицы mysql.users :
Результат должен выглядеть примерно так:
Приведенная выше команда показывает только два столбца из таблицы mysql.user ( User и Host ). Эта таблица содержит более 40 столбцов, таких как Password , Select_priv , Update_priv и т. Д.
Используйте команду desc mysql.user; оператор для отображения информации о столбцах таблицы. Узнав имя столбца, вы можете запустить запрос к выбранным данным.
Например, чтобы получить список всех учетных записей пользователей MySQL, включая информацию о пароле и о том, активен он или просрочен, вы должны использовать следующий запрос:
Показать пользователей, имеющих доступ к конкретной базе данных
Информация о привилегиях уровня базы данных хранится в таблице mysql.db .
Вы можете запросить таблицу, чтобы узнать, какие пользователи имеют доступ к данной базе данных и уровень привилегий.
Например, чтобы получить список всех пользователей, имеющих некоторый уровень доступа к базе данных с именем db_name вы должны использовать следующий запрос:
Чтобы получить информацию только об учетных записях пользователей, имеющих доступ к данной базе данных, без отображения используемых привилегий:
Следующий запрос покажет вам информацию обо всех базах данных и связанных пользователях:
Выводы
В этом руководстве мы показали, как получить список всех пользователей MySQL и узнать, какие пользователи имеют доступ к конкретной базе данных.
List all users in MySQL database server
This article describes how to list all users in a MySQL database server in MySQL.
It is a common operation for a database administrator or database operation and maintenance personnel to view all users in the current database server.
In the MySQL database server, the mysql database stores some basic information, including all users. You can query all users from the user table in the mysql database.
List All Users
To list all users of the MySQL server, you must be logged in to the MySQL database server as an administrator. In the mysql client, we can do these as follows:
Enter the password for the root account and press Enter :
Use the following SELECT statement to query all users from the user table in the mysql database:
Here’s the output from the MySQL server that I’m connecting to:
There are many columns in the mysql.user table, which store various information about the user, such as password, password expiration time, whether it is locked or not, and various privileges.
Here, we only output two columns: user and host , where the user column holds the username of the user account, and the host column holds the host (which is usually the hostname or IP address) that the user account is allowed to log in from.
To get more information about the user table, you can output all columns of the user tables:
Here is the complete output:
Show Current User
To get information about the current user’s credentials, you can use the current_user() function as follows:
To get information about the current user, you can use the user() function as follows:
Show logged in users
You can get the currently logged in user using the following SHOW PROCESSLIST statement :
Or, you can get all logged in users from the information_schema.processlist table, as following:
Conclusion
In this article, you learned how to query information about all users, all logged in users, and the current user in MySQL.
Note that there is no SHOW USERS statement in MySQL, not like SHOW DATABASES , SHOW TABLES .
How to Show Users in MySQL and User Information Using Linux
A MySQL database server is frequently the first choice for developers and anyone interested in testing a structured query language. MySQL includes numerous features that make it dependable, secure, and efficient.
There are, however, some ways to improve MySQL server security even further. One of them is to create database users with restricted access.
This tutorial will explain why you should create separate MySQL user accounts and how to use the MySQL SHOW USERS command on your Linux VPS.
Why Create Users in MySQL Server
Whenever users or database administrators install MySQL, the first user that will be created is the root user – the MySQL administrator. The root user will have permission to do everything on the MySQL database.
While having all the permissions can seem beneficial, doing so comes with its own security flaws, and sharing the root user between several people is dangerous. Hackers often try to log in as the root user and steal the hosted information or even destroy the whole MySQL server alongside its data.
Thus, system administrators create users with specific permissions on some databases. In other words, if credentials for one account get compromised, the impact will be minimal and manageable.
How to Show Users in MySQL Database on Linux
Unlike the SHOW DATABASES or SHOW TABLES commands that display all databases or tables right away, the SHOW USERS command does not exist in MySQL. Even though there is no such command, users can use a MySQL query and get a full list of users in a given MySQL database server.
Follow the steps below for more information.
1. Log in as the MySQL Root User
Start by logging in to the VPS via SSH as the root user. Once that’s done, enter the MySQL command line with this command:
Then, enter your MySQL root password.
Important! Keep in mind that system and MySQL root passwords are separate and can differ.
Once you are in the MySQL console as the root user, you will be able to run queries to display other MySQL users.
2. Use the MySQL SHOW USERS Query
Use the following query to show MySQL users created in the database server:
As a result, you will see the list of all the users that have been created in MySQL.
Take note that there might be duplicate users. This is because MySQL filters access to a server according to the IP address it comes from.
You can also add a host column to see even more information using the following command:
With this, you will be able to see the MySQL users and which host or IP address they have permission to access. In our case, all of the users are from a local database:
3. See More MySQL User Information (Optional)
If you need more information about MySQL users, the search command can be expanded with the help of MySQL queries.
For example, the following command will print out all possible information from the user table:
However, such output might look too messy to figure out. Thus, we recommend narrowing the search by using more specific queries.
Here are some of the more popular use-cases:
Preview Table Columns
The following query will display a preview of user table columns. It is especially helpful if users want to check information about a specific table.
Display Only Unique Usernames
To skip repeating usernames, the following query can be used:
Password Expiration and Account Locking Status
In order to check the password expiration status and account locking state, use this query:
Show Current and Current Logged Users
The current user can be displayed with the following query:
If you need more information, you can modify the query to display currently logged-in users with their states. This command is beneficial for finding idle users that use up too many resources.
Conclusion
The administration of a database server can be challenging work. Thus, database administrators must be careful when creating and administering user permissions.
The MySQL SHOW USERS command allows administrators to view MySQL users alongside other important information.
To recap, we’ve covered the basics of how to show MySQL users tied to a database and learned how to:
- List all users created in a given MySQL database
- Preview MySQL database table columns
- Display only the unique usernames in a table
- Check password expiration and account locking status
- Show current and currently logged-in users in a MySQL database
We hope that you found this tutorial helpful. If you have any additional questions or insights, let us know in the comments section below.
Learn More About Database Management Systems
MySQL Show Users FAQ
How Can I See All MySQL Users and Passwords?
Make the following SQL query:
mysql> select user, password, host from mysql. user
This query brings up a list of users, their usernames, passwords, and database host.
How Do I Show Users in MySQL MariaDB?
Log into your MariaDB/MySQL server with the mysql client as a root user. Type the following query:
$ mysql -u root –p
Or
$ mysql –u root –h localhost –p mysql
Where Are Users Stored in MySQL?
MySQL stores users in the user table within the database.
Ignas takes great satisfaction in helping people tackle even the most complex technical issues. His current goal is to write easy-to-follow articles so that these issues will not happen at all. During his free time, Ignas likes to play video games and fix up things around his house.
MySQL SHOW USERS: List All Users in a MySQL Database Server
Summary: this tutorial shows you how to list users in a MySQL database.
MySQL show users: list all users
Are you looking for the MySQL SHOW USERS command? Unfortunately, MySQL does not have the SHOW USERS command like SHOW DATABASES , SHOW TABLES , etc., therefore to list all users in a MySQL database server, you use the following query:
In this statement, we queried user data from the user table of the mysql database.
To execute this query, you must log in to the MySQL database server as an administrator.
The following shows the output of the query above:
As you can see, we have three users in our local database.
To get more information on the user table, you can preview its columns using the following command:
For example, to show users and other information such as host, account locking, and password expiration status, you use the following query:
Here is the output of the query.
Show current user
To get the information on the current user, you use the user() function as shown in the following statement:
Or you use the current_user() function:
In this case, the current user is local@localhost .
Show current logged users
To list all users that are currently logged in the MySQL database server, you execute the following statement:
As use can see, there are two users that are currently logged in the MySQL database, one is executing a query while the other is “sleep”.
In this tutorial, you have learned how to list all users in a MySQL database server by querying data from the user table in the mysql database.