大网数据广告活动
服务器运维问答

List all users of a MariaDB

· 52CE 我爱测

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 wit…

问题原文

原帖链接 · H. Weirauch · CC BY-SA 4.0

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.

已采纳解答

原帖链接 · Esa Jokinen · CC BY-SA 4.0

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;

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2023-06-11)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。