Why access control protects data in MySQL - See It in Action

Why Access Control Protects Data

📖 Scenario: You are managing a small company's database that stores employee information. To keep sensitive data safe, you need to control who can see or change the data.

🎯 Goal: Build a simple database table and set up access control by creating a user with limited permissions. This will show how access control protects data by restricting what users can do.

📋 What You'll Learn

Create a table called employees with columns id, name, and salary

Insert three employee records with exact values

Create a user called readonly_user with password 'readonlypass'

Grant only SELECT permission on the employees table to readonly_user

Demonstrate the permission by writing a query that readonly_user can run

💡 Why This Matters

🌍 Real World

Companies use access control to protect sensitive employee data from unauthorized changes or views.

💼 Career

Database administrators must know how to create users and assign permissions to keep data safe and comply with privacy rules.

1

Create the employees table and insert data

Write SQL statements to create a table called employees with columns id (integer), name (varchar 50), and salary (integer). Then insert these three rows exactly: (1, 'Alice', 70000), (2, 'Bob', 55000), and (3, 'Charlie', 60000).

MySQL

-- Your code here to create the employees table and insert data

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create a user with limited access

Write an SQL statement to create a user called readonly_user with password 'readonlypass'.

MySQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  salary INT
);

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 70000),
(2, 'Bob', 55000),
(3, 'Charlie', 60000);

-- Your code here to create readonly_user

Need a hint?

Use CREATE USER 'username'@'host' IDENTIFIED BY 'password' syntax.

3

Grant SELECT permission to the user

Write an SQL statement to grant only SELECT permission on the employees table to the user readonly_user.

MySQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  salary INT
);

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 70000),
(2, 'Bob', 55000),
(3, 'Charlie', 60000);

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'readonlypass';

-- Your code here to grant SELECT permission

Need a hint?

Use GRANT SELECT ON table TO user syntax.

4

Write a query to show data accessible by the user

Write a SQL query that the user readonly_user can run to see all employee names and salaries from the employees table.

MySQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  salary INT
);

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 70000),
(2, 'Bob', 55000),
(3, 'Charlie', 60000);

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'readonlypass';

GRANT SELECT ON employees TO 'readonly_user'@'localhost';

-- Your code here to select name and salary

Need a hint?

Use SELECT name, salary FROM employees; to view allowed data.