MySQL Security: User Management and Privileges

MySQL Security: User Management and Privileges

Database security is paramount in protecting sensitive data. MySQL provides a robust privilege system that controls what users can do and which data they can access. Understanding and properly configuring MySQL security is essential for every DBA and developer.

🎯 MySQL Security Overview

Security Layers

┌─────────────────────────────────────────────────────────────────────┐
│                    MYSQL SECURITY LAYERS                             │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  1. Network Security          2. Authentication                     │
│     - Firewall rules             - Username/Password                │
│     - SSL/TLS encryption         - Authentication plugins           │
│     - Bind address               - Password policies                │
│                                                                      │
│  3. Authorization              4. Data Security                     │
│     - User privileges            - Encryption at rest               │
│     - Role-based access          - Audit logging                    │
│     - Object-level grants        - Data masking                     │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

💡 Key Principle: Apply the principle of least privilege - users should only have the minimum permissions necessary to perform their tasks.

🔧 User Management

Creating Users

-- Basic user creation
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePassword123!';

-- User with specific host
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'SecurePassword123!';

-- User with any host (less secure)
CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePassword123!';

-- User with password expiration
CREATE USER 'temp_user'@'localhost'
    IDENTIFIED BY 'TempPass123!'
    PASSWORD EXPIRE INTERVAL 90 DAY;

-- User with account locking
CREATE USER 'contractor'@'%'
    IDENTIFIED BY 'ContractorPass!'
    ACCOUNT LOCK;

-- User with resource limits
CREATE USER 'api_user'@'%'
    IDENTIFIED BY 'ApiPass123!'
    WITH MAX_QUERIES_PER_HOUR 10000
         MAX_UPDATES_PER_HOUR 1000
         MAX_CONNECTIONS_PER_HOUR 100
         MAX_USER_CONNECTIONS 10;

Authentication Plugins (MySQL 8.0)

-- Default authentication (caching_sha2_password in MySQL 8.0)
CREATE USER 'modern_user'@'%'
    IDENTIFIED WITH caching_sha2_password BY 'SecurePass123!';

-- Legacy authentication (for older clients)
CREATE USER 'legacy_user'@'%'
    IDENTIFIED WITH mysql_native_password BY 'LegacyPass123!';

-- LDAP authentication (Enterprise)
CREATE USER 'ldap_user'@'%'
    IDENTIFIED WITH authentication_ldap_simple
    AS 'uid=ldap_user,ou=users,dc=example,dc=com';

-- View authentication plugins
SELECT user, host, plugin FROM mysql.user;

Password Policies

-- Configure password policy (MySQL 8.0)
-- In my.cnf or SET GLOBAL:

-- Password validation plugin settings
SET GLOBAL validate_password.policy = 'STRONG';  -- LOW, MEDIUM, STRONG
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- Check current policy
SHOW VARIABLES LIKE 'validate_password%';

-- Set password expiration globally
SET GLOBAL default_password_lifetime = 90;

-- Password history (prevent reuse)
SET GLOBAL password_history = 5;
SET GLOBAL password_reuse_interval = 365;

Managing Users

-- Alter user password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewSecurePass123!';

-- Expire password immediately
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;

-- Lock/Unlock account
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

-- Rename user
RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

-- Drop user
DROP USER 'app_user'@'localhost';
DROP USER IF EXISTS 'temp_user'@'%';

-- View all users
SELECT User, Host, account_locked, password_expired
FROM mysql.user
ORDER BY User;

🔐 Privilege System

Privilege Levels

Common Privileges

-- Data privileges
GRANT SELECT ON database.* TO 'readonly_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'app_user'@'%';

-- Structure privileges
GRANT CREATE, ALTER, DROP, INDEX ON database.* TO 'developer'@'%';

-- Administrative privileges
GRANT CREATE USER, RELOAD, PROCESS ON *.* TO 'admin_user'@'localhost';

-- All privileges on a database
GRANT ALL PRIVILEGES ON database.* TO 'db_owner'@'localhost';

