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.

Practice

1. What does the GRANT SELECT ON database.* TO 'user'@'host'; command do in MySQL?

easy

A. Allows the user to read data from all tables in the specified database.

B. Allows the user to modify data in all tables in the specified database.

C. Creates a new user with read access to the database.

D. Deletes all data from the specified database.

Solution

1. Step 1: Understand the GRANT command

   The GRANT command is used to give specific permissions to a user on a database or table.

2. Step 2: Analyze the SELECT privilege

   SELECT privilege allows reading data but not modifying it.

3. Final Answer:

   Allows the user to read data from all tables in the specified database. -> Option A

4. Quick Check:

   GRANT SELECT = read permission [OK]

Hint: SELECT privilege means read-only access [OK]

Common Mistakes:

• Confusing SELECT with UPDATE or DELETE privileges
• Thinking GRANT creates a user
• Assuming it deletes data

2. Which of the following is the correct syntax to grant all privileges on the shop database to user 'alice' connecting from any host?

easy

A. GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%';

B. GRANT ALL PRIVILEGES ON shop TO 'alice'@'%';

C. GRANT ALL ON shop.* TO 'alice';

D. GRANT ALL PRIVILEGES TO 'alice'@'%' ON shop.*;

Solution

1. Step 1: Check the database and user format

   The database must be followed by .* to specify all tables, and user must include host.

2. Step 2: Verify the correct order of clauses

   The syntax is: GRANT privileges ON database.* TO 'user'@'host';

3. Final Answer:

   GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%'; -> Option A

4. Quick Check:

   Correct syntax = GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%'; [OK]

Hint: Use database.* to grant on all tables [OK]

Common Mistakes:

• Omitting .* after database name
• Placing TO clause incorrectly
• Not specifying host with user

3. Given the commands:

GRANT SELECT, INSERT ON sales.* TO 'bob'@'localhost';
SHOW GRANTS FOR 'bob'@'localhost';

What will the SHOW GRANTS output include?

medium

A. GRANT ALL PRIVILEGES ON sales.* TO 'bob'@'localhost'

B. GRANT SELECT ON sales.* TO 'bob'@'localhost'

C. GRANT SELECT, INSERT ON sales.* TO 'bob'@'localhost'

D. No privileges granted

Solution

1. Step 1: Understand the GRANT command given

   The user 'bob' is granted SELECT and INSERT privileges on all tables in the sales database.

2. Step 2: Interpret SHOW GRANTS output

   SHOW GRANTS displays the exact privileges granted, so it will list SELECT and INSERT on sales.* for bob.

3. Final Answer:

   GRANT SELECT, INSERT ON sales.* TO 'bob'@'localhost' -> Option C

4. Quick Check:

   SHOW GRANTS matches granted privileges [OK]

Hint: SHOW GRANTS shows exact privileges granted [OK]

Common Mistakes:

• Assuming ALL PRIVILEGES are granted
• Ignoring INSERT privilege
• Thinking SHOW GRANTS returns no rows

4. Identify the error in the following command:

GRANT UPDATE, DELETE ON * TO 'carol'@'localhost';

medium

A. Host '%' must be used instead of 'localhost'

B. User name must be in double quotes

C. Privileges UPDATE and DELETE cannot be granted together

D. Missing database or table name after ON

Solution

1. Step 1: Check the ON clause syntax

   The ON clause requires a database or table name, e.g., database.* or database.table.

2. Step 2: Identify the missing part

   The command uses * alone, which is invalid syntax in MySQL for ON.

3. Final Answer:

   Missing database or table name after ON -> Option D

4. Quick Check:

   ON must specify database or table [OK]

Hint: ON must specify database or table, not just * [OK]

Common Mistakes:

• Using * alone without database name
• Using wrong quotes for user
• Assuming localhost is invalid host

5. You want to allow user 'dave' to create new tables only in the inventory database but not modify data. Which GRANT command is correct?

hard

A. GRANT ALL PRIVILEGES ON inventory.* TO 'dave'@'localhost';

B. GRANT CREATE ON inventory.* TO 'dave'@'localhost';

C. GRANT CREATE, INSERT ON inventory.* TO 'dave'@'localhost';

D. GRANT CREATE ON *.* TO 'dave'@'localhost';

Solution

1. Step 1: Identify the needed privilege

   To create tables, the CREATE privilege is required; INSERT or ALL PRIVILEGES would allow data changes.

2. Step 2: Scope the privilege to the inventory database

   Granting on inventory.* limits the permission to that database only.

3. Step 3: Check user and host format

   User 'dave'@'localhost' is correctly specified.

4. Final Answer:

   GRANT CREATE ON inventory.* TO 'dave'@'localhost'; -> Option B

5. Quick Check:

   CREATE only on inventory = GRANT CREATE ON inventory.* TO 'dave'@'localhost'; [OK]

Hint: Grant only CREATE on specific database for table creation [OK]

Common Mistakes:

• Granting ALL PRIVILEGES instead of just CREATE
• Including INSERT which allows data changes
• Granting on *.* instead of specific database