Grant MySQL privileges

A new account can log in and do nothing else. GRANT adds capabilities, and each grant names three things: which privileges, on what scope, to which account. The scope notes_app.* means every table in the notes_app database and nothing outside it.

The running application needs to read and change rows. It does not need to create or drop tables:

GRANT SELECT, INSERT, UPDATE, DELETE
ON notes_app.*
TO 'notes_app'@'localhost';

The migration account changes the schema. It also needs row access for backfills:

GRANT SELECT, INSERT, UPDATE, DELETE,
  CREATE, ALTER, INDEX, DROP, REFERENCES
ON notes_app.*
TO 'notes_migrator'@'localhost';

Neither account receives privileges on *.*, the global scope. A leaked application credential is now limited to one database and cannot change its tables.

GRANT takes effect immediately for new connections. You do not need FLUSH PRIVILEGES after it — that command is only for the rare case where someone edited the grant tables directly with INSERT or UPDATE.

Verify the boundary

Check what an account holds:

SHOW GRANTS FOR 'notes_app'@'localhost';

The output lists one line per grant. For notes_app you should see exactly two: a GRANT USAGE ON *.* line, which means “may connect, nothing more” and appears for every account, and the SELECT, INSERT, UPDATE, DELETE grant on notes_app.* you just issued. Anything beyond those two lines is access this account should not have.

Reading grants is necessary but not sufficient. The convincing check is behavioral: test the boundary by connecting as notes_app and trying something the account must not be able to do:

DROP TABLE notes;

MySQL should return an access-denied error:

ERROR 1142 (42000): DROP command denied to user 'notes_app'@'localhost' for table 'notes'

A failed dangerous action proves the restriction works. My advice is to run this negative test every time you set up accounts. It takes ten seconds and catches the day someone “temporarily” granted too much.