MySQL Security Basics

Security is one of the most important aspects of database management.

A database often stores sensitive information such as:

  • User credentials
  • Financial records
  • Personal data
  • Business information

If security is not properly managed, data can be stolen, modified, or deleted.

MySQL provides built-in features to protect databases from unauthorized access.

1. Users in MySQL

In MySQL, access is controlled through user accounts.

Each user has:

  • A username
  • A password
  • Specific privileges

To create a new user:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Example:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123';

2. Privileges in MySQL

Privileges determine what actions a user can perform.

Common privileges:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CREATE
  • DROP
  • ALL PRIVILEGES

3. Granting Privileges

Use the GRANT command to give permissions.

Syntax:

GRANT privilege_type

ON database_name.table_name

TO 'username'@'host';

Example:

GRANT SELECT, INSERT

In school.students

TO 'app_user'@'localhost';

This allows the user to:

  • Read data
  • Insert data
    But do not delete or drop tables.

To apply changes:

FLUSH PRIVILEGES;

4. Revoking Privileges

To remove permissions:

REVOKE INSERT

In school.students

FROM 'app_user'@'localhost';

This removes the insert permission.

5. Roles in MySQL

Roles allow grouping multiple privileges.

Instead of assigning permissions to users individually, you can:

  1. Create a role
  2. Assign privileges to the role
  3. Assign the role to users

Example:

CREATE ROLE 'sales_role';

Grant privileges to the role:

GRANT SELECT ON company.orders TO 'sales_role';

Assign role to a user:

GRANT 'sales_role' TO 'app_user'@'localhost';

Roles simplify user management in large systems.

6. Principle of Least Privilege

Users should only have the permissions they absolutely need.

For example:

  • A reporting user should only have SELECT permission.
  • An application user should not have DROP or ALTER privileges.

This reduces security risks.

7. SQL Injection (Conceptual Overview)

SQL injection is a security vulnerability where attackers insert malicious SQL code into application input fields.

Example of unsafe query in application code:

SELECT * FROM users WHERE username = 'input';

If input is not validated, an attacker could manipulate the query.

Prevention methods:

  • Use prepared statements
  • Use parameterized queries
  • Validate and sanitize inputs
  • Avoid dynamic SQL construction

SQL injection protection is mainly handled at the application level, but understanding the concept is important for database security.

8. Best Security Practices

  1. Use strong passwords.
  2. Avoid using the root account in applications.
  3. Grant minimum required privileges.
  4. Regularly review user accounts.
  5. Disable anonymous users.
  6. Keep MySQL updated.
  7. Use encrypted connections (SSL).
  8. Take regular backups.

9. Real-World Example

In a company system:

  • HR team can access employee salary data.
  • The sales team can only view customer orders.
  • Developers can modify development databases but not production databases.

Using users, roles, and privileges, you can strictly control access.

Summary

MySQL security is based on:

  • User accounts
  • Privileges
  • Roles
  • GRANT and REVOKE commands

Important principles:

  • Follow the least privilege
  • Protect against SQL injection
  • Use strong authentication
  • Monitor and control access

Proper security ensures that sensitive data remains protected, and only authorized users can access or modify the database.