Score:1

Server

List all users of a MariaDB

H. Weirauch

6/11/24, 2:45 PM

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.

323

2 + 5

mariadb

Esa Jokinen

6/11/24, 3:36 PM

Which user you are running the queries with?

Romeo Ninov

6/11/24, 3:45 PM

Have you try `SELECT User FROM mysql.user;`?

H. Weirauch

6/11/24, 5:27 PM

@EsaJokinen: root, as I've written.

H. Weirauch

6/11/24, 5:27 PM

@Romeo Ninov: Yes, I have tried all combinations of upper/lowercase, to no avail.

Romeo Ninov

6/11/24, 5:36 PM

try something like: `mysql -u root` then in mysql: `use mysql; show tables;`

Score:6

Server

Esa Jokinen

6/11/24, 6:40 PM

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;

+ 0

Score:0

Server

frax

6/11/24, 4:10 PM

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 :-)

+ 3

H. Weirauch

6/11/24, 5:24 PM

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`

frax

6/12/24, 10:39 AM

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

frax

6/12/24, 10:44 AM

In Debian the root user is password-less and only have access on localhost (same machine as the server is running): ```On new installs no root password is set and no debian-sys-maint user is 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.```

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: List all users of a MariaDB

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.