Labs · Cyber Security

Lab: Create a Practice Database and a Least-Privilege MySQL User That Can Only Read (SELECT)

By Ravindra Bagale · Last updated: 8 October 2026

Suppose we are…

Suppose we are a backend trainee at Meesho. The analytics team wants to connect a dashboard to our product database. Our lead says: "Give them a user that can read the shop tables and nothing else. No INSERT, no DELETE, no DROP, and no access to other databases." This is the principle of least privilege (every user gets the minimum rights it needs).

Goal of this lab

By the end you will have:

• MariaDB (a free database that works like MySQL) installed and secured.
• A practice database shop with a products table and 5 rows.
• A user report that can only SELECT from shop, with proof that other actions are denied.

What you need (all free)

• Your free-tier EC2 Amazon Linux 2023 server (no new port needed: we connect locally).
• 35–40 minutes.

Part 1: install and secure

1. SSH to the server and install MariaDB:

   sudo dnf install -y mariadb105-server
   sudo systemctl enable --now mariadb
   

2. Run the security script:

   sudo mariadb-secure-installation
   

   Press Enter for the current root password (empty). Answer n to "Switch to unix_socket authentication" (it is already on), Y to remove anonymous users, Y to disallow root login remotely, Y to remove the test database and Y to reload privileges.

Part 2: practice database

3. Open the database shell as root and create the practice data:

   sudo mariadb
   

   CREATE DATABASE shop;
   USE shop;
   CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), price INT);
   INSERT INTO products (name, price) VALUES ('Kurti', 499), ('Saree', 1299), ('Bedsheet', 699), ('Earrings', 199), ('Backpack', 899);
   SELECT * FROM products;
   

   👁 What you should see: a table with 5 rows, 5 rows in set.

Part 3: the least-privilege user

4. Still in the root shell, create the user and give only SELECT on shop:

   CREATE USER 'report'@'localhost' IDENTIFIED BY 'Lab-Only-Pass-2026';
   GRANT SELECT ON shop.* TO 'report'@'localhost';
   SHOW GRANTS FOR 'report'@'localhost';
   EXIT;
   

   👁 What you should see: GRANT USAGE ON *.* ... (only means "can log in") and GRANT SELECT ON shop.* TO report@localhost.

5. Log in as the new user:

   mariadb -u report -p shop
   

   Type Lab-Only-Pass-2026 when asked.

6. Try reading, then try the forbidden actions:

   SELECT name, price FROM products WHERE price > 500;
   INSERT INTO products (name, price) VALUES ('Hack', 1);
   DROP TABLE products;
   SHOW DATABASES;
   

   👁 What you should see: SELECT returns Saree, Bedsheet and Backpack. INSERT fails with ERROR 1142 (42000): INSERT command denied to user 'report'@'localhost'. DROP fails with DROP command denied. SHOW DATABASES lists only information_schema and shop.

7. Type EXIT;.

Common mistakes

Self-check checklist

Try-at-home challenge

The dashboard team now also needs to add rows to a new table feedback, but must still not change products. Write the SQL to create the table and give exactly that extra right.

CREATE TABLE shop.feedback (id INT PRIMARY KEY AUTO_INCREMENT, msg VARCHAR(200));
GRANT INSERT ON shop.feedback TO 'report'@'localhost';
SHOW GRANTS FOR 'report'@'localhost';

Clean up to avoid charges

Keep the server for Lab 11 (backup and restore) if you continue today. Otherwise: EC2 → Instances → Instance state → Terminate (delete) instance.

Samjla ka? One user per app, only the rights it needs. Aata pudhe jaauya: Chapter 11 backs up this database and removes users we do not need.