MySQL/MariaDB User Management and Access Privileges

Create a MySQL User

-- localhost only
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- Any host
CREATE USER 'myuser'@'%' IDENTIFIED BY 'StrongPassword123!';

Grant Privileges

-- Full access to one database
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

-- Read-only
GRANT SELECT ON mydb.* TO 'readonly_user'@'localhost';

-- App user
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost';

FLUSH PRIVILEGES;

View Privileges

SHOW GRANTS FOR 'myuser'@'localhost';
SELECT User, Host FROM mysql.user;

Revoke Privileges

REVOKE ALL PRIVILEGES ON mydb.* FROM 'myuser'@'localhost';
FLUSH PRIVILEGES;

Create User for a Web App

CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'SecurePass789!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;