Ultimate Guide to Create User MySQL with Access Control Setup - Techvblogs
Ubuntu Linux MySQL

Ultimate Guide to Create User MySQL with Access Control Setup

Master MySQL user creation, permission setup, and access management fast.

Suresh Ramani - Author - Techvblogs
Suresh Ramani
 

1 week ago

TechvBlogs - Google News
SSD VPS Servers, Cloud Servers and Cloud Hosting - Vultr.com

Knowing how to create user MySQL with proper access control is essential for database security and management. This comprehensive guide covers everything you need to know about MySQL user creation, privilege management, and security best practices that will help you maintain a secure and well-organized database environment.

Whether you’re setting up a new application, managing a production database, or learning MySQL administration, understanding how to create user MySQL accounts with appropriate permissions is crucial for protecting your data while ensuring legitimate access. You’ll discover step-by-step instructions, security considerations, and troubleshooting tips that make MySQL user management straightforward and secure.

MySQL User Management

Understanding MySQL user management fundamentals provides the foundation for secure database administration and access control.

Overview of MySQL User System

MySQL’s user system operates on a host-user combination principle, where each user account is uniquely identified by both username and host. When you create user MySQL accounts, you’re essentially defining who can access your database and from where they can connect.

Key Components of MySQL User Management:

  • User Account: Combination of username and host (user@host)
  • Authentication: Password verification and authentication plugins
  • Authorization: Privileges that determine what actions users can perform
  • Host Restrictions: Controls from which locations users can connect
  • Security Context: SSL requirements and connection encryption

MySQL User Storage:

MySQL stores user account information in the mysql.user table, which contains:

  • User credentials and authentication data
  • Global privileges and access levels
  • Host connection restrictions
  • Password validation requirements
  • Account status and expiration settings

Understanding this structure helps you make informed decisions when you create user MySQL accounts for different purposes and security requirements.

Create User MySQL

Learning to create user MySQL accounts properly ensures secure database access while maintaining proper authorization controls.

Basic User Creation Command

The fundamental command to create user MySQL accounts uses the CREATE USER statement with essential parameters for username, host, and authentication.

Basic Syntax:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Simple User Creation Examples:

-- Create a basic user for localhost access
CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Create user with specific host IP
CREATE USER 'appuser'@'192.168.1.100' IDENTIFIED BY 'StrongPassword456!';

-- Create user accessible from any host (use with caution)
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'ComplexPass789!';

-- Verify user creation
SELECT User, Host FROM mysql.user WHERE User = 'webuser';

Important Considerations:

  • Always use strong passwords when you create user MySQL accounts
  • Choose appropriate host restrictions based on security requirements
  • Avoid using ‘%’ wildcard for host unless absolutely necessary
  • Test user creation immediately after execution

Create User with Host Restriction

Host restrictions provide an additional security layer when you create user MySQL accounts by limiting connection sources.

Host Restriction Examples:

-- Create user for specific IP address
CREATE USER 'dbadmin'@'192.168.1.50' IDENTIFIED BY 'AdminPass123!';

-- Create user for IP range using wildcards
CREATE USER 'developer'@'192.168.1.%' IDENTIFIED BY 'DevPass456!';

-- Create user for specific subnet
CREATE USER 'analyst'@'10.0.0.%' IDENTIFIED BY 'AnalysisPass789!';

-- Create user for domain-based access
CREATE USER 'support'@'company.com' IDENTIFIED BY 'SupportPass101!';

-- Multiple users with different host restrictions
CREATE USER 'backup_local'@'localhost' IDENTIFIED BY 'BackupLocal123!';
CREATE USER 'backup_remote'@'backup.company.com' IDENTIFIED BY 'BackupRemote456!';

Host Pattern Examples:

  • localhost or 127.0.0.1: Local connections only
  • 192.168.1.100: Specific IP address
  • 192.168.1.%: Any IP in 192.168.1.x subnet
  • %.company.com: Any subdomain of company.com
  • %: Any host (highest security risk)

Create User with Password Authentication

Modern MySQL versions support multiple authentication plugins when you create user MySQL accounts, enhancing security through advanced password handling.

Authentication Plugin Options:

