Why access control protects data in MySQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why is access control important in databases?

Which of the following best explains why access control is essential for protecting data in a database?

A It restricts who can view or modify data, preventing unauthorized access.

B It automatically backs up data to prevent loss.

C It speeds up query execution by limiting data size.

D It encrypts data so users cannot read it.

💡 Hint

Think about how controlling user permissions helps keep data safe.

Access control limits which users can see or change data, protecting it from unauthorized use or damage.

❓ query_result

intermediate

Result of a query with restricted access

Given a user with SELECT permission only on the employees table, what will happen if they try to run this query?

DELETE FROM employees WHERE id = 5;

A The row with id 5 will be deleted.

B The query will fail with a permission error.

C The database will prompt for admin approval.

D The query will run but no rows will be deleted.

💡 Hint

Consider what permissions are needed to delete data.

Without DELETE permission, the database denies the operation to protect data integrity.

📝 Syntax

advanced

Granting access with SQL

Which of the following SQL statements correctly grants SELECT permission on the customers table to user john?

A GRANT SELECT ON customers TO 'john'@'localhost';

B GRANT SELECT ON customers.* TO john;

C GRANT SELECT ON database.customers TO john;

D GRANT SELECT ON customers TO john;

💡 Hint

Remember how to specify user names in MySQL GRANT statements.

In MySQL, user names must be quoted as strings with host in GRANT statements.

❓ optimization

advanced

Optimizing access control for many users

You have hundreds of users needing different access levels to various tables. Which approach best optimizes managing access control?

A Grant permissions individually to each user for each table.

B Use a single admin user for all access to simplify control.

C Create roles with specific permissions and assign users to roles.

D Store permissions in a separate table and check manually in queries.

💡 Hint

Think about how grouping permissions can reduce repetitive work.

Roles let you assign permissions once and then assign users to roles, making management easier and less error-prone.

🔧 Debug

expert

Why does this access control fail?

A database admin runs this command to revoke SELECT permission from user alice on the orders table:

REVOKE SELECT ON orders FROM alice;

But alice can still select data from orders. What is the most likely reason?

A Alice is connected with a different username.

B The REVOKE command syntax is incorrect and did not run.

C The orders table does not exist, so the command had no effect.

D Alice has SELECT permission through a role that was not revoked.

💡 Hint

Consider how permissions can be granted indirectly.

Permissions granted via roles remain active unless revoked from the role or user removed from the role.

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