Listing and Managing Users in MariaDB

Understanding user accounts is critical for administering secure and compliant MariaDB database deployments. This comprehensive guide will educate you on listing MariaDB users via SQL queries, system views and command-line tools. Best practices are also provided for lifecycle user access control and auditing.

Let‘s get started.

Overview

MariaDB, a popular open source database, fully supports complex access control mechanisms to protect data. The authorization model includes:

Users – Represent individual database users

Privileges – Discrete permissions like SELECT or INSERT

Objects – Databases, tables and stored procedures

Various privilege types can be granted to usernames, allowing highly customized access permissions.

For example, allowing the reporting user to query the sales table:

GRANT SELECT ON analytics.sales TO ‘reporting‘@‘appserver‘;

Understanding provisioned users is therefore critical from an access control perspective.

53% of organizations suffer data loss due to underprivileged misconfigurations according to 2022 Insider Threat Report. Listing users helps avoid these pitfalls.

Native MariaDB Options

MariaDB offers native support for visibility into provisioned users via SQL queries and client interfaces.

Querying mysql.user Table

The central repository for user account data resides in the mysql database within the user table.

SELECT User, Host FROM mysql.user;

This structured table tracks username, hostname, password hash, global privileges and other attributes per account.

Additional columns like Password, Account_locked and plugin expose further useful details on the accounts.

MariaDB System Views

Beyond the mysql.user table itself, optimized system views are available:

mysql.user – Most key user columns

information_schema.USER_PRIVILEGES – Granted global privileges

information_schema.SCHEMA_PRIVILEGES – Privileges per database

information_schema.TABLE_PRIVILEGES – Table grants

Each surfaces user data from different perspectives – like access control, permissions and auditing.

SELECT * FROM information_schema.USER_PRIVILEGES;   

MariaDB Command Line Client

The native clients for MariaDB and MySQL allow interacting directly with the connected database instance.

This enables listing active user sessions via process lists rather than account configuration data.

MariaDB [(none)]> SELECT CURRENT_USER();
MariaDB [(none)]> SELECT USER(); 

MariaDB [(none)]> SHOW PROCESSLIST;  

16% of breaches originate from privileged users like database administrators according to Verizon‘s 2022 DBIR. Auditing active sessions helps mitigate internal threats.

Listing All MariaDB User Accounts

When getting started with user visibility, retrieving all accounts is typically the first step:

All User Details

SELECT User, Host, password, plugin
FROM mysql.user;

This exposes basic account attributes.

All User Privileges

SELECT *
FROM information_schema.USER_PRIVILEGES; 

That adds visibility into the role-based access control (RBAC) permissions per account.

Combining data points gives complete user visibility – crucial for enforcing least privilege.

Example Scenario:

Performing security audits before migrating legacy databases or upgrading versions. Comparing changes between existing permissions and new environments facilitates a smooth and secure transition.

Having historical accountability data is also required for many compliance regulations.

Listing Specific MariaDB User Accounts

While listing all users has value, often filtering to specific accounts is required.

By Username

SELECT User, Host 
FROM mysql.user
WHERE User LIKE ‘%admin%‘;

Locates accounts with names containing ‘admin‘.

By Host

SELECT User, Host
FROM mysql.user  
WHERE Host LIKE ‘%.servers.com‘;

Finds accounts tied to hosts in the servers.com domain.

By Date

SELECT User, Host, password_last_changed 
FROM mysql.user
WHERE password_last_changed 
      > CURRENT_DATE - INTERVAL 30 DAY;

Shows accounts with passwords changed in past 30 days.

Generic User Filter

SELECT * 
FROM mysql.user
WHERE User = ‘billing_rw‘
  AND Host = ‘192.168.0.11‘\ 
  AND plugin = ‘mysql_native_password‘;  

Filters on any user table columns.

These allow inspecting particular subsets of accounts – like external vendors, regulated data groups or newly suspicious logins detected.

Example Scenario:

A media site suffers a content scraping attack extracting millions of records using compromised credentials. Listing users by recent creation dates and hosts identifies potentially malicious accounts. Password resets and access revocation helps contain the breach.

Listing Privileges for MariaDB Users

While account attributes provide user visibility, viewing precise access permissions granted is equally important.

By Username

SELECT * FROM information_schema.SCHEMA_PRIVILEGES 
WHERE GRANTEE LIKE ‘%etl%‘;

Shows database and table grants for ETL process accounts.

By Privilege Type

SELECT * FROM information_schema.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE = ‘BACKUP_ADMIN‘;

Locates accounts having backup admin grants.

By Database

SELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = ‘employees‘;

Displays all user privileges within employees database.

These help implement least privilege and mandatory access control models by revealing extraneous grants assigned over time.

Example Scenario:

A financial organization empowers a new account security team to help address regulatory audit findings around excess privileges enabled in databases. Listing users and privileges focusing on finance transaction systems helps remediate these high risk gaps.

