Database roles and permissions: least privilege for every user

Least Privilege

The principle of least privilege: every database user gets only the permissions it needs, nothing more. A compromised application credential should not be able to DROP TABLE or export your entire database.

Creating Roles

-- Application user: read/write on app tables
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%';

-- Read-only user: for reporting tools, read replicas
CREATE USER 'report_user'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON app_db.* TO 'report_user'@'%';

-- Admin: limited to DBA team, never used by application
CREATE USER 'dba_admin'@'localhost' IDENTIFIED BY 'very_strong_password';
GRANT ALL PRIVILEGES ON app_db.* TO 'dba_admin'@'localhost';

Key Rules

• The application's DB user should never have DROP, TRUNCATE, or CREATE privileges.
• Never use the root user in application connection strings.
• Use a dedicated migration user (with schema-change privileges) only during deployments.
• Audit privilege assignments regularly — revoke what is no longer needed.

-- Revoke an overpermissioned grant
REVOKE DELETE ON app_db.users FROM 'app_user'@'%';