-- Create user with default authentication (recommended)
CREATE USER 'secureuser'@'localhost' 
IDENTIFIED BY 'StrongPassword123!' 
REQUIRE SSL;

-- Create user with specific authentication plugin
CREATE USER 'sha2user'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'CachingPass456!';

-- Create user with native password plugin (for compatibility)
CREATE USER 'legacyuser'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'LegacyPass789!';

-- Create user with password expiration
CREATE USER 'tempuser'@'localhost' 
IDENTIFIED BY 'TempPass101!' 
PASSWORD EXPIRE INTERVAL 90 DAY;

-- Create user with account locking capability
CREATE USER 'serviceuser'@'localhost' 
IDENTIFIED BY 'ServicePass202!' 
ACCOUNT UNLOCK;

Advanced Authentication Features:

-- Create user with multiple authentication requirements
CREATE USER 'highsecuser'@'10.0.0.%' 
IDENTIFIED BY 'HighSecPass303!' 
REQUIRE SSL 
REQUIRE X509 
PASSWORD EXPIRE INTERVAL 60 DAY 
PASSWORD HISTORY 5 
PASSWORD REUSE INTERVAL 30 DAY;

-- Create user with failed login tracking
CREATE USER 'monitored'@'localhost' 
IDENTIFIED BY 'MonitoredPass404!' 
FAILED_LOGIN_ATTEMPTS 3 
PASSWORD_LOCK_TIME 2;

Common Errors While Creating Users

Understanding common errors helps troubleshoot issues when you create user MySQL accounts.

Error 1: User Already Exists

-- Error: CREATE USER failed; user already exists
-- Solution: Check existing users first
SELECT User, Host FROM mysql.user WHERE User = 'username';

-- If user exists, either drop or use different host
DROP USER 'existinguser'@'localhost';
CREATE USER 'existinguser'@'localhost' IDENTIFIED BY 'NewPassword123!';

Error 2: Invalid Host Format

-- Incorrect: Missing quotes around host
CREATE USER username@localhost IDENTIFIED BY 'password';

-- Correct: Proper quoting
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Error 3: Weak Password Rejected

-- Check password validation settings
SHOW VARIABLES LIKE 'validate_password%';

-- Create user with compliant password
CREATE USER 'stronguser'@'localhost' 
IDENTIFIED BY 'Complex!Pass123@' 
PASSWORD REQUIRE CURRENT;

Error 4: Insufficient Privileges

-- Ensure you have CREATE USER privilege
SHOW GRANTS FOR CURRENT_USER();

-- Grant CREATE USER privilege if needed
GRANT CREATE USER ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

Best Practices for User Creation

Following best practices ensures security and maintainability when you create user MySQL accounts.

Security Best Practices:

-- 1. Use strong, unique passwords
CREATE USER 'webapp'@'localhost' 
IDENTIFIED BY 'WebApp$ecure123!@#' 
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 2. Restrict host access appropriately
CREATE USER 'api_service'@'192.168.1.%' 
IDENTIFIED BY 'API$ervice456!@#';

-- 3. Implement SSL requirements
CREATE USER 'sensitive_app'@'%' 
IDENTIFIED BY 'Sensitive@pp789!@#' 
REQUIRE SSL;

-- 4. Set account expiration for temporary users
CREATE USER 'consultant'@'%.consulting.com' 
IDENTIFIED BY 'Consultant123!@#' 
PASSWORD EXPIRE INTERVAL 30 DAY 
ACCOUNT LOCK;

Naming Conventions:

  • Use descriptive usernames that indicate purpose
  • Include environment indicators (dev, staging, prod)
  • Follow consistent naming patterns across your organization
  • Document user purposes and responsibilities

Grant Privileges in MySQL

Proper privilege management ensures users have appropriate access levels when you create user MySQL accounts for different roles.

GRANT ALL vs Specific Privileges

Understanding the difference between granting all privileges versus specific privileges helps maintain security when managing MySQL users.

Grant All Privileges (Use with Caution):

-- Grant all privileges on all databases (superuser access)
GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'localhost' WITH GRANT OPTION;

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

-- Grant all privileges on specific table
GRANT ALL PRIVILEGES ON webapp_db.users TO 'user_admin'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

