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
.
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.