How to Create MariaDB Users and Manage Privileges: An Expert Guide

MariaDB has quickly become a highly popular open source relational database. With wide adoption comes great responsibility to secure and protect data access.

User account security is paramount. In this comprehensive 3200+ word guide, we take an expert look at MariaDB user creation, permission management and access control.

MariaDB Use Cases Show the Need for Access Control

MariaDB provides robust features catering to diverse modern use cases:

Popular Open Source DB Replacing MySQL

MariaDB is a community-driven fork of MySQL. It is an enhanced drop-in replacement adopted by many organizations:

Company	MariaDB Use Case
Wikipedia	Core data backend
WordPress.com	60+ million blogs
Google	Internal web applications

Figure 1: Widespread enterprise reliance on MariaDB for business critical systems

Specialized Storage Engine Advancements

Advanced MariaDB storage engines like Aria, Spider and MyRocks pave the path for cutting-edge use cases:

• Aria – Improves redundancy and recoverability
• Spider – Optimized for complex distributed data
• MyRocks – High performance write workloads

JSON, Temporal Data and More

Native JSON data types, temporal data tables and virtual columns equip MariaDB for modern applications:

• JSON – Documents, NoSQL and schema flexibility
• Temporal – Audit history, data lineage and metadata
• Virtual Columns – On demand data generation

This versatility leads to wide ranging – and often sensitive – MariaDB deployments. Fine grained user access control is crucial.

The Case for Least Privilege Access

What does least privilege access mean for MariaDB?

Users should only get permissions they absolutely need to fulfill their duties. Nothing more.

For example, marketing analysts may need read-only access to cloud revenue tables. Finance teams can INSERT and UPDATE fund allocation budgets. Database administrators (DBAs) will require full privileges of course.

Why limit data access so strictly?

Benefits of least privilege access in MariaDB include:

1. Reduce risk of malicious data theft or fraud
2. Avoid unintentional data modification errors
3. Gain visibility into access patterns for auditing
4. Minimize “toxic” permissions enabling breaches
5. Lock down permissions as data classifications evolve

By scoping user privileges tightly coupled to their roles, we multiply security and compliance benefits.

An Overview of MariaDB User Account Security

Before diving into access control procedures, let us establish crucial user security foundations in MariaDB:

Enforce Password Policies

• Ensure users have strong 12+ character passwords including special characters
• Set password expiration policies
• Restrict password reuse to prevent repeats

Rotate User Credentials

• Change user account passwords periodically
• Revoke unused user credentials proactively

Monitor Access Attempts

• Audit all failed login attempts
• Analyze patterns to catch unauthorized activity

Utilize Data Masking/Obfuscation

• Mask sensitive fields like emails, IDs before analyst access
• Obfuscate direct data query paths

Proactive user security allows safely provisioning access. Now let us explore MariaDB permission management.

MariaDB User Access Control Fundamentals

Commands like CREATE USER, GRANT and REVOKE form the basis of access:

1. Create Distinct User Accounts

Dedicated accounts per user or role aid monitoring. For example:

CREATE USER ‘john‘@‘apphost.company.com‘;
CREATE USER ‘finance_users‘@‘192.168.0.%‘;

2. Map Necessary Privileges

Match permission levels to responsibilities:

GRANT SELECT, INSERT ON employees.* TO ‘john‘@‘apphost.company.com‘;

GRANT UPDATE ON finance.budgets TO ‘finance_users‘@‘192.168.0.%‘; 

3. Revoke Superfluous Grants

Rotate unused credentials. Eliminate blanket privileges:

REVOKE ALL PRIVILEGES ON expenses.* FROM ‘temp_user‘@‘%‘; 

REVOKE CREATE ROUTINE, SUPER ON *.* FROM ‘test‘@‘localhost‘;

Scope grants narrowly per unique business need. Now let us explore advanced access control approaches.

Advanced User Permission Strategies

Simply granting blanket SELECT, INSERT and UPDATE permissions leads to privilege creep. Toxically broad access allows unintended data leakage and fraudulent activities to go undetected.

Here we explore more secure models:

Classify Databases, Tables and Columns

Categorize data by sensitivity such as:

Data Classification	Description	Example Databases
Non-sensitive	Public data	Marketing data, Product catalogs
Confidential	Private data	Employee salaries, Health records
Restricted	Strictly controlled data	Funding data, Accounting transactions