Specific Privilege Grants (Recommended):

-- Application database user with limited privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_db.* TO 'webapp'@'localhost';

-- Read-only user for reporting
GRANT SELECT ON analytics_db.* TO 'reporter'@'%';

-- Backup user with specific privileges
GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup_user'@'localhost';

-- Developer user with schema modification rights
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON dev_db.* 
TO 'developer'@'192.168.1.%';

Common Privilege Types:

  • SELECT: Read data from tables
  • INSERT: Add new records
  • UPDATE: Modify existing records
  • DELETE: Remove records
  • CREATE: Create databases and tables
  • DROP: Delete databases and tables
  • ALTER: Modify table structure
  • INDEX: Create and drop indexes

Grant Privileges on Specific Database

Targeting privileges to specific databases enhances security by limiting access scope.

Database-Specific Privilege Examples:

-- E-commerce application privileges
GRANT SELECT, INSERT, UPDATE ON ecommerce.products TO 'catalog_manager'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.orders TO 'order_processor'@'localhost';
GRANT SELECT ON ecommerce.* TO 'customer_service'@'192.168.1.%';

-- Content management system
GRANT SELECT, INSERT, UPDATE, DELETE ON cms.articles TO 'content_editor'@'%';
GRANT SELECT, INSERT, UPDATE ON cms.users TO 'user_manager'@'localhost';
GRANT SELECT ON cms.analytics TO 'analytics_viewer'@'%.company.com';

-- Financial application with strict controls
GRANT SELECT ON finance.transactions TO 'auditor'@'audit.company.com';
GRANT INSERT ON finance.transactions TO 'payment_processor'@'secure.payment.com';
GRANT SELECT, UPDATE ON finance.accounts TO 'account_manager'@'localhost';

Column-Level Privileges:

-- Grant access to specific columns only
GRANT SELECT (id, name, email) ON users.customers TO 'customer_lookup'@'localhost';
GRANT UPDATE (status, last_updated) ON orders.order_items TO 'fulfillment'@'localhost';

-- Sensitive data protection
GRANT SELECT (id, name, phone) ON users.employees TO 'hr_basic'@'localhost';
GRANT SELECT (id, name, phone, salary, ssn) ON users.employees TO 'hr_admin'@'localhost';

Revoke and Modify Privileges

Managing existing privileges allows you to adjust access levels as requirements change.

Revoke Privileges:

-- Revoke specific privileges
REVOKE INSERT, UPDATE ON webapp_db.* FROM 'webapp'@'localhost';

-- Revoke all privileges on specific database
REVOKE ALL PRIVILEGES ON test_db.* FROM 'developer'@'localhost';

-- Revoke grant option
REVOKE GRANT OPTION ON *.* FROM 'admin_user'@'localhost';

-- Revoke all privileges and grant option
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'former_admin'@'localhost';

Modify Existing Privileges:

-- Add privileges to existing user
GRANT CREATE, DROP ON development.* TO 'developer'@'localhost';

-- Change privilege scope
REVOKE SELECT ON *.* FROM 'reporter'@'localhost';
GRANT SELECT ON reporting_db.* TO 'reporter'@'localhost';

-- Temporary privilege elevation
GRANT ALTER ON production_db.* TO 'maintenance'@'localhost';
-- After maintenance:
REVOKE ALTER ON production_db.* FROM 'maintenance'@'localhost';

View Current User Privileges

Monitoring user privileges helps maintain security and troubleshoot access issues.

Check User Privileges:

-- View privileges for specific user
SHOW GRANTS FOR 'webapp'@'localhost';

-- View current user's privileges
SHOW GRANTS FOR CURRENT_USER();

-- View all grants for user pattern
SELECT DISTINCT User, Host FROM mysql.user WHERE User LIKE 'webapp%';

-- Detailed privilege information
SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv 
FROM mysql.user WHERE User = 'webapp';

-- Database-specific privileges
SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv 
FROM mysql.db WHERE User = 'webapp';

Privilege Auditing Queries:

-- Users with dangerous privileges
SELECT User, Host FROM mysql.user 
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';

-- Users with access to all databases
SELECT User, Host FROM mysql.user 
WHERE Select_priv = 'Y' AND Host = '%';

