Users, Permissions and Backups

A database is only production-ready when access is controlled and a restore has been tested.

Create a User

Give the application only the permissions it needs on one database. Grant nothing globally.

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'a-long-random-password';

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

FLUSH PRIVILEGES;

Backups

mysqldump writes a SQL file that recreates the schema and data. Test a restore on a separate database regularly.

mysqldump -u root -p school > school-backup.sql

mysql -u root -p school_restore < school-backup.sql

Credentials

Keep database passwords in environment variables, never in source code or a public repository. Rotate them if they leak.

Practice

1. Create a read-only user for reporting.
2. Dump a database and restore it under a new name.
3. List the grants for a user.

Hands-on exercise

Write SQL that:
1. Creates a user reporter with a password.
2. Grants only SELECT on every table in school.
3. Applies the privileges.

Recap

Least-privilege users limit damage, tested backups make recovery possible, and credentials belong outside source code.