Why access control protects data in MySQL

Introduction

Access control keeps data safe by letting only the right people see or change it.

When you want to stop strangers from reading private information.

When only certain workers should update company records.

When you want to track who can add or delete data.

When you need to follow rules about data privacy.

When you want to prevent accidental changes by users.

Syntax

MySQL

GRANT privileges ON database.table TO 'user'@'host';
REVOKE privileges ON database.table FROM 'user'@'host';

Use GRANT to give permissions like SELECT, INSERT, UPDATE, DELETE.

Use REVOKE to remove permissions when no longer needed.

Examples

Gives user 'alice' permission to read data from the customers table.

MySQL

GRANT SELECT ON mydb.customers TO 'alice'@'localhost';

Removes permission for user 'bob' to add new orders.

MySQL

REVOKE INSERT ON mydb.orders FROM 'bob'@'localhost';

Gives 'carol' full access to all tables in the mydb database from any host.

MySQL

GRANT ALL PRIVILEGES ON mydb.* TO 'carol'@'%';

Sample Program

This creates a database and table, grants read-only access to 'testuser', and shows the permissions.

MySQL

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;
CREATE TABLE IF NOT EXISTS customers (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
GRANT SELECT ON mydb.customers TO 'testuser'@'localhost';
SHOW GRANTS FOR 'testuser'@'localhost';

Important Notes

Always give the least permissions needed to keep data safe.

Access control helps prevent mistakes and attacks on your data.

Summary

Access control limits who can see or change data.

Use GRANT and REVOKE to manage permissions.

Proper access control keeps data safe and trustworthy.