How to Show Users in MariaDB

Understanding how to properly display and analyze MariaDB user accounts is essential for maintaining the robust security standards expected in different enterprise environments.In this comprehensive tutorial, I’ll share the proven techniques.

How to Show Users in MariaDB

Core User Information Storage: MariaDB stores all user account information in the mysql system database, specifically within several interconnected tables that work together to provide comprehensive access control.

Table Name	Primary Purpose	Key Information Stored	Access Level Required
mysql.user	Global user accounts	Username, host, global privileges	Root/Admin
mysql.db	Database-level privileges	Database-specific permissions	Root/Admin
mysql.tables_priv	Table-level privileges	Table-specific access rights	Root/Admin
mysql.columns_priv	Column-level privileges	Field-specific permissions	Root/Admin
mysql.host	Host-based permissions	Legacy host privileges	Root/Admin

Basic Methods to Show Users in MariaDB

Method 1: Using the mysql.user Table

This fundamental approach, which I use regularly when auditing user accounts for different corporations, provides comprehensive user information:

-- Display all user accounts with basic information
SELECT 
    User,
    Host,
    account_locked,
    password_expired,
    is_role,
    default_role,
    max_connections
FROM mysql.user
ORDER BY User, Host;

Key Benefits of This Approach:

• Complete visibility: Shows all user accounts regardless of current connection status
• Security auditing: Reveals account lock status and expiration information
• Host analysis: Displays connection restrictions for compliance reviews
• Role identification: Distinguishes between users and roles in modern MariaDB versions

Method 2: Using SHOW Commands

For database administrators who prefer command-line efficiency, MariaDB provides several SHOW commands:

-- Display current database users
SHOW USERS;

-- Alternative method showing process information
SHOW PROCESSLIST;

-- Show grants for specific users
SHOW GRANTS FOR 'username'@'hostname';

SHOW Command Advantages:

• Simplicity: Easy to remember and execute quickly
• Focused output: Provides essential information without overwhelming detail
• Integration friendly: Works well in scripts and automated monitoring
• Version compatibility: Supported across different MariaDB versions

Method 3: Information Schema Queries

The Information Schema approach offers standardized access to user information, which I frequently use when integrating with enterprise monitoring systems:

-- Query user information through INFORMATION_SCHEMA
SELECT 
    GRANTEE as UserAccount,
    PRIVILEGE_TYPE as GrantedPrivilege,
    IS_GRANTABLE as CanGrantToOthers
FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE GRANTEE LIKE '%@%'
ORDER BY GRANTEE, PRIVILEGE_TYPE;

Advanced User Display Techniques

Comprehensive User Analysis Query

Throughout my work with enterprises, I’ve developed this comprehensive query that provides detailed user information suitable for security audits and compliance reporting:

-- Advanced user information query for American enterprise environments
SELECT 
    u.User as Username,
    u.Host as ConnectionHost,
    CASE 
        WHEN u.User = 'root' THEN 'System Administrator'
        WHEN u.User LIKE 'app_%' THEN 'Application Account'
        WHEN u.User LIKE 'backup_%' THEN 'Backup Service'
        WHEN u.User LIKE '%_dev' THEN 'Development Account'
        ELSE 'Business User'
    END as AccountType,
    CASE 
        WHEN u.account_locked = 'Y' THEN 'Locked'
        WHEN u.password_expired = 'Y' THEN 'Password Expired'
        WHEN u.User = '' THEN 'Anonymous'
        ELSE 'Active'
    END as AccountStatus,
    u.max_connections as MaxConnections,
    u.max_questions as MaxQueriesPerHour,
    u.max_updates as MaxUpdatesPerHour,
    CASE 
        WHEN u.ssl_type = 'ANY' THEN 'SSL Required'
        WHEN u.ssl_type = 'X509' THEN 'Certificate Required'
        WHEN u.ssl_type = 'SPECIFIED' THEN 'Specific SSL Config'
        ELSE 'No SSL Requirement'
    END as SecurityLevel,
    u.plugin as AuthenticationMethod
FROM mysql.user u
WHERE u.User != ''  -- Exclude anonymous users
ORDER BY 
    AccountType, 
    AccountStatus, 
    Username;

User Privilege Analysis

For American corporations requiring detailed privilege auditing, this query reveals comprehensive permission information:

