+2 votes
in Tutorials and Guides by (1.5m points)

How to list all MySQL or MariaDB users from the console

1 Answer

+3 votes
by (725k points)
 
Best answer

One of the basic tasks when managing MySQL databases is to manage users and their permissions . Today we will take the first step with a simple tutorial that will serve as an introduction and consists of listing all MySQL users from the console or terminal .

image

We will start by saying something very basic and that to advanced users it may seem common sense, but it has already happened to me on several occasions that new users believe that system users are MySQL users and it is not so. The system user system operates independently of the MySQL user system.

That said, also clarify that this tutorial is valid for MySQL and MariaDB database systems .

How to list all MySQL or MariaDB users from the console.

First of all we must access the MySQL or MariaDB server from the terminal , something that we already explain in detail in this tutorial . We must access our MySQL root user since we are going to need these permissions to be able to access the mysql name database, which is the one that interests us to find out the users.

Therefore, the command we will use to access will be:
mysql -u root -p mysql

Once we have accessed the MySQL command interpreter as root, we can launch the necessary queries that we will see below.

Show the mysql database tables.

Now that we have access to the mysql database, we can work with it by launching the following query to find out the name of its tables:
show tables;

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
30 rows in set (0.00 sec)

We see that among all the tables there is a user call, which is what interests us, since it stores all the information related to MySQL / MariaDB users .

To obtain the columns of the user table, we launch the following query:
describe user;

MariaDB [mysql]> describe user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field                  | Type                              | Null | Key | Default  | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host                   | char(60)                          | NO   | PRI |          |       |
| User                   | char(80)                          | NO   | PRI |          |       |
| Password               | char(41)                          | NO   |     |          |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N        |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N        |       |
| File_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N        |       |
| References_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N        |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N        |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N        |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N        |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N        |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |          |       |
| ssl_cipher             | blob                              | NO   |     | NULL     |       |
| x509_issuer            | blob                              | NO   |     | NULL     |       |
| x509_subject           | blob                              | NO   |     | NULL     |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0        |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0        |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0        |       |
| max_user_connections   | int(11)                           | NO   |     | 0        |       |
| plugin                 | char(64)                          | NO   |     |          |       |
| authentication_string  | text                              | NO   |     | NULL     |       |
| password_expired       | enum('N','Y')                     | NO   |     | N        |       |
| is_role                | enum('N','Y')                     | NO   |     | N        |       |
| default_role           | char(80)                          | NO   |     |          |       |
| max_statement_time     | decimal(12,6)                     | NO   |     | 0.000000 |       |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)

We see that there is a column named User where all user names are stored.

Get all mysql users.

Now it's time to show all MySQL users with the query:
select User from user;

MariaDB [mysql]> select User from user;
+------------------+
| User             |
+------------------+
| root             |
| blog_wp          |
| foro_dev         |
| joomla_test      |
+------------------+
4 rows in set (0.00 sec)

We can see that in this example we have four different users.

Generally, other user data such as the password or the host is also obtained. We achieve this with the following query:
select User,Password,Host from user;

MariaDB [mysql]> select User,Password,Host from user;
+------------------+-------------------------------------------+-----------+
| User             | Password                                  | Host      |
+------------------+-------------------------------------------+-----------+
| root             | *FEB88477570B28D1977U7CF6DA702P3DF79CB282 | localhost |
| blog_wp          | *4AB88HC7570218D197669CF6DA702K9DFC79H2PM | localhost |
| foro_dev         | *D6B8847797GB28P197669CF6DA932F5K12C9TTF2 | localhost |
| joomla_test      | *1B0569321682F7E857C469B2DD2A4F08384CC6HE | localhost |
+------------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

In addition, users can have different privileges and we can also consult them from this same table, where the fields that end with the _priv prefix refer to some type of privilege. For example, Insert_priv tells us if the user has sufficient permissions to use the INSERT .

Consult the users and the databases they have assigned.

Finally, we will use the db table where it is possible to find out which databases different users have access to . For this we will use the query:
select Host,Db,User from db;

MariaDB [mysql]> select Host,Db,User from db;
+-----------+--------+---------+
| Host      | Db     | User    |
+-----------+--------+---------+
| localhost | blog   | root    |
| localhost | ttrss  | root    |
| localhost | wp_db  | root    |
| localhost | wp_db  | blog_wp |
+-----------+--------+---------+
4 rows in set (0.00 sec)

In this case we see that the root user has access to three databases (blog, ttrss and wp_db), while the user blog_wp only has access to one (wp_db).

So far in this tutorial, since it pretends to be basic and I don't want to end up making a more complex explanation. I hope it has helped you get MySQL users and if you have any questions, there are the comments to your service.


Most popular questions within the last 20 days

...