Lesson 30 / 30

Users, Privileges & Security

Create MySQL users with least-privilege GRANTs, avoid using root in applications and protect data with strong passwords.

Least privilege

Applications should not connect as root. Create a dedicated user per application and grant only the privileges it needs, on only the database it uses.

Create a user, grant limited rights and review them.

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
SHOW GRANTS FOR 'shop_app'@'localhost';

REVOKE DELETE ON shop.* FROM 'shop_app'@'localhost';

Never build SQL by concatenating user input. Use prepared statements or parameterised queries to prevent SQL injection, and keep passwords out of source control.

Quick check

Quick check: Which practice best prevents SQL injection?

  • Parameterised queries
  • Concatenating strings
  • Using root everywhere
  • Disabling indexes
Answer

Parameterised queries — Parameters keep user input separate from SQL code.