MySQL User Management

Key User Management Tasks

CREATE USER 'user'
@'localhost' 
IDENTIFIED BY 'pass';

GRANT ALL 
ON database.* 
TO 'user'@'host';

REVOKE INSERT 
ON database.* 
FROM 'user'@'host';

DROP USER 
'user'@'localhost';

🔹 Creating Users

Create new MySQL users with CREATE USER command. Specify username, host (where they can connect from), and password. The host can be 'localhost' for local connections, a specific IP, or '%' for any host. Always use strong passwords for security.

-- Create user for local access
CREATE USER 'john'@'localhost' IDENTIFIED BY 'StrongPass123!';

-- Create user for specific IP
CREATE USER 'jane'@'192.168.1.100' IDENTIFIED BY 'SecurePass456!';

-- Create user for any host (less secure)
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass789!';

-- Create user without password (not recommended)
CREATE USER 'guest'@'localhost';

🔹 Granting Privileges

Use GRANT to give users specific permissions on databases, tables, or columns. Grant only the minimum privileges needed for each user's role. Common privileges include SELECT, INSERT, UPDATE, DELETE, and CREATE. Use ALL PRIVILEGES carefully as it grants complete control.

-- Grant all privileges on a database
GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'jane'@'localhost';

-- Grant privileges on specific table
GRANT SELECT ON mydb.products TO 'readonly'@'localhost';

-- Grant privileges on all databases
GRANT SELECT ON *.* TO 'auditor'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

🔹 Viewing User Privileges

Check what privileges a user has with SHOW GRANTS. This helps audit user permissions and troubleshoot access issues. Review user privileges regularly to ensure they align with current roles and responsibilities. Remove unnecessary privileges to maintain security.

-- Show privileges for current user
SHOW GRANTS;

-- Show privileges for specific user
SHOW GRANTS FOR 'john'@'localhost';

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

-- View detailed user information
SELECT * FROM mysql.user WHERE User = 'john';

🔹 Revoking Privileges

Remove privileges from users with REVOKE when they no longer need certain access. This is essential for maintaining security when user roles change. Always revoke unnecessary privileges promptly to minimize security risks and follow the principle of least privilege.

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

-- Revoke all privileges on a database
REVOKE ALL PRIVILEGES ON mydb.* FROM 'jane'@'localhost';

-- Revoke grant option (ability to grant privileges to others)
REVOKE GRANT OPTION ON mydb.* FROM 'admin'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

🔹 Modifying User Passwords

Change user passwords regularly for security. Use ALTER USER or SET PASSWORD commands. Enforce strong password policies and require password changes periodically. Never store passwords in plain text or share them insecurely. Consider using password expiration policies.

-- Change password for user (MySQL 5.7.6+)
ALTER USER 'john'@'localhost' IDENTIFIED BY 'NewStrongPass123!';

-- Change password using SET PASSWORD
SET PASSWORD FOR 'jane'@'localhost' = PASSWORD('NewSecurePass456!');

-- Change your own password
ALTER USER USER() IDENTIFIED BY 'MyNewPassword789!';

-- Expire password (force user to change on next login)
ALTER USER 'john'@'localhost' PASSWORD EXPIRE;

🔹 Deleting Users

Remove users who no longer need database access with DROP USER. This permanently deletes the user account and all associated privileges. Always verify you're deleting the correct user and document user removals for audit purposes. Consider disabling instead of deleting for temporary situations.

-- Delete single user
DROP USER 'john'@'localhost';

-- Delete multiple users
DROP USER 'user1'@'localhost', 'user2'@'localhost';

-- Delete user if exists (no error if doesn't exist)
DROP USER IF EXISTS 'olduser'@'localhost';

-- Verify user is deleted
SELECT User, Host FROM mysql.user WHERE User = 'john';

🔹 Role-Based Access Control

MySQL 8.0+ supports roles, which are named collections of privileges. Create roles for common job functions, grant privileges to roles, then assign roles to users. This simplifies management when multiple users need the same permissions and makes privilege changes easier.

-- Create roles
CREATE ROLE 'app_developer', 'app_readonly', 'app_admin';

-- Grant privileges to roles
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_developer';
GRANT SELECT ON mydb.* TO 'app_readonly';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

-- Assign roles to users
GRANT 'app_developer' TO 'john'@'localhost';
GRANT 'app_readonly' TO 'jane'@'localhost';

-- Set default role for user
SET DEFAULT ROLE 'app_developer' TO 'john'@'localhost';

-- View roles
SHOW GRANTS FOR 'app_developer';

🔹 Security Best Practices

Follow security best practices to protect your database: use strong passwords, limit root access, grant minimum necessary privileges, disable remote root login, use SSL connections, regularly audit user accounts, and monitor login attempts. Security is an ongoing process requiring constant attention.

-- Rename root user for security
RENAME USER 'root'@'localhost' TO 'admin'@'localhost';

-- Disable remote root access
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1');

-- Require SSL for user
ALTER USER 'john'@'%' REQUIRE SSL;

-- Set account resource limits
ALTER USER 'app_user'@'%' 
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_CONNECTIONS_PER_HOUR 100;

-- Lock user account
ALTER USER 'john'@'localhost' ACCOUNT LOCK;

-- Unlock user account
ALTER USER 'john'@'localhost' ACCOUNT UNLOCK;