Why access control protects data in MySQL - Visual Breakdown

Concept Flow - Why access control protects data

User tries to access data

↓

Check user identity

↓

Verify user permissions

↓

Allow access

Access control checks who the user is and what they are allowed to do before letting them see or change data.

Execution Sample

MySQL

GRANT SELECT ON employees TO 'user1';
-- user1 tries to SELECT data
SELECT * FROM employees WHERE id=1;

User1 is given permission to read data from employees table, then tries to read data.

Execution Table

Step	Action	User	Permission Check	Result	Output
1	Grant SELECT on employees	admin	N/A	Permission granted to user1	N/A
2	User1 attempts SELECT * FROM employees WHERE id=1	user1	Has SELECT permission?	Yes	Returns employee data with id=1
3	User2 attempts SELECT * FROM employees WHERE id=1	user2	Has SELECT permission?	No	Access denied error

💡 User without permission is blocked from accessing data

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3
user1_permission	None	SELECT on employees	SELECT on employees	SELECT on employees
user2_permission	None	None	None	None
query_result_user1	None	None	Employee data row	Employee data row
query_result_user2	None	None	None	Access denied

Key Moments - 2 Insights

Why does user2 get an access denied error even though they run the same SELECT query as user1?

Because user2 does not have the SELECT permission granted (see execution_table row 3), so access control blocks their query.

What happens if a user has permission to SELECT but tries to UPDATE data?

Access control checks the requested action against permissions. If UPDATE is not granted, the action is denied even if SELECT is allowed.

Visual Quiz - 3 Questions

Look at the execution_table, what permission does user1 have after step 1?

A UPDATE on employees

B No permissions

C SELECT on employees

D ALL privileges

💡 Hint

Check the 'Permission Check' column in row 1 of execution_table

At which step does user2 get blocked from accessing data?

A Step 3

B Step 2

C Step 1

D User2 is never blocked

💡 Hint

Look at the 'Result' and 'Output' columns for user2 in execution_table

If user1 was not granted any permission, what would happen at step 2?

A User1 would get employee data

B User1 would get access denied error

C User1 could update data

D Query would run without checks

💡 Hint

Refer to how access control blocks users without permission in execution_table row 3

Concept Snapshot

Access control checks user identity and permissions before allowing data access.
Permissions like SELECT, UPDATE control what actions users can do.
Without proper permission, users get access denied errors.
This protects data from unauthorized viewing or changes.
Grant permissions explicitly to users to allow safe access.

Full Transcript

Access control protects data by checking who the user is and what permissions they have before allowing access. For example, if user1 is granted SELECT permission on the employees table, they can read data. If user2 has no permissions, their query is blocked with an access denied error. This ensures only authorized users can view or modify data, keeping it safe from unauthorized access or changes.

Practice

1. Why is access control important in a MySQL database?

easy

A. It limits who can view or change data, protecting it from unauthorized access.

B. It speeds up query execution by caching results.

C. It automatically backs up the database daily.

D. It formats the data for better display on websites.

Solution

1. Step 1: Understand the purpose of access control

   Access control restricts database access to authorized users only.

2. Step 2: Identify the benefit of restricting access

   By limiting who can view or modify data, it protects data from unauthorized changes or leaks.

3. Final Answer:

   It limits who can view or change data, protecting it from unauthorized access. -> Option A

4. Quick Check:

   Access control = Protects data [OK]

Hint: Access control means who can do what with data [OK]

Common Mistakes:

• Confusing access control with performance features
• Thinking access control handles backups
• Believing access control formats data display

2. Which MySQL command is used to give a user permission to access a database?

easy

A. SELECT

B. REVOKE

C. GRANT

D. DELETE

Solution

1. Step 1: Recall MySQL commands for permissions

   GRANT is used to give permissions; REVOKE removes them.

2. Step 2: Identify the command that grants access

   GRANT assigns privileges to users to access or modify data.

3. Final Answer:

   GRANT -> Option C

4. Quick Check:

   GRANT = Give permission [OK]

Hint: GRANT gives access, REVOKE removes it [OK]

Common Mistakes:

• Confusing GRANT with REVOKE
• Choosing data manipulation commands like SELECT or DELETE
• Thinking SELECT grants permissions

3. Given the commands:
GRANT SELECT ON employees TO 'user1'@'localhost';
REVOKE INSERT ON employees FROM 'user1'@'localhost';
What can 'user1' do on the employees table?

medium

A. No permissions at all

B. Only select (read) data from employees

C. Both select and insert data

D. Insert new data but cannot read data

Solution

1. Step 1: Analyze the GRANT command

   GRANT SELECT allows user1 to read data from employees table.

2. Step 2: Analyze the REVOKE command

   REVOKE INSERT removes insert permission, so user1 cannot add data.

3. Final Answer:

   Only select (read) data from employees -> Option B

4. Quick Check:

   GRANT SELECT + REVOKE INSERT = Read only [OK]

Hint: GRANT adds, REVOKE removes permissions [OK]

Common Mistakes:

• Assuming REVOKE removes all permissions
• Thinking user1 can insert data
• Confusing SELECT with INSERT permission

4. A database admin runs:
GRANT SELECT ON sales TO 'user2'@'%';
REVOKE SELECT ON sales FROM 'user2'@'%';
But user2 can still read the sales table. What is the likely problem?

medium

A. User2 has SELECT permission from another host or user entry.

B. The REVOKE command syntax is incorrect.

C. MySQL does not support REVOKE for SELECT.

D. User2 needs to restart the database to update permissions.

Solution

1. Step 1: Understand permission scope in MySQL

   Permissions are granted per user and host combination.

2. Step 2: Identify why REVOKE may not work as expected

   If user2 has another entry with SELECT permission from a different host, that permission remains.

3. Final Answer:

   User2 has SELECT permission from another host or user entry. -> Option A

4. Quick Check:

   Multiple user entries can override REVOKE [OK]

Hint: Check all user@host entries for permissions [OK]

Common Mistakes:

• Assuming REVOKE syntax is wrong without checking user entries
• Believing REVOKE is unsupported for SELECT
• Thinking a database restart is needed for permission changes

5. You want to ensure only the HR team can update employee salaries in MySQL. Which approach best protects this sensitive data?

hard

A. Grant all permissions to everyone and rely on application checks.

B. Allow all users to SELECT and UPDATE salaries for transparency.

C. Do not use any access control and trust users to behave.

D. Grant UPDATE permission on salaries table only to HR users, revoke from others.

Solution

1. Step 1: Identify the need for restricted update access

   Only HR should modify salaries to protect sensitive data.

2. Step 2: Apply access control best practice

   Grant UPDATE permission only to HR users and revoke from others to enforce this.

3. Final Answer:

   Grant UPDATE permission on salaries table only to HR users, revoke from others. -> Option D

4. Quick Check:

   Restrict update rights to protect sensitive data [OK]

Hint: Limit update rights to trusted users only [OK]

Common Mistakes:

• Giving update rights to all users
• Relying only on application-level checks
• Ignoring access control for sensitive tables