Since MariaDB 10.4, the traditional mysql.user table has been replaced with a view. This architectural change explains why running:

SELECT user FROM mysql.user;

throws error 1356 about invalid references. The view depends on underlying tables that require specific privileges.

To retrieve all users in MariaDB 10.4+, use:

SELECT User FROM mysql.global_priv;

Alternatively, you can query the view with proper privileges:

SELECT * FROM information_schema.USER_PRIVILEGES;

For comprehensive user data including authentication details, run:

SELECT 
    User, 
    Host, 
    plugin AS AuthenticationMethod,
    password_expired AS PasswordExpired,
    account_locked AS AccountLocked
FROM mysql.global_priv;

Case 1: If you get permission errors even as root, try:

FLUSH PRIVILEGES;
SELECT User FROM mysql.global_priv;

Case 2: To see which users have specific privileges:

SELECT * FROM mysql.user WHERE Create_priv = 'Y';

For database administrators, these additional commands are useful:

SHOW GRANTS FOR CURRENT_USER();
SHOW DATABASES;
SELECT DISTINCT User FROM mysql.db;

• The mysql.global_priv table contains the most accurate user information
• Always include the Host column when working with user accounts
• User management commands may differ between MariaDB and MySQL

---

Most database administrators coming from MySQL background instinctively try to list users with:

SELECT user FROM mysql.user;

However, in MariaDB 10.4+, this approach fails with:

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

MariaDB 10.4 introduced a significant architectural change where mysql.user became a view rather than a table. This view depends on underlying system tables that might not be accessible without proper privileges.

Here are several reliable methods to list users in recent MariaDB versions:

Method 1: Query the global_priv Table

SELECT User FROM mysql.global_priv;

Method 2: Use SHOW USERS Command

SHOW USERS;

Method 3: Full User Details Query

SELECT 
    user AS User,
    host AS Host,
    password_expired AS Password_expired,
    account_locked AS Account_locked
FROM mysql.global_priv;

For more detailed user information including privileges:

SELECT * FROM information_schema.USER_PRIVILEGES;

Or to see which users can access specific databases:

SELECT * FROM information_schema.SCHEMA_PRIVILEGES;

If you still encounter permission issues:

1. Ensure you're connecting as root or a user with SUPER privileges
2. Try running FLUSH PRIVILEGES; before your query
3. For MariaDB Galera clusters, check node synchronization status

For large user bases, these queries are more efficient:

-- Fast user count
SELECT COUNT(*) FROM mysql.global_priv;

-- Paginated results
SELECT User FROM mysql.global_priv LIMIT 50 OFFSET 100;