Granting privileges in MySQL - Mini Project: Build & Apply

Granting Privileges in MySQL

📖 Scenario: You are managing a small company's database. You need to control who can access and change the data. This is important to keep the data safe and organized.

🎯 Goal: Learn how to grant specific privileges to a user in MySQL so they can perform certain actions on a database.

📋 What You'll Learn

Create a new user with a password

Grant SELECT privilege on a specific database to the user

Grant INSERT privilege on a specific table to the user

Apply the changes so the privileges take effect

💡 Why This Matters

🌍 Real World

Database administrators often need to control who can read or change data. Granting privileges helps keep data safe and organized.

💼 Career

Knowing how to manage user privileges is essential for roles like database administrator, backend developer, and data analyst.

1

Create a new user

Write a MySQL statement to create a new user called 'report_user' with the password 'ReportPass123'.

MySQL

-- Your code here to create the user 'report_user' with password 'ReportPass123'

Need a hint?

Use the CREATE USER statement with the exact username and password given.

2

Grant SELECT privilege on the database

Write a MySQL statement to grant the SELECT privilege on the database called company_data to the user 'report_user'.

MySQL

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'ReportPass123';
-- Your code here to grant SELECT on company_data to 'report_user'

Need a hint?

Use GRANT SELECT ON company_data.* TO 'report_user'@'localhost'; to allow reading data from all tables in the database.

3

Grant INSERT privilege on a specific table

Write a MySQL statement to grant the INSERT privilege on the table sales_records in the company_data database to the user 'report_user'.

MySQL

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'ReportPass123';
GRANT SELECT ON company_data.* TO 'report_user'@'localhost';
-- Your code here to grant INSERT on company_data.sales_records to 'report_user'

Need a hint?

Use GRANT INSERT ON company_data.sales_records TO 'report_user'@'localhost'; to allow adding new rows to the sales_records table.

4

Apply the privilege changes

Write a MySQL statement to reload the privilege tables so the changes take effect immediately.

MySQL

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'ReportPass123';
GRANT SELECT ON company_data.* TO 'report_user'@'localhost';
GRANT INSERT ON company_data.sales_records TO 'report_user'@'localhost';
-- Your code here to reload privileges

Need a hint?

Use FLUSH PRIVILEGES; to make sure MySQL applies the new permissions right away.