Linux mysql list users

How to see/get a list of MySQL/MariaDB users accounts

Step 1 – Login to mysql

First log into your MySQL/MariaDB server as a root user using the mysql client. Type the following command:
$ mysql -u root -p
OR
$ mysql -u root -h localhost -p mysql
Once logged in use various SQL queries as follows to show users accounts in a MariaDB or MySQL database.

Step 2 – Show users

Type the following query at mysql> prompt to see list the users in a MySQL database:
mysql> SELECT User FROM mysql.user;
Sample outputs:

Fig.01: How to get a list of MySQL user accounts

• No ads and tracking
• In-depth guides for developers and sysadmins at Opensourceflare✨
• Join my Patreon to support independent content creators and start reading latest guides:
  • How to set up Redis sentinel cluster on Ubuntu or Debian Linux
  • How To Set Up SSH Keys With YubiKey as two-factor authentication (U2F/FIDO2)
  • How to set up Mariadb Galera cluster on Ubuntu or Debian Linux
  • A podman tutorial for beginners – part I (run Linux containers without Docker and in daemonless mode)
  • How to protect Linux against rogue USB devices using USBGuard

Join Patreon ➔

Step 3 – Show users along with host name where they are allowed to login

The syntax is:
mysql> SELECT host, user FROM mysql.user;
OR
mysql> SELECT CONCAT(QUOTE(user),’@’,QUOTE(host)) UserAccount FROM mysql.user;
OR
mysql> SELECT host, user, password FROM mysql.user;
Sample outputs:

Step 4 – How to avoid repetitions of user names?

Try the following sql query:
mysql> SELECT User distinct from mysql.user;
The SELECT DISTINCT statement is used to return only different values.

Step 5 – Get a listing of the fields in the mysql.user

Type the following sql command to see all field names associated with mysql.user table:
mysql> DESC mysql.user;
Sample outputs:

Step 6 – Finding out user rights

Type the following command:
mysql> SELECT User, Db, Host from mysql.db;
Sample outputs:

The following provide more info about mysql.db:
mysql> desc mysql.db
Sample outputs:

Step 7 – How to find the privilege(s) granted to a particular MySQL user

Type the following sql statement:
mysql> show grants for ‘vivek’@’%’;
OR
mysql> show grants for ‘vivek’@’192.168.1.1’;
OR
mysql> show grants for ‘root’@’localhost’;
Sample outputs:

To assist seeing grants, enter:
mysql> select distinct concat(‘SHOW GRANTS FOR ‘, QUOTE(user), ‘@’, QUOTE(host), ‘;’) as query from mysql.user;
Sample outputs:

You can now copy and paste SHOW GRANTS FOR ‘vivek’@’10.175.1.5’;
You can canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them using pt-show-grants. Install pt-show-grants using apt-get command or apt command:
$ sudo apt-get install percona-toolkit
Type the command:
$ pt-show-grants

🐧 Get the latest tutorials on Linux, Open Source & DevOps via

Источник

Как показать / составить список пользователей в 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 и узнать, какие пользователи имеют доступ к конкретной базе данных.

Не стесняйтесь оставлять комментарии, если у вас есть вопросы.

Источник

How to Show Users in MySQL using Linux

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:

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:

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.

With this, we will be able to see the MySQL users and from which host or IP address, they have permission to access.

Conclusion

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!

Edward is an expert communicator with years of experience in IT as a writer, marketer, and Linux enthusiast. IT is a core pillar of his life, personal and professional. Edward’s goal is to encourage millions to achieve an impactful online presence. He also really loves dogs, guitars, and everything related to space.

Источник

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.

Источник

MySQL SHOW USERS: List All Users in a MySQL Database

Today we are going to guide you on how to show all users in the MySQL users Database.

A common question that most beginner MySQL users ask is “How do I see all of the users in my MySQL server?” Most of them assume that there is a show users command in MySQL, but there isn’t one. This is a common mistake because there are other MySQL commands for displaying information about the database. For example, SHOW DATABASES will show us all of the databases that are present in our MySQL Server, and SHOW TABLES will show us all the tables in the MySQL database that you have selected.

It’s not unusual for people to assume that there should be a SHOW USERS command in MySQL. Even though there isn’t a specific command for it, there are several ways to actually see the list of users and even filter them to see exactly what you need. This can all be done with the MySQL command line tool – let’s get started.

Once you are logged in to your Linux server, execute the following command to log in to the MySQL command line interface.

Table of Contents

Log in to your MySQL Server

Log in as root to your MySQL server using the following command:

Then enter your MySQL root password. It is not set by default, so all you need to do is press the [Enter] key if you never set it. However, if you have set the password (whether with the mysql_secure_installation script or something else), you should enter that password now.

MySQL Show Users Command

We’ll just to query the User table to show all MySQL Users with the following command:

The list of all MySQL users should be similar to the one below:

If you want to add more columns or exclude some, just edit the command with the columns you need. You may only need the names of the users, so you can use SELECT User FROM mysql.user;

Another way to see all users is to simply use the asterisk (*) wildcard when selecting fields from the user table. It should look like this:

This will return all possible field information for each user. Be aware that the number of fields is typically very long/extensive, so the output likely won’t be readable and you probably don’t need all of the information available for each user. This is why we suggest specifying the field names, that way the output will be easily readable, and you’ll only see the information that you need.

Some of the fields are related to privileges and permissions that each user has (such as “Insert_priv” or “Drop_priv”), and some fields are for the general properties of the user account, such as “max_connections” or “max_updates”.

Display Only Unique Usernames in MySQL Database

In some cases, selecting the user table can have duplicate results (e.g. one user for different hosts, but the users have the same name) – this can create clutter, so you may want to display only unique usernames.

If you want to display only unique usernames that won’t be repeated in more rows, you can use SELECT DISTINCT User FROM mysql.user; , which should give you this output:

No repeated rows, just like we wanted. This can give you a clean look at what users exist on the database server.

MySQL User table Information

As we mentioned in Step 2, there are many fields that you could use to see specific properties of your users. With this next command, you’ll be able to see all of the fields that your MySQL server supports. To see all the fields in the mysql.user table containing a description related to the user table, run the following MySQL command to query the database.

The output will look similar to the one below. There are a lot of options!:

you can now combine this with the example given in Step 2. For example, if you want to return the list of users and their update privileges, you can run this command:

Just replace “Update_priv” with any fields that you might need.

Show Information About Current User

You can use a built-in function of MySQL to see the name and host of the user that you used to log into the MySQL command line. It’s the “user()” function, and all you have to do is select it:

The output should give you information on the user running the query.

And that’s it, with the completion of this tutorial, you have learned how to show MySQL user accounts via the command line.

If you have any additional questions on the MySQL database, feel free to leave a comment below. You can always get a Managed MySQL hosting from us and our fully managed support will help you with problems related to MySQL or will explain how to show all users in the MySQL database.

PS. If you liked this post on making MySQL show all users, please share it with your friends via social media networks by using our share shortcuts. Thanks.

2 thoughts on “MySQL SHOW USERS: List All Users in a MySQL Database”

Either you can just simply use * (asterisk) after SELECT word. It’ll choose all the columns

Источник