You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Securing a MySQL installation means controlling who can connect, what they can do, and how they authenticate. Proper user management is a critical part of running MySQL in production.
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'StrongP@ssword1!';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'AnotherStr0ngPass!';
The host part (localhost, %, 192.168.1.%) controls which hosts the user can connect from. % is a wildcard matching any host.
Privileges are granted at the global, database, table, or column level:
-- All privileges on a specific database
GRANT ALL PRIVILEGES ON myapp.* TO 'alice'@'localhost';
-- Read-only access on a specific table
GRANT SELECT ON myapp.products TO 'appuser'@'%';
-- Execute a specific stored procedure
GRANT EXECUTE ON PROCEDURE myapp.GetUserPosts TO 'appuser'@'%';
FLUSH PRIVILEGES;
REVOKE INSERT, UPDATE ON myapp.* FROM 'appuser'@'%';
SHOW GRANTS FOR 'alice'@'localhost';
Application service accounts should have only the privileges they need. A typical web application needs SELECT, INSERT, UPDATE, and DELETE on its own database — not DROP, CREATE, or SUPER. Reserve highly privileged accounts for administrative tasks performed by humans.
-- Change a user's password
ALTER USER 'alice'@'localhost' IDENTIFIED BY 'NewP@ssword2!';
-- Rename a user
RENAME USER 'alice'@'localhost' TO 'alice_admin'@'localhost';
-- Remove a user entirely
DROP USER 'alice_admin'@'localhost';
MySQL 8 defaults to the caching_sha2_password authentication plugin, which is more secure than the older mysql_native_password. Some older clients may require:
ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password1!';
A well-secured MySQL installation significantly reduces the blast radius of any application-level vulnerability or credential leak.
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.