MySQL のユーザー管理

CREATE USER 'alice'@'localhost' IDENTIFIED BY 'strong_password';

-- Read-only access to one database
GRANT SELECT ON myapp.* TO 'alice'@'localhost';

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

-- Specific privileges on one table
GRANT SELECT, INSERT, UPDATE ON myapp.orders TO 'alice'@'localhost';

-- Global privilege (all databases)
GRANT PROCESS ON *.* TO 'alice'@'localhost';

-- Show grants for the current user
SHOW GRANTS;

-- Show grants for a specific user
SHOW GRANTS FOR 'alice'@'localhost';

REVOKE INSERT ON myapp.* FROM 'alice'@'localhost';
REVOKE ALL PRIVILEGES ON myapp.* FROM 'alice'@'localhost';

ALTER USER 'alice'@'localhost' IDENTIFIED BY 'new_password';

ALTER USER 'alice'@'localhost' PASSWORD EXPIRE;

-- Create a role
CREATE ROLE 'app_reader', 'app_writer';

-- Grant privileges to the role
GRANT SELECT ON myapp.* TO 'app_reader';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_writer';

-- Grant the role to a user
GRANT 'app_reader' TO 'alice'@'localhost';
GRANT 'app_writer' TO 'bob'@'%';

-- Activate in session
SET ROLE 'app_reader';

-- Set default role so it activates automatically on login
SET DEFAULT ROLE 'app_reader' TO 'alice'@'localhost';

DROP USER 'alice'@'localhost';