MySQL Privileges

In MySQL, privileges determine what operations a user can perform on the database. They are classified into three main categories:

Data Privileges

🧾 Allow the user to directly manipulate the information stored in the tables (read, insert, update or delete data).

No.	Privilege	What does it allow?	Activity to perform
1	SELECT	Read data from a table.	Write a query to get products with a price greater than 100.
2	INSERT	Insert records into a table.	Add a new customer to the `customers` table.
3	UPDATE	Modify existing records.	Update the email of a specific user.
4	DELETE	Delete records.	Delete products whose stock is 0.
5	FILE	Read/write system files from the database.	Load a CSV file from the server into a table.

Structure Privileges

🧾 Allow modifying the structure of databases and their objects: create, alter or drop tables, views, procedures, among others.

No.	Privilege	What does it allow?	Activity to perform
6	CREATE	Create new tables or databases.	Create a table named `orders`.
7	ALTER	Modify the structure of an existing table.	Add a `shipping_date` column to the `orders` table.
8	DROP	Delete structures like tables.	Delete the `obsolete_products` table.
9	INDEX	Create or delete indexes.	Create an index on the `email` field of the `users` table.
10	CREATE TEMPORARY TABLES	Create temporary tables.	Create a temporary table to store intermediate results.
11	SHOW VIEW	Query views.	Query and explain what the `monthly_sales` view shows.
12	CREATE VIEW	Create new views.	Generate a view showing customers with more than 3 purchases.
13	TRIGGER	Create triggers.	Create a trigger that logs every time a sale is inserted.
14	EVENT	Create scheduled events.	Create an event that deletes old records every month.
15	CREATE ROUTINE	Create procedures or functions.	Create a routine to apply a discount to all products.
16	ALTER ROUTINE	Modify existing routines.	Modify a procedure to receive a new parameter.
17	EXECUTE	Execute functions or procedures.	Execute a function that calculates total sales per customer.

Administration Privileges

🧾 Grant database system management capabilities, such as creating users, assigning permissions, monitoring processes or configuring replication.

No.	Privilege	What does it allow?	Activity to perform
18	GRANT	Assign privileges to other users.	Grant read permissions to a new user.
19	SUPER	Execute advanced administrative operations.	Simulate changing a global variable.
20	PROCESS	View active processes on the server.	Display active processes and kill a specific one.
21	RELOAD	Reload tables and privileges from disk.	Simulate reloading permissions after a change.
22	SHUTDOWN	Shut down the MySQL server.	(Do not perform) Only investigate the shutdown process.
23	SHOW DATABASES	View all available databases.	List databases accessible to the current user.
24	LOCK TABLES	Lock tables for exclusive use.	Lock a table, perform an operation and unlock it.
25	REFERENCES	Use foreign keys.	Create a relationship between `customers` and `orders`.
26	REPLICATION CLIENT	Query replication status.	Query the replication binlog status.
27	REPLICATION SLAVE	Act as a replica in replication.	Investigate how to configure a replica database.
28	CREATE USER	Create new database users.	Create a user with read-only permissions.

Activity

If you made it this far, congratulations! This activity is not graded, but the knowledge you will acquire if you develop it will be monumental.
You will see how to precisely control what each user can do in your database... like a true expert administrator.

Objective

Understand the meaning, use, and application of the different privileges in MySQL classified into:

📂 Data
🏗️ Structure
⚙️ Administration

Through this activity, participants will explore what each privilege allows and build functional examples demonstrating their use within a practical database context.

Instructions

Research: For each listed privilege, provide a brief explanation in your own words (maximum 2 lines) about what it allows and why it would be useful in a real system.
Application: Create a practical example in Markdown (.md) for each of the privileges. This example must include:
- A context table (real or simulated).
- A small SQL code block demonstrating the use of the privilege.
- A brief description of the objective of that code.

Data Privileges​

Structure Privileges​

Administration Privileges​