-- Users without passwords (security risk)
SELECT User, Host FROM mysql.user 
WHERE authentication_string = '' OR authentication_string IS NULL;

MySQL User Authentication

Advanced authentication features enhance security when you create user MySQL accounts for production environments.

Native vs Caching_sha2 Password

Understanding authentication plugins helps you choose appropriate security levels for different user types.

Authentication Plugin Comparison:

-- Check current default authentication plugin
SHOW VARIABLES LIKE 'default_authentication_plugin';

-- Create user with caching_sha2_password (MySQL 8.0+ default)
CREATE USER 'modern_user'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'ModernPass123!';

-- Create user with mysql_native_password (legacy compatibility)
CREATE USER 'legacy_app'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'LegacyPass456!';

-- Convert existing user to different authentication
ALTER USER 'existing_user'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'NewSecurePass789!';

Authentication Plugin Features:

  • caching_sha2_password: Default in MySQL 8.0+, stronger security, caching for performance
  • mysql_native_password: Compatible with older clients, widely supported
  • sha256_password: Strong security without caching, slower performance

Change Password for Existing User

Password management is crucial for maintaining security over time.

Password Change Methods:

-- Change password using ALTER USER (recommended)
ALTER USER 'webapp'@'localhost' IDENTIFIED BY 'NewStrongPass123!';

-- Change password with specific authentication plugin
ALTER USER 'service_user'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'ServiceNewPass456!';

-- Change password with expiration
ALTER USER 'temp_user'@'localhost' 
IDENTIFIED BY 'TempNewPass789!' 
PASSWORD EXPIRE INTERVAL 30 DAY;

-- Change current user's password
ALTER USER CURRENT_USER() IDENTIFIED BY 'MyNewPassword101!';

-- Set password expiration without changing password
ALTER USER 'user'@'localhost' PASSWORD EXPIRE;

Bulk Password Updates:

-- Update multiple users with similar patterns
-- Note: Execute each command separately
ALTER USER 'app_user1'@'localhost' IDENTIFIED BY 'AppUser1NewPass!';
ALTER USER 'app_user2'@'localhost' IDENTIFIED BY 'AppUser2NewPass!';
ALTER USER 'app_user3'@'localhost' IDENTIFIED BY 'AppUser3NewPass!';

-- Expire passwords for all development users
-- Use with caution in production
UPDATE mysql.user SET password_expired = 'Y' 
WHERE User LIKE 'dev_%' AND Host = 'localhost';
FLUSH PRIVILEGES;

Enforce Strong Password Policies

Password validation ensures users create secure passwords when you create user MySQL accounts.

Configure Password Validation:

-- Check current password validation settings
SHOW VARIABLES LIKE 'validate_password%';

-- Install password validation plugin (if not installed)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Configure password validation policies
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 2;
SET GLOBAL validate_password.special_char_count = 1;

Create Users with Strong Password Requirements:

-- User with password history tracking
CREATE USER 'secure_app'@'localhost' 
IDENTIFIED BY 'SecureApp123!@#' 
PASSWORD HISTORY 5 
PASSWORD REUSE INTERVAL 365 DAY;

-- User with password expiration and complexity
CREATE USER 'financial_app'@'192.168.1.%' 
IDENTIFIED BY 'Financial$ecure456!@#' 
PASSWORD EXPIRE INTERVAL 60 DAY 
FAILED_LOGIN_ATTEMPTS 3 
PASSWORD_LOCK_TIME 2;

Enable Two-Factor Authentication (2FA)

Multi-factor authentication adds extra security layers for sensitive accounts.

Configure 2FA with Authentication Plugins:

-- Create user with multi-factor authentication requirement
CREATE USER 'high_security'@'localhost' 
IDENTIFIED WITH authentication_ldap_sasl 
AS 'uid=highsec,ou=people,dc=company,dc=com'
AND IDENTIFIED WITH caching_sha2_password BY 'HighSec123!@#';

-- User with certificate-based authentication
CREATE USER 'cert_user'@'localhost' 
IDENTIFIED WITH authentication_ldap_sasl
REQUIRE X509;

