20. Users and Permissions

Users and Permissions

Database users control who can connect and what they can do. Good permission design protects data from accidental or unauthorized changes.

Create a user

CREATE USER "report_user"@"localhost"
IDENTIFIED BY "strong_password_here";

The part before @ is the username. The part after @ is the host the user can connect from.

Grant read-only access

GRANT SELECT
ON companydb.*
TO "report_user"@"localhost";

This user can read every table in companydb, but cannot insert, update, delete, or alter tables.

Grant access to one table

GRANT SELECT, INSERT
ON companydb.customers
TO "app_writer"@"localhost";

Use table-level grants when a user only needs part of a database.

Apply privilege changes

Modern MySQL applies many grant changes immediately, but FLUSH PRIVILEGES is still common after direct privilege table changes.

FLUSH PRIVILEGES;

Revoke permissions

REVOKE INSERT
ON companydb.customers
FROM "app_writer"@"localhost";

Remove a user:

DROP USER "report_user"@"localhost";

Check grants

SHOW GRANTS FOR "report_user"@"localhost";

Least privilege

Least privilege means each user gets only the permissions needed for their job.

Avoid using the root user in applications. Create specific users for applications, dashboards, and maintenance tasks.