-- Global superuser (use sparingly!)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

Privilege Reference

Column-Level Privileges

-- Grant SELECT on specific columns only
GRANT SELECT (id, username, email, created_at)
ON database.users TO 'limited_user'@'%';

-- Grant UPDATE on specific columns
GRANT UPDATE (status, updated_at)
ON database.orders TO 'status_updater'@'%';

-- View column privileges
SELECT * FROM information_schema.COLUMN_PRIVILEGES
WHERE GRANTEE LIKE '%limited_user%';

Revoking Privileges

-- Revoke specific privilege
REVOKE INSERT, UPDATE ON database.* FROM 'user'@'host';

-- Revoke all privileges on database
REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'host';

-- Revoke GRANT OPTION
REVOKE GRANT OPTION ON database.* FROM 'user'@'host';

-- Revoke all and drop user cleanly
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';
DROP USER 'user'@'host';

👥 Role-Based Access Control (MySQL 8.0+)

Creating and Using Roles

-- Create roles
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant privileges to roles
GRANT SELECT ON myapp.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin';

-- Grant roles to users
GRANT 'app_read' TO 'reporting_user'@'%';
GRANT 'app_read', 'app_write' TO 'app_user'@'%';
GRANT 'app_admin' TO 'admin_user'@'localhost';

-- Set default roles for users
SET DEFAULT ROLE 'app_read' TO 'reporting_user'@'%';
SET DEFAULT ROLE ALL TO 'app_user'@'%';

-- Activate roles in current session
SET ROLE 'app_read';
SET ROLE ALL;
SET ROLE NONE;

Hierarchical Roles

-- Create role hierarchy
CREATE ROLE 'junior_dev', 'senior_dev', 'lead_dev';

-- Junior developer: read only
GRANT SELECT ON dev_db.* TO 'junior_dev';

-- Senior developer: includes junior + write
GRANT 'junior_dev' TO 'senior_dev';
GRANT INSERT, UPDATE ON dev_db.* TO 'senior_dev';

-- Lead developer: includes senior + admin
GRANT 'senior_dev' TO 'lead_dev';
GRANT CREATE, ALTER, DROP, INDEX ON dev_db.* TO 'lead_dev';

-- Assign to users
GRANT 'junior_dev' TO 'new_hire'@'%';
GRANT 'senior_dev' TO 'experienced_dev'@'%';
GRANT 'lead_dev' TO 'team_lead'@'%';

View Role Assignments

-- Show roles for current user
SELECT CURRENT_ROLE();

-- Show roles granted to a user
SHOW GRANTS FOR 'app_user'@'%';

-- Show all role grants
SELECT * FROM mysql.role_edges;

-- Show all roles
SELECT User, Host FROM mysql.user WHERE account_locked = 'Y';

📊 Security Monitoring

Auditing User Activity

-- View current connections
SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 50) AS query_preview
FROM information_schema.PROCESSLIST
WHERE USER != 'system user';

-- Check login failures (requires audit log or error log)
-- In error log, look for:
-- [Warning] Access denied for user 'user'@'host'

-- View user privileges
SHOW GRANTS FOR 'app_user'@'localhost';

-- View all grants in the system
SELECT * FROM information_schema.USER_PRIVILEGES;
SELECT * FROM information_schema.SCHEMA_PRIVILEGES;
SELECT * FROM information_schema.TABLE_PRIVILEGES;

Security-Related Variables

-- Check security settings
SHOW VARIABLES LIKE '%password%';
SHOW VARIABLES LIKE '%ssl%';
SHOW VARIABLES LIKE '%auth%';

-- Important security variables
SELECT @@require_secure_transport;
SELECT @@validate_password.policy;
SELECT @@default_password_lifetime;
SELECT @@sql_mode;  -- Should include NO_AUTO_CREATE_USER

Finding Security Issues

-- Find users without passwords (security risk!)
SELECT User, Host FROM mysql.user WHERE authentication_string = '';

