Users, Roles, GRANT, REVOKE and Least Privilege

Concept

Access control in SQL has three parts: a user (who connects), a privilege (what they may do), and an object (what they may do it to). A role is a named bundle of privileges you grant to users instead of repeating yourself.

Syntax

-- Create a user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'a-strong-password';

-- Grant specific privileges on specific objects
GRANT SELECT, INSERT, UPDATE, DELETE ON notes_management.* TO 'app_user'@'localhost';

-- Grant on a single table, or specific columns
GRANT SELECT ON notes_management.notes TO 'report_user'@'%';
GRANT SELECT (id, first_name, last_name) ON company.employees TO 'hr_read'@'%';

-- Take it back
REVOKE DELETE ON notes_management.* FROM 'app_user'@'localhost';

-- Inspect
SHOW GRANTS FOR 'app_user'@'localhost';

DROP USER 'app_user'@'localhost';

The privileges that matter

Roles

-- MySQL 8, MariaDB 10.0.5+, PostgreSQL, SQL Server and Oracle all have roles
CREATE ROLE 'read_only', 'app_write', 'schema_admin';

GRANT SELECT                          ON notes_management.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE  ON notes_management.* TO 'app_write';
GRANT ALL PRIVILEGES                  ON notes_management.* TO 'schema_admin';

-- Assign roles to users
GRANT 'app_write' TO 'app_user'@'localhost';
GRANT 'read_only' TO 'analyst'@'%';

-- MySQL requires roles to be activated
SET DEFAULT ROLE ALL TO 'app_user'@'localhost';

Roles turn "twelve users each granted eight privileges" into "three roles, twelve assignments". When the policy changes you edit the role once, and a leaver is revoked in one statement.

Least privilege, applied

A typical application needs three accounts, not one:

-- Reporting through views, so the underlying tables stay private
CREATE VIEW v_public_employees AS
SELECT id, first_name, last_name, dept_id FROM employees;   -- no salary

GRANT SELECT ON company.v_public_employees TO 'report_user'@'%';
-- No grant on company.employees at all

This is where views become a security tool: the role can read exactly the columns and rows the view exposes, and nothing else. Add WITH CHECK OPTION if the view is also writable.

Dialect differences

In MySQL, 'app'@'localhost' and 'app'@'%' are different users with separate privileges - a frequent source of "it works locally but not from the app server".

Important rules

• Privileges are additive; REVOKE removes, it does not deny. There is no explicit deny in MySQL.
• MySQL 8 removed the old GRANT ... IDENTIFIED BY shortcut - create the user first, then grant.
• FLUSH PRIVILEGES is only needed after editing the grant tables directly, not after GRANT.
• Granting on *.* grants across every database on the server.
• A user with GRANT OPTION can escalate their own reach.

Common mistakes

• Running the application as root - an injection flaw then becomes total compromise.
• One account shared by the application, the migrations and the analysts.
• Granting ALL PRIVILEGES because a specific grant was fiddly to work out.
• Passwords in source control instead of environment variables or a secret store.
• Creating 'app'@'%' and exposing the database to the whole network.
• Never revoking access for people who have left.

Best practices

• One account per purpose, each with the narrowest privilege set that works.
• Use roles, and grant roles to users.
• Restrict the host part of MySQL accounts to the application servers.
• Expose reporting through views, not base tables.
• Keep credentials in configuration outside the repository - as this project does with its .env file.
• Audit SHOW GRANTS for every account on a schedule.

Practice

1. Write the exact grants for an application account that must read and write the notes schema but never change it.
2. Create a read only reporting role that can see employees but not salaries.
3. Explain why 'app'@'localhost' and 'app'@'%' behave differently in MySQL.