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.