MySQL new database, user, grant permissions cheatsheet

user-permissions.sql

-- =========
-- = Users =
-- =========

-- Create new user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Delete user
DROP USER 'username'@'localhost';

-- List users
SELECT CONCAT('\'', User, '\'@\'', Host, '\'') AS user FROM mysql.user;

-- =============
-- = Databases =
-- =============

-- Create new utf8 database
CREATE DATABASE database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Drop database
DROP DATABASE database;

-- List databases
SHOW DATABASES;

-- ==========
-- = Grants =
-- ==========

-- Grant permissions
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';
GRANT SELECT ON database.* TO 'username'@'localhost'; -- Read only
GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'username'@'localhost'; -- CRUD

-- Revoke permissions
REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'localhost';

-- Show permissions
SHOW GRANTS FOR 'username'@'localhost';

-- Flush permissions
FLUSH PRIVILEGES;