-- User requiring SSL certificate with specific subject
CREATE USER 'ssl_required'@'%' 
IDENTIFIED BY 'SSLRequired456!@#' 
REQUIRE SUBJECT '/C=US/ST=CA/L=SF/O=Company/CN=ssl_user';

MySQL Host-Based Access Control

Host-based access control provides network-level security when you create user MySQL accounts.

Create User for Localhost

Localhost users provide secure access for applications running on the same server as MySQL.

Localhost User Examples:

-- Standard localhost user
CREATE USER 'local_app'@'localhost' IDENTIFIED BY 'LocalApp123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'local_app'@'localhost';

-- Localhost user with socket authentication (Unix systems)
CREATE USER 'socket_user'@'localhost' IDENTIFIED WITH auth_socket;

-- Multiple localhost users for different services
CREATE USER 'web_service'@'localhost' IDENTIFIED BY 'WebService456!';
CREATE USER 'background_jobs'@'localhost' IDENTIFIED BY 'BackgroundJobs789!';
CREATE USER 'monitoring'@'localhost' IDENTIFIED BY 'Monitoring101!';

-- Localhost admin with elevated privileges
CREATE USER 'local_admin'@'localhost' IDENTIFIED BY 'LocalAdmin202!';
GRANT ALL PRIVILEGES ON *.* TO 'local_admin'@'localhost' WITH GRANT OPTION;

Localhost vs 127.0.0.1:

-- These are different in MySQL
CREATE USER 'app1'@'localhost' IDENTIFIED BY 'App1Pass!';      -- Unix socket
CREATE USER 'app2'@'127.0.0.1' IDENTIFIED BY 'App2Pass!';     -- TCP/IP connection

-- Grant privileges to both for compatibility
GRANT SELECT ON app_db.* TO 'app1'@'localhost';
GRANT SELECT ON app_db.* TO 'app1'@'127.0.0.1';

Allow Remote User Access

Remote access enables distributed applications and external tools to connect to MySQL.

Remote Access Configuration:

-- User for specific remote IP
CREATE USER 'remote_app'@'203.0.113.100' 
IDENTIFIED BY 'RemoteApp123!' 
REQUIRE SSL;

-- User for IP range
CREATE USER 'office_users'@'192.168.10.%' 
IDENTIFIED BY 'OfficeUsers456!';

-- User for specific domain
CREATE USER 'partner_access'@'%.partner.com' 
IDENTIFIED BY 'PartnerAccess789!' 
REQUIRE SSL;

-- Secure remote admin (use very carefully)
CREATE USER 'remote_admin'@'admin.company.com' 
IDENTIFIED BY 'RemoteAdmin101!' 
REQUIRE SSL 
REQUIRE X509;

Network Security Considerations:

-- Always require SSL for remote connections
GRANT SELECT, INSERT, UPDATE ON production.* TO 'remote_app'@'%' 
REQUIRE SSL;

-- Limit connection resources for remote users
CREATE USER 'limited_remote'@'%.external.com' 
IDENTIFIED BY 'LimitedRemote123!' 
WITH MAX_CONNECTIONS_PER_HOUR 100 
MAX_QUERIES_PER_HOUR 1000;

IP Whitelisting for MySQL Users

IP whitelisting provides precise control over connection sources.

IP Whitelist Examples:

-- Specific IP addresses
CREATE USER 'server1'@'192.168.1.100' IDENTIFIED BY 'Server1Pass!';
CREATE USER 'server2'@'192.168.1.101' IDENTIFIED BY 'Server2Pass!';
CREATE USER 'server3'@'192.168.1.102' IDENTIFIED BY 'Server3Pass!';

-- Subnet-based access
CREATE USER 'internal_apps'@'10.0.0.%' IDENTIFIED BY 'InternalApps123!';
CREATE USER 'dmz_services'@'172.16.0.%' IDENTIFIED BY 'DMZServices456!';

-- Multiple subnet access for the same user
GRANT SELECT ON reporting.* TO 'analyst'@'192.168.1.%';
GRANT SELECT ON reporting.* TO 'analyst'@'192.168.2.%';
GRANT SELECT ON reporting.* TO 'analyst'@'10.0.1.%';

