Granting privileges in MySQL - Step-by-Step Execution

Concept Flow - Granting privileges

Start

↓

Identify User

↓

Choose Privileges

↓

Specify Database/Table

↓

Execute GRANT Command

↓

Privileges Updated

↓

User Can Perform Actions

↓

End

The flow shows how to grant privileges to a user step-by-step: identify the user, select privileges, specify scope, execute the command, and update access.

Execution Sample

MySQL

GRANT SELECT, INSERT ON mydb.* TO 'alice'@'localhost';

This command grants SELECT and INSERT privileges on all tables in 'mydb' to user 'alice' connecting from localhost.

Execution Table

Step	Action	Command Part	Effect	Result
1	Identify User	'alice'@'localhost'	Target user for privileges	User identified
2	Choose Privileges	SELECT, INSERT	Privileges to grant	Privileges selected
3	Specify Scope	ON mydb.*	Apply privileges to all tables in 'mydb'	Scope set
4	Execute GRANT	GRANT SELECT, INSERT ON mydb.* TO 'alice'@'localhost';	Privileges assigned in MySQL	Privileges granted
5	Verify	SHOW GRANTS FOR 'alice'@'localhost';	Check assigned privileges	Privileges confirmed
6	End		Process complete	User can now perform SELECT and INSERT on mydb tables

💡 All steps completed, privileges successfully granted to user 'alice'@'localhost'

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
User	None	'alice'@'localhost'	'alice'@'localhost'	'alice'@'localhost'	'alice'@'localhost'	'alice'@'localhost'
Privileges	None	None	SELECT, INSERT	SELECT, INSERT	SELECT, INSERT	SELECT, INSERT
Scope	None	None	None	mydb.*	mydb.*	mydb.*
Privileges Granted	No	No	No	No	Yes	Yes

Key Moments - 3 Insights

Why do we specify the user as 'alice'@'localhost' instead of just 'alice'?

MySQL identifies users by both username and host. Specifying 'alice'@'localhost' means privileges apply only when 'alice' connects from the local machine. This is shown in execution_table step 1.

What happens if we omit the database or table after ON?

If you omit the database or table, MySQL will not know where to apply privileges. You must specify a database and optionally tables, like 'mydb.*' for all tables in 'mydb' (execution_table step 3).

Does the GRANT command immediately allow the user to perform actions?

Yes, once the GRANT command executes successfully (step 4), the privileges are active and the user can perform the allowed actions (step 6).

Visual Quiz - 3 Questions

Look at the execution_table, at which step are the privileges actually assigned in MySQL?

A Step 4

B Step 5

C Step 2

D Step 3

💡 Hint

Check the 'Effect' column for when privileges are assigned.

According to variable_tracker, what is the value of 'Scope' after step 3?

A mydb.*

B 'alice'@'localhost'

C None

D SELECT, INSERT

💡 Hint

Look at the 'Scope' row under 'After Step 3' column.

If we change the user to 'bob'@'%', which step in execution_table changes?

A Step 2

B Step 1

C Step 4

D Step 5

💡 Hint

User identification is handled in step 1.

Concept Snapshot

GRANT privileges ON database.table TO 'user'@'host';
- Specify user with username and host.
- List privileges like SELECT, INSERT.
- Define scope: database and tables.
- Execute to assign privileges immediately.
- Verify with SHOW GRANTS FOR 'user'@'host';

Full Transcript

Granting privileges in MySQL involves specifying the user with username and host, choosing which privileges to grant, defining the scope of those privileges on databases or tables, and executing the GRANT command. The process starts by identifying the user, selecting privileges, setting the scope, and then running the command to update the user's permissions. After granting, the user can perform the allowed actions immediately. Verification can be done using SHOW GRANTS. This step-by-step flow ensures precise control over database access.