1

How can I list all users in my MariaDB?

Teh Internet is full of

SELECT user FROM mysql.user;

in all its variations, but this does not work on MariaDB (I'm on version 10.5.19 if that matters), rather it produces the error (run with root privileges):

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I found only one single comment that is vaguely helpful: https://stackoverflow.com/a/64841540

MariaDB-10.4+ the mysql.user is a view rather than a table.

[…] Always check the official documentation.

Point is, I did check the official documentation, but it appears to silently assume that you already know your user names. I did not find any mention of how to get these in the first place, i. e. how to just display a list of users.

H. Weirauch
  • 21

2 Answers2

7

The official documentation for mysql.user Table (the first result from Google with mysql.user site:https://mariadb.com/kb/) has your answer:

In MariaDB 10.4 and later, the mysql.global_priv table has replaced the mysql.user table, and mysql.user should be considered obsolete. It is now a view into mysql.global_priv created for compatibility with older applications and monitoring scripts. New tools are supposed to use INFORMATION_SCHEMA tables.

So, for backwards compatibility you can still use:

SELECT User FROM mysql.global_priv;

This might also be useful:

SELECT GRANTEE 
FROM INFORMATION_SCHEMA.USER_PRIVILEGES 
GROUP BY GRANTEE;
Esa Jokinen
  • 49,773
0

Well, the matter that it's a view does not really matter, you should be able to select User from mysql.user anyways ... the error message you get indicates that the user you are connecting with do not have permission to select from mysql.user though ... so try:

echo select User from mysql.user | mysql -u root

if that works, make sure the user you connect with has permission to select from from mysql.user :-)

fraxflax
  • 76
  • Nope … ERROR 1356 (HY000) at line 1: View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them – H. Weirauch Jun 11 '23 at 17:24
  • ok, don't know what distribution you are running but you can check the admin user (usually root) access under /etc/mysql:

    grep root /etc/mysql/**

    to get to know which file(s) to check

     – fraxflax Jun 12 '23 at 10:39
  • In Debian the root user is password-less and only have access on localhost (same machine as the server is running): 
    created anymore. Instead the MariaDB root account is set to be authenticated
using the Unix socket, e.g. any mysqld invocation by root or via sudo will
let the user see the mysqld prompt.

You may never ever delete the mysql user "root". Although it has no password
is set, the unix_auth plugin ensure that it can only be run locally as the root
user.```
    – fraxflax Jun 12 '23 at 10:44