Dynamic IP Management:

-- Create procedure for IP whitelist management
DELIMITER $$
CREATE PROCEDURE AddIPToWhitelist(
    IN username VARCHAR(80),
    IN ip_address VARCHAR(60),
    IN user_password VARCHAR(255)
)
BEGIN
    SET @sql = CONCAT('CREATE USER ''', username, '''@''', ip_address, ''' IDENTIFIED BY ''', user_password, '''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Secure Remote Connections with SSL

SSL encryption protects data transmission for remote MySQL connections.

SSL Configuration for Users:

-- Require SSL for all connections
CREATE USER 'ssl_only'@'%' 
IDENTIFIED BY 'SSLOnly123!' 
REQUIRE SSL;

-- Require specific SSL cipher
CREATE USER 'strong_ssl'@'%' 
IDENTIFIED BY 'StrongSSL456!' 
REQUIRE CIPHER 'AES256-SHA';

-- Require client certificate
CREATE USER 'cert_required'@'%' 
IDENTIFIED BY 'CertRequired789!' 
REQUIRE X509;

-- Require specific certificate subject
CREATE USER 'specific_cert'@'%' 
IDENTIFIED BY 'SpecificCert101!' 
REQUIRE SUBJECT '/C=US/ST=CA/L=SF/O=Company/CN=specific_user';

-- Require certificate issuer
CREATE USER 'trusted_issuer'@'%' 
IDENTIFIED BY 'TrustedIssuer202!' 
REQUIRE ISSUER '/C=US/O=Company CA/CN=Company Certificate Authority';

SSL Status Verification:

-- Check SSL status for current connection
SHOW STATUS LIKE 'Ssl_cipher';

-- View SSL variables
SHOW VARIABLES LIKE '%ssl%';

-- Check user SSL requirements
SELECT User, Host, ssl_type, ssl_cipher, x509_issuer, x509_subject 
FROM mysql.user 
WHERE ssl_type != '';

MySQL User Maintenance

Regular maintenance ensures optimal performance and security for MySQL user accounts.

List All MySQL Users

Monitoring user accounts helps maintain database security and organization.

User Listing Queries:

-- Basic user list
SELECT User, Host FROM mysql.user ORDER BY User, Host;

-- Detailed user information
SELECT User, Host, authentication_string, 
       plugin, password_expired, account_locked
FROM mysql.user 
ORDER BY User, Host;

-- Users with specific privileges
SELECT DISTINCT User, Host 
FROM mysql.user 
WHERE Super_priv = 'Y' OR Grant_priv = 'Y'
ORDER BY User;

-- Users by authentication plugin
SELECT plugin, COUNT(*) as user_count 
FROM mysql.user 
GROUP BY plugin;

-- Active vs locked accounts
SELECT 
    account_locked,
    COUNT(*) as account_count
FROM mysql.user 
GROUP BY account_locked;

User Access Summary:

-- Create comprehensive user audit view
SELECT 
    u.User,
    u.Host,
    u.plugin as auth_plugin,
    u.password_expired,
    u.account_locked,
    GROUP_CONCAT(DISTINCT d.Db) as accessible_databases
FROM mysql.user u
LEFT JOIN mysql.db d ON u.User = d.User AND u.Host = d.Host
GROUP BY u.User, u.Host
ORDER BY u.User, u.Host;

Delete or Drop MySQL User

Removing unnecessary user accounts improves security and reduces maintenance overhead.

Safe User Deletion Process:

-- Step 1: Check user's current privileges
SHOW GRANTS FOR 'username'@'host';

-- Step 2: Check user's active connections
SELECT User, Host, Command, State 
FROM information_schema.PROCESSLIST 
WHERE User = 'username';

-- Step 3: Revoke all privileges (optional, but safer)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

-- Step 4: Drop the user
DROP USER 'username'@'host';

-- Step 5: Verify deletion
SELECT User, Host FROM mysql.user WHERE User = 'username';

Bulk User Deletion:

-- Delete multiple users (execute separately)
DROP USER 'temp_user1'@'localhost';
DROP USER 'temp_user2'@'localhost';
DROP USER 'temp_user3'@'localhost';

-- Delete users matching pattern (be very careful)
-- First, list users to confirm
SELECT User, Host FROM mysql.user WHERE User LIKE 'test_%';

-- Then delete (example for test users only)
DROP USER 'test_user1'@'localhost';
DROP USER 'test_user2'@'localhost';

Rename MySQL User

Renaming users helps maintain consistent naming conventions without losing privileges.

User Rename Process:

-- Method 1: Using RENAME USER (recommended)
RENAME USER 'old_username'@'host' TO 'new_username'@'host';

-- Method 2: Multiple host rename
RENAME USER 
    'old_name'@'localhost' TO 'new_name'@'localhost',
    'old_name'@'%' TO 'new_name'@'%';

-- Verify rename
SELECT User, Host FROM mysql.user WHERE User IN ('old_username', 'new_username');

Complex Rename Scenarios:

-- Rename and change host
RENAME USER 'developer'@'localhost' TO 'senior_dev'@'192.168.1.%';

-- Rename multiple related users
RENAME USER 
    'app_dev'@'localhost' TO 'app_developer'@'localhost',
    'app_test'@'localhost' TO 'app_tester'@'localhost',
    'app_stage'@'localhost' TO 'app_staging'@'localhost';

Troubleshoot User Access Issues

Systematic troubleshooting helps resolve common user access problems.

Common Access Issues and Solutions:

-- Issue 1: User cannot connect
-- Check if user exists
SELECT User, Host FROM mysql.user WHERE User = 'problematic_user';

-- Check authentication plugin compatibility
SELECT User, Host, plugin FROM mysql.user WHERE User = 'problematic_user';

-- Solution: Update authentication plugin if needed
ALTER USER 'problematic_user'@'host' 
IDENTIFIED WITH mysql_native_password BY 'password';

-- Issue 2: Access denied for specific database
-- Check database privileges
SHOW GRANTS FOR 'user'@'host';

-- Check database-specific permissions
SELECT * FROM mysql.db WHERE User = 'user' AND Db = 'database_name';

-- Solution: Grant appropriate privileges
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'user'@'host';
FLUSH PRIVILEGES;

-- Issue 3: Connection from wrong host
-- Check host restrictions
SELECT User, Host FROM mysql.user WHERE User = 'user';

-- Solution: Add appropriate host or modify existing
CREATE USER 'user'@'new_host' IDENTIFIED BY 'password';
-- Copy privileges from existing user
-- Then optionally remove old host user

Diagnostic Queries:

-- Check connection attempts
SELECT User, Host, plugin FROM mysql.user;

-- View current connections
SELECT User, Host, Command, Time, State, Info 
FROM information_schema.PROCESSLIST;

-- Check user account status
SELECT User, Host, account_locked, password_expired 
FROM mysql.user 
WHERE User = 'specific_user';

-- Verify SSL requirements
SELECT User, Host, ssl_type, ssl_cipher 
FROM mysql.user 
WHERE ssl_type != '';

Key Takeaways

Successfully managing MySQL users requires understanding the relationship between user creation, privilege management, and security considerations. When you create user MySQL accounts, always follow security best practices and implement appropriate access controls.

Essential principles for MySQL user management:

  • Always use strong passwords and appropriate authentication plugins
  • Implement host-based restrictions to limit connection sources
  • Grant only necessary privileges following the principle of least privilege
  • Regular audit user accounts and privileges for security compliance
  • Use SSL encryption for remote connections to protect data transmission

Best practices for production environments:

  • Document all user accounts and their purposes
  • Implement password policies and regular password rotation
  • Monitor user access patterns and connection attempts
  • Maintain separate users for different applications and environments
  • Regular backup of user privilege configurations
  • Test user access after any changes to ensure functionality

By following this comprehensive guide, you’ll be able to create user MySQL accounts that provide secure, controlled access to your databases while maintaining the flexibility needed for various applications and use cases.

MySQL Ubuntu Linux
Previous

How to MySQL List Users with Permissions from Command Line Easily
Next

Send WhatsApp Message Using Laravel 12 - Complete Integration Guide

Comments (0)

SSD VPS Servers, Cloud Servers and Cloud Hosting - Vultr.com

Comment

Note: All Input Fields are required.