Find Users in MariaDB
Question:Is there a query to run in MariaDB that will return all Users created?
Answer: In MariaDB, there is a system table called mysql.user. Since MariaDB is a fork of MySQL, the system table is still called mysql.user for compatibility.
You can run a query against this system table that returns all of the Users that have been created in MariaDB as well as information about these Users.
To retrieve all Users in MariaDB, you can execute the following SQL statement:
SELECT User
FROM mysql.user;
The mysql.user table contains the following columns:
Column |
Explanation |
Host |
Host for the user (ie: localhost, %, etc) |
User |
User name (ie: root, fastread.aitechtonic, etc) |
Password |
Password stored as a hashed value |
Select_priv |
Y or N (indicating whether privilege has been assigned to user) |
Insert_priv |
Y or N (indicating whether privilege has been assigned to user) |
Update_priv |
Y or N (indicating whether privilege has been assigned to user) |
Delete_priv |
Y or N (indicating whether privilege has been assigned to user) |
Create_priv |
Y or N (indicating whether privilege has been assigned to user) |
Drop_priv |
Y or N (indicating whether privilege has been assigned to user) |
Reload_priv |
Y or N (indicating whether privilege has been assigned to user) |
Shutdown_priv |
Y or N (indicating whether privilege has been assigned to user) |
Process_priv |
Y or N (indicating whether privilege has been assigned to user) |
File_priv |
Y or N (indicating whether privilege has been assigned to user) |
Grant_priv |
Y or N (indicating whether privilege has been assigned to user) |
References_priv |
Y or N (indicating whether privilege has been assigned to user) |
Index_priv |
Y or N (indicating whether privilege has been assigned to user) |
Alter_priv |
Y or N (indicating whether privilege has been assigned to user) |
Show_db_priv |
Y or N (indicating whether privilege has been assigned to user) |
Super_priv |
Y or N (indicating whether privilege has been assigned to user) |
Create_tmp_table_priv |
Y or N (indicating whether privilege has been assigned to user) |
Lock_tables_priv |
Y or N (indicating whether privilege has been assigned to user) |
Execute_priv |
Y or N (indicating whether privilege has been assigned to user) |
Repl_slave_priv |
Y or N (indicating whether privilege has been assigned to user) |
Repl_client_priv |
Y or N (indicating whether privilege has been assigned to user) |
Create_view_priv |
Y or N (indicating whether privilege has been assigned to user) |
Show_view_priv |
Y or N (indicating whether privilege has been assigned to user) |
Create_routine_priv |
Y or N (indicating whether privilege has been assigned to user) |
Alter_routine_priv |
Y or N (indicating whether privilege has been assigned to user) |
Create_user_priv |
Y or N (indicating whether privilege has been assigned to user) |
Event_priv |
Y or N (indicating whether privilege has been assigned to user) |
Trigger_priv |
Y or N (indicating whether privilege has been assigned to user) |
Create_tablespace |
Y or N (indicating whether privilege has been assigned to user) |
ssl_type |
Security column |
ssl_cipher |
Security column stored as a [BLOB] |
x509_issuer |
Security column stored as a [BLOB] |
x509_subject |
Security column stored as a [BLOB] |
max_questions |
Resource control column |
max_updates |
Resource control column |
max_connections |
Resource control column |
max_user_connections |
Resource control column |
plugin |
Security column |
authentication_string |
Security column |
password_expired |
Y or N (indicating whether the password has expired) |
is_role |
Y or N (indicating whether the user is a role) |