BLOG POSTS

MangoHost Blog / Create a New MySQL User and Grant Permissions – Secure Setup

Create a New MySQL User and Grant Permissions – Secure Setup

Creating dedicated MySQL users with properly scoped permissions is a cornerstone of database security that every developer and sysadmin should master. Rather than using root for everything or creating overprivileged users, setting up granular user accounts follows the principle of least privilege and significantly reduces attack surface. This guide walks through creating MySQL users from scratch, configuring appropriate permissions for different scenarios, and implementing security best practices that protect your database infrastructure.

Understanding MySQL User Management and Permission System

MySQL’s permission system operates on a hierarchical structure with five privilege levels: Global, Database, Table, Column, and Routine. When you create a user, MySQL stores the account information in system tables within the mysql database, specifically the user, db, tables_priv, columns_priv, and procs_priv tables.

The authentication process follows this flow: MySQL first checks if the user exists and can connect from the specified host, then evaluates permissions starting from the most specific level (column) up to the global level. This layered approach allows for precise control over what users can access and modify.

User accounts in MySQL follow the format ‘username’@’host’, where the host portion can be an IP address, hostname, or wildcard pattern. The host restriction is a security feature that limits where users can connect from, preventing unauthorized access even if credentials are compromised.

Step-by-Step MySQL User Creation Process

First, connect to your MySQL server as root or another user with CREATE USER privileges:

mysql -u root -p

Create a new user with the CREATE USER statement. Here are several common patterns:

-- Create user for localhost access only
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'SecureP@ssw0rd123';

-- Create user for specific IP address
CREATE USER 'webuser'@'192.168.1.100' IDENTIFIED BY 'AnotherSecureP@ss';

-- Create user for any host (less secure, use carefully)
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'StrongPassword456';

-- Create user with SSL requirement
CREATE USER 'ssluser'@'%' IDENTIFIED BY 'SSLPassword789' REQUIRE SSL;

After creating users, grant appropriate permissions. Start with database-level permissions for most applications:

-- Grant all privileges on specific database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'localhost';

-- Grant specific privileges on database
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_db.* TO 'webuser'@'192.168.1.100';

-- Grant read-only access
GRANT SELECT ON analytics_db.* TO 'readonly_user'@'%';

-- Grant table-specific permissions
GRANT SELECT, UPDATE ON myapp_db.users TO 'limited_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Verify the user creation and permissions:

-- List all users
SELECT User, Host FROM mysql.user;

-- Show grants for specific user
SHOW GRANTS FOR 'appuser'@'localhost';

-- Test connection (from another terminal)
mysql -u appuser -p -h localhost

Real-World Permission Scenarios and Use Cases

Different applications require different permission sets. Here are practical examples for common scenarios:

**Web Application Database User:**

CREATE USER 'webapp'@'%' IDENTIFIED BY 'WebAppSecurePass123';
GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_db.* TO 'webapp'@'%';
GRANT CREATE TEMPORARY TABLES ON webapp_db.* TO 'webapp'@'%';
FLUSH PRIVILEGES;

**Analytics and Reporting User:**

CREATE USER 'analyst'@'%' IDENTIFIED BY 'AnalyticsPass456';
GRANT SELECT ON webapp_db.* TO 'analyst'@'%';
GRANT SELECT ON logs_db.* TO 'analyst'@'%';
-- Allow creation of views for complex queries
GRANT CREATE VIEW ON analytics_db.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

**Backup User with Minimal Privileges:**

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupSecurePass789';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
-- For MySQL 5.7.6+ add RELOAD for consistent backups
GRANT RELOAD ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

**Development Environment User:**

CREATE USER 'developer'@'%' IDENTIFIED BY 'DevPassword123';
GRANT ALL PRIVILEGES ON dev_*.* TO 'developer'@'%';
GRANT CREATE, DROP ON test_*.* TO 'developer'@'%';
FLUSH PRIVILEGES;

Security Best Practices and Advanced Configuration

Implement these security measures for production environments:

**Password Validation and Policies:**

-- Enable password validation plugin (MySQL 5.7+)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Set password policy
SET GLOBAL validate_password_policy = 'MEDIUM';
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;

**Account Security Features:**

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

