Why access control protects data in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to grant SELECT permission on the employees table to user 'john'.

MySQL

GRANT [1] ON employees TO 'john';

Drag options to blanks, or click blank then click option'

AUPDATE

BDELETE

CSELECT

DINSERT

Attempts:

3 left

💡 Hint

Common Mistakes

Using DELETE instead of SELECT will allow data removal, not reading.

UPDATE and INSERT are for modifying data, not just reading.

The SELECT privilege allows the user to read data from the table, which is essential for controlled data access.

2fill in blank

medium

Complete the code to revoke UPDATE permission from user 'alice' on the sales table.

MySQL

REVOKE [1] ON sales FROM 'alice';

Drag options to blanks, or click blank then click option'

ASELECT

BUPDATE

CINSERT

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Revoking SELECT will prevent reading data, not updating.

INSERT and DELETE are for adding or removing data, not updating.

UPDATE permission allows modifying existing data. Revoking it prevents unauthorized changes.

3fill in blank

hard

Fix the error in the code to grant all privileges on the database 'company' to user 'bob'.

MySQL

GRANT [1] ON company.* TO 'bob';

Drag options to blanks, or click blank then click option'

AALL

BEVERY

CALL PRIVILEGE

DALL PRIVILEGES

Attempts:

3 left

💡 Hint

Common Mistakes

Using just 'ALL' is incomplete syntax.

Misspelling 'PRIVILEGES' causes errors.

Using 'EVERY' is not valid SQL.

The correct syntax to grant all privileges is 'ALL PRIVILEGES'.

4fill in blank

hard

Fill both blanks to create a user 'carol' with password 'secure123' and grant SELECT permission on the products table.

MySQL

CREATE USER 'carol'@'localhost' IDENTIFIED BY '[1]';
GRANT [2] ON products TO 'carol'@'localhost';

Drag options to blanks, or click blank then click option'

Asecure123

BSELECT

CDELETE

Dpassword123

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong password string.

Granting DELETE instead of SELECT.

The password must match the user's password, and SELECT permission allows reading the products table.

5fill in blank

hard

Fill all three blanks to revoke INSERT and UPDATE permissions from user 'dave' on the orders table and then drop the user.

MySQL

REVOKE [1], [2] ON orders FROM 'dave';
DROP USER '[3]';

Drag options to blanks, or click blank then click option'

AINSERT

BUPDATE

Cdave

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DELETE instead of UPDATE or INSERT.

Dropping a user with the wrong name.

We revoke INSERT and UPDATE permissions, then drop the user named 'dave'.

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