Database Security

Protecting Your Data

Databases store your most valuable asset — data. Security measures protect it from unauthorized access, breaches, and loss.

Authentication and Authorization

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

  -- Grant specific privileges
  GRANT SELECT, INSERT, INSERT ON mydb.* TO 'app_user'@'localhost';

  -- Revoke privileges
  REVOKE DELETE ON mydb.* FROM 'app_user'@'localhost';

  -- Show privileges
  SHOW GRANTS FOR 'app_user'@'localhost';

  -- Drop user
  DROP USER 'app_user'@'localhost';

SQL Injection

  DANGEROUS: User input directly in query
  ┌──────────────────────────────────────────────┐
  │ "SELECT * FROM users WHERE name = '"        │
  │ + username + "' AND pass = '" + password + "'│"
  └──────────────────────────────────────────────┘

  If username = "'; DROP TABLE users; --"
  The query becomes:
  SELECT * FROM users WHERE name = ''; DROP TABLE users; --' AND pass = ''
  → Deletes your entire users table!

  SAFE: Use parameterized queries
  ┌──────────────────────────────────────────────┐
  │ SELECT * FROM users WHERE name = ? AND pass = ?│
  │ (values bound separately, never executed as SQL)│
  └──────────────────────────────────────────────┘

Always use parameterized queries or prepared statements. Never concatenate user input into SQL strings.

Encryption

• Encryption at rest — Data is encrypted on disk. Even if someone steals the hard drive, they can't read it without the key.
• Encryption in transit — Data is encrypted between client and server using TLS/SSL.
• Column-level encryption — Sensitive columns (passwords, credit cards) are encrypted individually.