-- Find users with wildcard hosts
SELECT User, Host FROM mysql.user WHERE Host = '%';

-- Find users with excessive privileges
SELECT GRANTEE, PRIVILEGE_TYPE
FROM information_schema.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE IN ('ALL', 'SUPER', 'FILE', 'PROCESS', 'SHUTDOWN')
ORDER BY GRANTEE;

-- Find users with GRANT OPTION
SELECT GRANTEE, IS_GRANTABLE
FROM information_schema.USER_PRIVILEGES
WHERE IS_GRANTABLE = 'YES';

🔒 Security Hardening

Post-Installation Security

-- 1. Set root password (if not already set)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'VerySecureRootPass123!';

-- 2. Remove anonymous users
DELETE FROM mysql.user WHERE User = '';

-- 3. Remove remote root login
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- 4. Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db = 'test' OR Db = 'test\\_%';

-- 5. Flush privileges
FLUSH PRIVILEGES;

Configuration Hardening

# /etc/mysql/mysql.conf.d/security.cnf

[mysqld]
# Bind to specific interface (not 0.0.0.0 if possible)
bind-address = 127.0.0.1

# Require SSL for all connections
require_secure_transport = ON

# Disable symbolic links
symbolic-links = 0

# Disable LOAD DATA LOCAL
local-infile = 0

# SQL mode for strictness
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# Log all queries (for auditing - impacts performance)
# general_log = ON
# general_log_file = /var/log/mysql/general.log

# Password policy
validate_password.policy = STRONG
validate_password.length = 12
default_password_lifetime = 90

Creating Application-Specific Users

-- Read-only reporting user
CREATE USER 'reporting'@'10.0.0.%'
    IDENTIFIED BY 'ReportingSecure123!'
    WITH MAX_USER_CONNECTIONS 5;
GRANT SELECT ON production_db.* TO 'reporting'@'10.0.0.%';

-- Application service user
CREATE USER 'app_service'@'app-server.internal'
    IDENTIFIED BY 'AppServiceSecure123!'
    WITH MAX_CONNECTIONS_PER_HOUR 10000;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_service'@'app-server.internal';
GRANT EXECUTE ON app_db.* TO 'app_service'@'app-server.internal';

-- Backup user
CREATE USER 'backup_user'@'localhost'
    IDENTIFIED BY 'BackupSecure123!';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT,
      EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost';

-- Monitoring user
CREATE USER 'monitoring'@'%'
    IDENTIFIED BY 'MonitorSecure123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'monitoring'@'%';

✅ Security Best Practices Checklist

User Management

• [ ] Use strong, unique passwords for all accounts
• [ ] Implement password expiration policies
• [ ] Remove or lock unused accounts
• [ ] Use specific hosts instead of wildcards
• [ ] Implement resource limits for users

Privilege Management

• [ ] Apply principle of least privilege
• [ ] Use roles for consistent access patterns
• [ ] Review and audit privileges regularly
• [ ] Avoid granting SUPER or ALL privileges
• [ ] Use column-level grants when possible

Monitoring

• [ ] Enable audit logging
• [ ] Monitor failed login attempts
• [ ] Track privilege changes
• [ ] Alert on suspicious activity
• [ ] Regular security assessments

Configuration

• [ ] Require SSL/TLS connections
• [ ] Bind to specific interfaces
• [ ] Disable unnecessary features
• [ ] Keep MySQL updated
• [ ] Use strong SQL modes

🎓 Conclusion

MySQL security requires a layered approach combining proper user management, precise privilege grants, and ongoing monitoring. By implementing the practices in this guide, you can significantly reduce the attack surface of your database and protect sensitive data.

💡 Pro Tip: Regularly run mysql_secure_installation equivalent checks and automate privilege audits to maintain security over time.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Related Articles

• MySQL SSL/TLS Encryption Setup
• MySQL Audit Plugin Setup
• MySQL Data Masking Techniques

Need help with MySQL security? Contact us for expert database consulting.