-- Lock account after failed login attempts (MySQL 5.7.6+)
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'SecurePass123' 
FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

-- Require SSL connections
ALTER USER 'existing_user'@'%' REQUIRE SSL;

**Network Security Configuration:**

-- Bind MySQL to specific interface (my.cnf)
[mysqld]
bind-address = 127.0.0.1
# or for specific network interface
bind-address = 192.168.1.50

-- Enable SSL (my.cnf)
[mysqld]
ssl-ca = /path/to/ca.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem

Common Issues and Troubleshooting Guide

**Connection Denied Errors:**
The most frequent issue is host-based access denial. Check these areas:

-- Verify user exists with correct host
SELECT User, Host FROM mysql.user WHERE User = 'your_username';

-- Check if user can connect from your location
-- If connecting from 192.168.1.50 but user is created for 'localhost', it will fail
CREATE USER 'username'@'192.168.1.50' IDENTIFIED BY 'password';
-- or use wildcard (less secure)
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

**Permission Denied for Operations:**

-- Check current user privileges
SHOW GRANTS FOR CURRENT_USER();

-- Grant missing permissions
GRANT INSERT, UPDATE ON database.table TO 'username'@'host';
FLUSH PRIVILEGES;

**Password Authentication Issues:**

-- For MySQL 8.0+ compatibility with older clients
ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

-- Reset forgotten password
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

Performance Considerations and User Management

User management operations can impact performance, especially on busy systems. Here’s a comparison of different approaches:

Operation	Performance Impact	Best Practice	Notes
CREATE USER	Low	Batch operations during maintenance	Minimal impact on active connections
GRANT with wildcards	Medium	Use specific database/table names	Wildcard grants require more privilege checking
FLUSH PRIVILEGES	High	Avoid during peak hours	Reloads grant tables, brief lock
SHOW GRANTS queries	Low-Medium	Cache results when possible	Impact increases with user count

**Monitoring User Activity:**

-- Enable general query log for auditing
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Monitor current connections
SHOW PROCESSLIST;

-- Check user connection history (requires MySQL 5.7+)
SELECT * FROM performance_schema.accounts;

Comparison with Alternative Database Systems

Feature	MySQL	PostgreSQL	MariaDB
User Creation Syntax	CREATE USER ‘user’@’host’	CREATE USER username	Same as MySQL
Host-based Access	Built-in with @host	pg_hba.conf file	Built-in with @host
Role-based Access	Limited (MySQL 8.0+)	Full role support	Role support available
SSL Requirements	REQUIRE SSL clause	hostssl in pg_hba.conf	REQUIRE SSL clause

Advanced User Management Features

**MySQL 8.0 Role-Based Access Control:**

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

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

-- Create user and assign roles
CREATE USER 'john'@'%' IDENTIFIED BY 'SecurePass123';
GRANT 'app_read', 'app_write' TO 'john'@'%';

-- Set default roles
ALTER USER 'john'@'%' DEFAULT ROLE 'app_read', 'app_write';
FLUSH PRIVILEGES;

**Resource Limits and Quotas:**

-- Limit user resources
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'Password123'
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 100
     MAX_CONNECTIONS_PER_HOUR 10
     MAX_USER_CONNECTIONS 2;

Integration with Infrastructure and Deployment

When deploying on VPS or dedicated servers, consider these automation approaches:

**Docker Environment Variables:**

# docker-compose.yml
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: webapp_db
      MYSQL_USER: webapp_user
      MYSQL_PASSWORD: webapp_pass
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

**Terraform MySQL Provider:**

resource "mysql_user" "webapp" {
  user               = "webapp_user"
  host               = "%"
  plaintext_password = var.webapp_password
}

resource "mysql_grant" "webapp" {
  user       = mysql_user.webapp.user
  host       = mysql_user.webapp.host
  database   = "webapp_db"
  privileges = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

For comprehensive MySQL documentation and advanced configuration options, refer to the official MySQL User Account Management guide and the MySQL Access Control and Privilege System documentation.

The foundation of secure database management starts with proper user creation and permission assignment. By implementing these practices systematically, you create robust barriers against unauthorized access while maintaining the flexibility needed for legitimate application operations. Regular auditing of user permissions and staying current with security updates ensures your MySQL infrastructure remains protected against evolving threats.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.