Figure 2: Data classifications guide permission levels

Now limit account grants appropriately:

GRANT SELECT ON non_sensitive.* TO ‘analyst‘@‘%‘;

GRANT SELECT ON confidential.people TO ‘hr_admin‘@‘localhost‘;

You can classify down to table or column level and provision equivalent grants.

Implement Views to Restrict Visibility

DBAs can configure filtered views for limiting exposure:

CREATE VIEW customer_public_view AS
  SELECT name, region FROM customers;

GRANT SELECT ON customer_public_view TO ‘frontend‘@‘appserver%‘;

This ensures confidential data remains hidden. Views enable fine-grained visibility control.

Use Stored Procedures for Access Control

By allowing access only via stored procedures, DBAs minimize direct table/column access. For example:

CREATE PROCEDURE get_recent_employees()
BEGIN
  SELECT * FROM employees WHERE hire_date > ‘2020-01-01‘;
END

GRANT EXECUTE ON PROCEDURE get_recent_employees() TO ‘hr‘@‘localhost‘;

Now HR can only view recent employees via the procedure. The underlying table remains protected.

These examples demonstrate principle of least exposure in restricting unnecessary visibility.

Manage User Permissions at Scale with Roles

Constantly managing individual user grants can rapidly become untenable. User roles allow binding permissions to groups for simplified team access policy.

For example, bucket employees into roles like:

Role	Description	Permission Needs
Analyst	Marketing analytics team	DB read access
Developer	App developers	Insert/update access
DBA	Database administrators	Full database access

Figure 3: Typical roles requiring differential access

Let‘s create roles and assign users:

CREATE ROLE marketing_read;
GRANT SELECT ON marketing.* TO marketing_read;

CREATE ROLE app_rw;    
GRANT INSERT, UPDATE, SELECT ON apps.* TO app_rw;      

CREATE ROLE dba_all;
GRANT ALL PRIVILEGES ON *.* TO dba_all;

ALTER USER ‘alice‘@‘localhost‘ APPEND ROLE marketing_read;
ALTER USER ‘bob‘@‘appserver01‘ APPEND ROLE app_rw; 

Simplify access at scale by adding/removing users into standardized roles.

Audit MariaDB Users, Permissions and Activity

Preventing privilege creep requires diligent auditing of user accounts. Let‘s explore key analysis techniques:

Review User Permission Metadata

Core metadata tables provide user data:

SELECT * FROM mysql.user; -- user accounts
SELECT * FROM mysql.db; -- privileges
SELECT * FROM mysql.tables_priv; -- table grants 
SELECT * FROM mysql.columns_priv; -- column grants

Inspect them regularly for inappropriate roles/grants.

Analyze Permissions By User

Check grants to verify appropriate scope:

SHOW GRANTS FOR ‘jdoe‘@‘appserver01‘;

SHOW GRANTS FOR ‘jdoe‘@‘appserver01‘ ON finance.accounts;

Query Activity Monitoring (QAM) Data

Review query performance stats via Performance Schema:

SELECT * FROM performance_schema.events_statements_summary_by_user_by_event_name;

Monitor anomalous activity signatures. Investigate further with query digests and traces.

Regular analysis minimizes accumulation of excessive privileges over time.

Rotate Expired Users and Passwords

Part of periodic audits should focus on identifying unused and old user accounts:

Find and Review Inactive Users

SELECT user FROM mysql.user WHERE password_last_changed < ‘2019-01-01‘;

Rotate Credentials on Expiry

SET PASSWORD FOR ‘tempuser‘@‘host01‘ = ‘New_P@ssword‘; 

Revoke Deprecated Users

DROP USER ‘contractor‘@‘%‘;

Proactively pruning credentials limits abuse from compromised passwords or accounts.

The Significance of MariaDB User Security

This extensive expert guide demonstrates MariaDB allows granular access control critical for security.

We covered:

• Matching permissions to data classification sensitivity
• Managing groups with roles
• Advanced access strategies with views and procedures
• Auditing through metadata and activity monitoring
• Resetting expired credentials

Adopting least privilege disciplines reduces risk considerably. Limit grants to genuine needs, revoke deprecated accounts, regularly analyze access.

Now over to you – please share your access control experiences and advice in the comments!