Skip to main content

MySQL User Management

Introduction​

User management is a fundamental aspect of MySQL administration. It involves creating and maintaining user accounts, assigning appropriate privileges, and ensuring database security. Effective user management allows database administrators to control who can access the database and what operations they can perform. This is essential for maintaining the security and integrity of your database systems.

In this guide, we'll explore MySQL's user management system, including:

  • Creating and deleting user accounts
  • Setting and changing passwords
  • Managing user privileges
  • Authentication plugins
  • Best practices for user security

User Account Basics​

MySQL User Account Structure​

In MySQL, user accounts consist of two parts:

  1. A username
  2. A host from which the user can connect

This is often represented as 'username'@'host'. For example:

  • 'john'@'localhost' - User john accessing from the same machine
  • 'mary'@'192.168.1.%' - User mary accessing from any IP in the 192.168.1.x subnet
  • 'david'@'%' - User david accessing from anywhere
note

The same username connecting from different hosts is treated as different users in MySQL.

Creating User Accounts​

To create a new MySQL user, you can use the CREATE USER statement:

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

Examples​

Creating a user that can connect only from localhost:

sql
CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'password123';

Creating a user that can connect from any host:

sql
CREATE USER 'admin'@'%' IDENTIFIED BY 'secure_password';

Creating Users with Different Authentication Methods​

MySQL supports various authentication plugins:

sql
-- Using MySQL's default authentication
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';

-- Using MySQL native password authentication
CREATE USER 'user2'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

-- Using SHA-256 authentication
CREATE USER 'user3'@'localhost' IDENTIFIED WITH sha256_password BY 'password';

-- Using caching SHA-2 authentication (more secure, available in MySQL 8.0+)
CREATE USER 'user4'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

Modifying User Accounts​

Changing Passwords​

For a specific user:

sql
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

To change your own password:

sql
ALTER USER USER() IDENTIFIED BY 'new_password';

Renaming a User​

sql
RENAME USER 'old_username'@'host' TO 'new_username'@'host';

Example: Renaming and Changing Authentication​

sql
-- Rename user and change authentication method
RENAME USER 'developer'@'localhost' TO 'senior_dev'@'localhost';
ALTER USER 'senior_dev'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_secure_password';

Managing User Privileges​

MySQL uses a privilege-based system to control what users can do. Privileges can be granted at different levels:

  • Global privileges (apply to all databases)
  • Database privileges (apply to all objects in a specific database)
  • Table privileges (apply to all columns in a specific table)
  • Column privileges (apply to specific columns in a table)
  • Stored routine privileges (apply to stored procedures and functions)

Common Privileges​

PrivilegeDescription
ALLGrants all privileges
SELECTAllows reading data
INSERTAllows adding new rows
UPDATEAllows modifying existing rows
DELETEAllows removing rows
CREATEAllows creating tables or databases
DROPAllows removing tables or databases
GRANT OPTIONAllows granting privileges to others

Granting Privileges​

General syntax:

sql
GRANT privilege1, privilege2, ... ON database_name.table_name TO 'username'@'host';

Examples​

Granting global privileges:

sql
-- Grant all privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

Granting database-level privileges:

sql
-- Grant full access to a specific database
GRANT ALL PRIVILEGES ON myapp.* TO 'app_user'@'%';

-- Grant read-only access to a database
GRANT SELECT ON analytics.* TO 'analyst'@'%';

Granting table-level privileges:

sql
-- Grant read and update on a specific table
GRANT SELECT, UPDATE ON customers.users TO 'support'@'localhost';

Revoking Privileges​

To remove privileges, use the REVOKE statement:

sql
REVOKE privilege1, privilege2, ... ON database_name.table_name FROM 'username'@'host';

Example:

sql
-- Revoke DELETE privilege on customers table
REVOKE DELETE ON shop.customers FROM 'support'@'localhost';

-- Revoke all privileges from a user on a database
REVOKE ALL PRIVILEGES ON analytics.* FROM 'former_analyst'@'%';

Viewing User Information and Privileges​

Listing All Users​

sql
SELECT user, host FROM mysql.user;

Example output:

+---------------+-----------+
| user          | host      |
+---------------+-----------+
| admin         | %         |
| developer     | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

Checking User Privileges​

sql
SHOW GRANTS FOR 'username'@'host';

Example:

sql
SHOW GRANTS FOR 'app_user'@'%';

Output:

+--------------------------------------------------------------+
| Grants for app_user@%                                       |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%`                        |
| GRANT SELECT, INSERT, UPDATE ON `myapp`.* TO `app_user`@`%` |
+--------------------------------------------------------------+

Checking Current User​

sql
SELECT CURRENT_USER();

Removing User Accounts​

To delete a user account:

sql
DROP USER 'username'@'host';

For example:

sql
DROP USER 'former_employee'@'%';
warning

When you drop a user, all privileges granted to that user are automatically revoked, but any routines or views created by that user that include DEFINER clauses may be affected.

Practical Examples​

Example 1: Setting Up Development Team Access​

Let's create accounts for a team of developers working on a web application:

sql
-- Create the database
CREATE DATABASE webapp;

-- Create the lead developer account with full access
CREATE USER 'lead_dev'@'%' IDENTIFIED BY 'secure_password1';
GRANT ALL PRIVILEGES ON webapp.* TO 'lead_dev'@'%';

-- Create junior developer accounts with limited privileges
CREATE USER 'junior_dev1'@'localhost' IDENTIFIED BY 'secure_password2';
GRANT SELECT, INSERT, UPDATE ON webapp.* TO 'junior_dev1'@'localhost';
-- No DELETE privilege to prevent accidental data loss

-- Read-only account for QA testing
CREATE USER 'qa_tester'@'192.168.1.%' IDENTIFIED BY 'secure_password3';
GRANT SELECT ON webapp.* TO 'qa_tester'@'192.168.1.%';

-- Apply the changes
FLUSH PRIVILEGES;

Example 2: Database Migration Scenario​

When migrating a database, you might need temporary admin access:

sql
-- Create migration user with specific privileges
CREATE USER 'migration_user'@'10.0.0.5' IDENTIFIED BY 'temp_migration_pass';

-- Grant necessary privileges for the migration
GRANT SELECT, INSERT, CREATE, ALTER, INDEX, DROP, REFERENCES ON customer_db.* TO 'migration_user'@'10.0.0.5';

-- After migration is complete, remove the user
DROP USER 'migration_user'@'10.0.0.5';

Example 3: User Management Script​

Here's a practical script that might be used to set up a new environment:

sql
-- Database setup script for new environment

-- Create application database
CREATE DATABASE IF NOT EXISTS app_production;

-- Create user accounts
CREATE USER IF NOT EXISTS 'app_admin'@'%' IDENTIFIED BY 'admin_password';
CREATE USER IF NOT EXISTS 'app_service'@'%' IDENTIFIED BY 'service_password';
CREATE USER IF NOT EXISTS 'app_readonly'@'%' IDENTIFIED BY 'readonly_password';

-- Grant appropriate privileges
GRANT ALL PRIVILEGES ON app_production.* TO 'app_admin'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_production.* TO 'app_service'@'%';
GRANT SELECT ON app_production.* TO 'app_readonly'@'%';

-- Add specific table privileges
GRANT CREATE TEMPORARY TABLES ON app_production.* TO 'app_service'@'%';
REVOKE DELETE ON app_production.audit_logs FROM 'app_service'@'%';

-- Apply changes
FLUSH PRIVILEGES;

-- Output confirmation
SELECT CONCAT('User setup complete. Created ', COUNT(*), ' users.') AS result 
FROM mysql.user WHERE user LIKE 'app_%';

Password and Security Management​

Password Validation​

MySQL includes password validation plugins that can enforce password strength rules:

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

-- Configure password policy
SET GLOBAL validate_password.policy = 'MEDIUM';
SET GLOBAL validate_password.length = 8;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

Password Expiration​

You can set passwords to expire after a certain period:

sql
-- Set global default password expiration
SET GLOBAL default_password_lifetime = 90; -- 90 days

-- Set expiration for specific user
ALTER USER 'username'@'host' PASSWORD EXPIRE INTERVAL 60 DAY;

-- Force immediate password change
ALTER USER 'username'@'host' PASSWORD EXPIRE;

-- Disable expiration for a specific user
ALTER USER 'service_account'@'%' PASSWORD EXPIRE NEVER;

Role-Based Access Control (MySQL 8.0+)​

MySQL 8.0 introduced roles, which allow grouping privileges together and assigning them to users.

Creating and Managing Roles​

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

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

-- Grant roles to users
GRANT 'app_read' TO 'analyst'@'%';
GRANT 'app_read', 'app_write' TO 'developer'@'%';
GRANT 'app_admin' TO 'dba'@'localhost';

-- Set default role (active after login)
SET DEFAULT ROLE 'app_read' TO 'analyst'@'%';
SET DEFAULT ROLE ALL TO 'developer'@'%';

-- Switching active roles during a session
SET ROLE 'app_admin';

Best Practices for MySQL User Management​

  1. Principle of Least Privilege: Grant users only the permissions they need to perform their tasks.

  2. Use Strong Passwords: Enforce strong password policies using MySQL's password validation plugin.

  3. Avoid Using Root: Never use the root user for application connections; create specific users with limited permissions.

  4. Limit Connection Hosts: Restrict from which hosts users can connect by specifying IP addresses instead of using '%'.

  5. Regular Auditing: Periodically review user accounts and privileges:

    sql
    -- Find users with global privileges
    SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';

    -- Find unused accounts
    -- (Requires additional monitoring to identify unused accounts)

  6. Remove Unused Accounts: Delete accounts that are no longer needed.

  7. Use Roles for Privilege Management (MySQL 8.0+): Group privileges into roles for easier management.

  8. Enable Binary Logging: Keep track of changes to users and privileges:

    sql
    -- Check if binary logging is enabled
    SHOW VARIABLES LIKE 'log_bin';

  9. Use Secure Authentication: Prefer modern authentication methods like caching_sha2_password.

  10. Regular Password Rotation: Implement password expiration policies.

Summary​

MySQL user management is a critical aspect of database administration that directly affects your database security. We've covered:

  • Creating and modifying user accounts
  • Managing user privileges at different levels
  • Setting up authentication and password policies
  • Using roles for more efficient privilege management
  • Best practices for maintaining secure user access

With proper user management, you can ensure that your MySQL databases remain secure while still allowing appropriate access to users and applications.

Additional Resources and Exercises​

Additional Resources​

Exercises​

  1. Basic User Management:

    • Create a user that can connect only from localhost
    • Grant this user SELECT privileges on a specific database
    • Test the connection and verify the user can only read data

  2. Role-Based Access:

    • Create roles for 'reader', 'writer', and 'admin'
    • Assign appropriate privileges to each role
    • Create users and assign different roles to them
    • Test that each user has the correct level of access

  3. Security Audit:

    • Write a query to list all users with global privileges
    • Find all users that can connect from any host
    • Document which users have which privileges on which databases

  4. Migration Exercise:

    • Create a script that would safely migrate users from one MySQL server to another
    • Include their privileges in the migration

  5. Password Policy:

    • Configure MySQL's password validation plugin
    • Set up a policy that requires strong passwords
    • Test creating users with weak and strong passwords

These exercises will help you become more familiar with MySQL's user management capabilities and security best practices.


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)