Comparing SQL and CLI User Listing

As outlined already, MariaDB offers several SQL-based options for listing user accounts – focused primarily on configuration data.

Alternatively, leveraging the interactive MariaDB or MySQL command line clients allows viewing active user connections and instances.

Configuration Listing

Focuses on:

• User creation metadata
• Account attributes
• Access control grants
• Database permissions

Activity Listing

Focuses on:

• Active user sessions
• Running database queries
• Current commands
• Temporary privilege usage

Complementing SQL configuration queries with CLI activity commands delivers comprehensive user monitoring.

For example, you may revoke SQL write privileges but that user could still have open CLI sessions with cached temporary grants permitting continued access.

Together these approaches help address common issues like privilege creep, excessive user permissions, and underprivileged account misconfigurations.

Best Practices for MariaDB Users

Properly administering user accounts is challenging yet critical for security. Consider several best practices:

Enforce Least Privilege

Grant users only the minimum database and table access explicitly required for duties. Avoid broad toplevel privileges. Temporarily escalate as needed via roles.

Implement Access Reviews

Routinely audit privileges ensuring alignment with current responsibilities. This also helps comply with controls like segregation of duties.

Focus on Privileged Users

Scrutinize highly privileged accounts via activity logging and multi-factor authentication (MFA). Disable unused root accounts.

Utilize Roles

Group common privileges into roles assigned to users. This simplifies access management as teams and duties shift across an organization.

For example:

CREATE ROLE read_only;
GRANT SELECT ON *.* TO ‘read_only‘;

GRANT ‘read_only‘ TO ‘temp_user‘;

Rename Default Accounts

Rename easily guessed accounts like root upon installation. Avoid common admin usernames when possible.

Require Secure Passwords

Enforce expirations, MFA and complexity requirements for credentials protecting sensitive data.

Revoking Permissions

Remove user access by revoking privileges rather than deleting accounts. Retaining deactivated accounts aids future forensics.

Following security best practices is vital for avoiding database breaches, unintended data leaks and access control violations. 95% of organizations fail compliance audits for preventable access control gaps according to 2022 Data Risk Report.

Prioritizing user visibility and adopting the measures above reduces these risks substantially.

Scenario Examples of Listing MariaDB Users

Let‘s explore some common scenarios taking advantage of native SQL queries for listing users and diagnosing issues.

Troubleshooting Access Denials

Applications or users begin reporting failed logins or permission errors accessing MariaDB resources.

SELECT user, host, password_last_changed, account_locked 
FROM mysql.user
WHERE user LIKE ‘%billing%‘;

Checking account locks, recent password changes and other attributes indicates why billing team members lost database access.

Investigating Privilege Anomalies

Routine user access reviews uncover several accountants granted developer permissions.

SELECT GRANTEE, PRIVILEGE_TYPE  
FROM information_schema.USER_PRIVILEGES 
WHERE GRANTEE LIKE ‘%account%‘;

Listing precise privileges for the accounting team reveals how unnecessary access crept in over last year.

Validating Least Privilege

Your organization is adopting a zero trust model with restricted access for all users.

SELECT DISTINCT GRANTEE FROM information_schema.USER_PRIVILEGES;

SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE  
FROM information_schema.USER_PRIVILEGES; 

Comparing total unique user counts with those having elevated GRANT OPTION helps identify risks as focus areas for minimizing privileges.

Detecting Privilege Sprawl

Your CISO warns that rights accumulation among long-tenured users enables vulnerabilities.

SELECT USER, HOST, password_last_changed, Create_time
FROM mysql.user
ORDER BY Create_time DESC;

Listing users by age and crawling grants over time identifies proliferation areas to reign in.

These examples demonstrate applying native MariaDB user listings securely in real world scenarios.

Securing User Access Lifecycle

Beyond everyday visibility, certain events trigger specific listing use cases:

Stage	SQL Listings	Best Practices
Provisioning	Validating new user aligns to access request tickets	Enforce least privilege
Privilege Changes	Retrieve grants before and after escalations	Require ticket linkage
Access Reviews	Compare assigned versus actual privilege usage	Maintain audit logs
Revocation	Document deprecated permissions for offboarding	Expire rather than delete
Breaches	Pivot user listings to detection search queries	Develop incident response plans

Taking a lifecycle approach ensures all stages have tailored user account practices to meet business needs while still securing data.

Conclusion

Understanding MariaDB users is the foundation for protecting database security. SQL queries against mysql.user and information_schema provide administrator visibility across configured users, active sessions and fine-grained privileges. Complement these listings with CLI process tools and user access best practices to effectively control all stages of the MariaDB user lifecycle – from provisioning through revocation.

What scenarios require tailored user listings within your organization? Evaluate where enhanced visibility using the solutions outlined could help optimize security, compliance and everyday database administration.