-- Detailed privilege analysis for compliance reporting
WITH UserPrivileges AS (
    SELECT 
        User,
        Host,
        CONCAT(
            CASE WHEN Select_priv = 'Y' THEN 'SELECT,' ELSE '' END,
            CASE WHEN Insert_priv = 'Y' THEN 'INSERT,' ELSE '' END,
            CASE WHEN Update_priv = 'Y' THEN 'UPDATE,' ELSE '' END,
            CASE WHEN Delete_priv = 'Y' THEN 'DELETE,' ELSE '' END,
            CASE WHEN Create_priv = 'Y' THEN 'CREATE,' ELSE '' END,
            CASE WHEN Drop_priv = 'Y' THEN 'DROP,' ELSE '' END,
            CASE WHEN Grant_priv = 'Y' THEN 'GRANT,' ELSE '' END,
            CASE WHEN Super_priv = 'Y' THEN 'SUPER,' ELSE '' END
        ) as GlobalPrivileges
    FROM mysql.user
    WHERE User != ''
)
SELECT 
    User,
    Host,
    CASE 
        WHEN GlobalPrivileges LIKE '%SUPER%' THEN 'Administrative'
        WHEN GlobalPrivileges LIKE '%GRANT%' THEN 'Privileged'
        WHEN GlobalPrivileges LIKE '%CREATE%' OR GlobalPrivileges LIKE '%DROP%' THEN 'Developer'
        WHEN GlobalPrivileges LIKE '%SELECT%' AND GlobalPrivileges NOT LIKE '%INSERT%' THEN 'Read-Only'
        ELSE 'Limited Access'
    END as AccessLevel,
    TRIM(TRAILING ',' FROM GlobalPrivileges) as SpecificPrivileges
FROM UserPrivileges
ORDER BY AccessLevel, User;

Best Practices

User Management Security Standards

Based on my implementations for major corporations, these security practices are essential:

Account Naming Conventions:

• Service accounts: svc_applicationname (e.g., svc_payroll, svc_crm)
• Application users: app_systemname (e.g., app_ecommerce, app_analytics)
• Human users: firstname_lastname (e.g., john_smith, sarah_johnson)
• Administrative accounts: admin_purpose (e.g., admin_backup, admin_monitoring)

Host Restriction Guidelines:

Environment Type	Recommended Host Pattern	Security Level	Use Case
Production	Specific IP/hostname	Maximum	'app_user'@'prod-server01.company.com'
Development	Subnet restriction	High	'dev_user'@'192.168.10.%'
Testing	Network segment	Medium	'test_user'@'%.testnet.company.com'
Emergency	Localhost only	Maximum	'emergency'@'localhost'

Monitoring and Alerting Implementation

For businesses requiring proactive monitoring, implement these alerting thresholds:

-- Create monitoring view for American enterprise dashboards
CREATE VIEW v_UserSecurityDashboard AS
SELECT 
    'User Account Security Summary' as DashboardSection,
    COUNT(*) as TotalAccounts,
    SUM(CASE WHEN account_locked = 'N' AND password_expired = 'N' AND User != '' THEN 1 ELSE 0 END) as ActiveAccounts,
    SUM(CASE WHEN User = '' THEN 1 ELSE 0 END) as AnonymousAccounts,
    SUM(CASE WHEN Host = '%' THEN 1 ELSE 0 END) as UnrestrictedHostAccounts,
    SUM(CASE WHEN Super_priv = 'Y' THEN 1 ELSE 0 END) as SuperUserAccounts,
    SUM(CASE WHEN password_expired = 'Y' THEN 1 ELSE 0 END) as ExpiredPasswordAccounts,
    ROUND(
        (SUM(CASE WHEN account_locked = 'Y' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2
    ) as LockedAccountPercentage
FROM mysql.user;

-- Alert conditions for American enterprise security teams
SELECT 
    CASE 
        WHEN AnonymousAccounts > 0 THEN 'CRITICAL: Anonymous accounts present'
        WHEN SuperUserAccounts > 3 THEN 'WARNING: High number of super users'
        WHEN UnrestrictedHostAccounts > 2 THEN 'WARNING: Accounts with unrestricted host access'
        WHEN ExpiredPasswordAccounts > ActiveAccounts * 0.1 THEN 'WARNING: High percentage of expired passwords'
        WHEN LockedAccountPercentage > 50 THEN 'INFO: High percentage of locked accounts'
        ELSE 'OK: Security metrics within acceptable ranges'
    END as SecurityAlert,
    TotalAccounts, ActiveAccounts, AnonymousAccounts, 
    UnrestrictedHostAccounts, SuperUserAccounts, ExpiredPasswordAccounts
FROM v_UserSecurityDashboard;

Conclusion

Effective user management begins with comprehensive visibility into user accounts and their associated privileges.

Strategic Implementation for Enterprises

Security-First Approach: The techniques outlined in this tutorial provide businesses with the tools necessary to maintain robust security standards while ensuring operational efficiency. Whether you’re supporting healthcare systems requiring HIPAA compliance or financial institutions meeting SOX requirements, these user display methods form the foundation of effective access control management.

Scalability Considerations: As businesses continue expanding their digital footprints, the ability to efficiently analyze and monitor user accounts becomes increasingly critical. The advanced queries and monitoring procedures I’ve shared scale effectively from small startups to enterprise-level deployments supporting thousands of concurrent users.

Compliance and Auditing: American regulatory environments demand comprehensive user access documentation and monitoring capabilities. The reporting techniques presented here provide the detailed audit trails and security assessments required for regulatory compliance while supporting proactive security management.

You may also like the following articles:

• MariaDB Display List of Databases
• How to Access MariaDB in Linux