MySQL User Management and Security

40 min

MySQL provides comprehensive user management and security features that control who can access the database and what they can do. User management includes creating users, assigning passwords, granting privileges, and managing roles. Security features include authentication, authorization, encryption, and network security. Understanding user management and security is essential for protecting sensitive data in production environments.

Users are created with CREATE USER and identified by passwords or authentication plugins. Users are specified with username and host (e.g., 'user'@'localhost' or 'user'@'%'). Host specification controls where users can connect from, enabling network-based access control. Understanding user creation enables you to create appropriate database access accounts.

Privileges control what users can do. MySQL supports global privileges (affecting all databases), database privileges (affecting specific databases), table privileges (affecting specific tables), and column privileges (affecting specific columns). Common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and ALTER. Understanding privileges enables fine-grained access control.

Roles (MySQL 8.0+) enable grouping privileges for easier management. You create roles, grant privileges to roles, and then grant roles to users. This simplifies privilege management, especially for users with similar access requirements. Roles can be granted to other roles, creating role hierarchies. Understanding roles enables efficient privilege management.

Security best practices include using strong passwords, following least privilege principle (granting only necessary privileges), regularly reviewing user privileges, using roles for privilege management, and enabling SSL/TLS for encrypted connections. Security should be implemented from the start, not added later. Understanding security best practices enables protecting your database.

Additional security features include password policies (enforcing strong passwords), account locking (preventing brute force attacks), password expiration, and audit logging. These features help maintain security over time. Understanding these features enables comprehensive database security.

Key Concepts

User management controls database access and privileges.
Users are identified by username and host.
Privileges control what users can do at various levels.
Roles enable grouping privileges for easier management.
Security requires multiple layers of protection.

Learning Objectives

Master

Creating and managing MySQL users
Granting and revoking privileges
Using roles for privilege management
Implementing security best practices

Develop

Understanding database security principles
Designing secure database access control
Implementing comprehensive security measures

Tips

Create user: CREATE USER 'user'@'host' IDENTIFIED BY 'password';
Grant privileges: GRANT SELECT, INSERT ON database.* TO 'user'@'host';
Show privileges: SHOW GRANTS FOR 'user'@'host';
Revoke privileges: REVOKE DELETE ON database.* FROM 'user'@'host';

Common Pitfalls

Granting excessive privileges, violating least privilege principle.
Not specifying hosts, allowing connections from anywhere.
Using weak passwords, compromising security.
Not reviewing privileges regularly, accumulating unnecessary access.

Summary

User management controls database access and operations.
Privileges enable fine-grained access control.
Roles simplify privilege management.
Following security best practices ensures database protection.

Exercise

Create users and manage permissions for different access levels.

-- Create a new user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Create a read-only user
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'readonly_pass';

-- Grant privileges to application user
GRANT SELECT, INSERT, UPDATE, DELETE ON my_first_db.* TO 'app_user'@'localhost';

-- Grant read-only privileges
GRANT SELECT ON my_first_db.* TO 'readonly_user'@'localhost';

-- Grant specific table privileges
GRANT SELECT, INSERT ON my_first_db.products TO 'app_user'@'localhost';

-- Grant specific column privileges
GRANT SELECT (id, name, price) ON my_first_db.products TO 'readonly_user'@'localhost';

-- Show user privileges
SHOW GRANTS FOR 'app_user'@'localhost';
SHOW GRANTS FOR 'readonly_user'@'localhost';

-- Create a role for different access levels
CREATE ROLE 'manager_role';
GRANT SELECT, INSERT, UPDATE ON my_first_db.* TO 'manager_role';

-- Assign role to user
GRANT 'manager_role' TO 'app_user'@'localhost';

-- Revoke privileges
REVOKE DELETE ON my_first_db.* FROM 'app_user'@'localhost';

-- Drop user (be careful!)
-- DROP USER 'readonly_user'@'localhost';

Exercise Tips

Use specific hosts: 'user'@'localhost' instead of 'user'@'%' when possible.
Grant minimal privileges: only grant what users need.
Use roles: CREATE ROLE 'role_name'; GRANT role TO user;
Review privileges regularly: SHOW GRANTS to audit user access.

MySQL User Management and Security