MySQL Security

MySQL Security

-- Create users with least privilege
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'webapp'@'localhost';
FLUSH PRIVILEGES;

-- Read-only user for reporting
CREATE USER 'reporter'@'%' IDENTIFIED BY 'pass';
GRANT SELECT ON mydb.* TO 'reporter'@'%';

-- Show grants
SHOW GRANTS FOR 'webapp'@'localhost';

-- Revoke
REVOKE DELETE ON mydb.* FROM 'webapp'@'localhost';

-- Drop user
DROP USER 'webapp'@'localhost';

-- Change password
ALTER USER 'webapp'@'localhost' IDENTIFIED BY 'newpass';

-- Secure installation
mysql_secure_installation  # remove test DB, anonymous users

-- Enable SSL
SHOW VARIABLES LIKE 'have_ssl';