Revoking privileges in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to revoke SELECT privilege from user 'alice' on database 'shop'.

MySQL

REVOKE [1] ON shop.* FROM 'alice'@'localhost';

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

ASELECT

BINSERT

CUPDATE

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT instead of SELECT will revoke the wrong privilege.

Forgetting to specify the correct database and tables.

The REVOKE statement removes privileges. Here, we remove the SELECT privilege from user 'alice' on all tables in the 'shop' database.

2fill in blank

medium

Complete the code to revoke ALL privileges from user 'bob' on the database 'inventory'.

MySQL

REVOKE [1] ON inventory.* FROM 'bob'@'%';

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

ADELETE

BSELECT

CALL PRIVILEGES

DUPDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using only SELECT or UPDATE revokes just one privilege, not all.

Forgetting to specify the correct user host.

Using ALL PRIVILEGES revokes every privilege the user has on the specified database.

3fill in blank

hard

Fix the error in the code to revoke INSERT privilege from user 'carol' on all databases.

MySQL

REVOKE [1] ON *.* FROM 'carol'@'localhost';

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

AINSERTS

BINSERT

CINSRT

DINSET

Attempts:

3 left

💡 Hint

Common Mistakes

Misspelling privilege names like INSERTS or INSRT.

Using plural forms which are invalid.

The correct privilege name is INSERT. Misspelled privilege names cause syntax errors.

4fill in blank

hard

Fill both blanks to revoke UPDATE privilege from user 'dave' on the 'sales' database.

MySQL

REVOKE [1] ON [2] FROM 'dave'@'192.168.1.10';

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

AUPDATE

Bsales.*

Csales

D*.*

Attempts:

3 left

💡 Hint

Common Mistakes

Using just the database name without .* to specify tables.

Choosing the wrong privilege to revoke.

The UPDATE privilege is revoked on all tables in the sales database, which is specified as sales.*.

5fill in blank

hard

Fill all three blanks to revoke DELETE privilege from user 'eve' on the 'marketing' database.

MySQL

REVOKE [1] ON [2] FROM '[3]'@'localhost';

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

ADELETE

Bmarketing.*

Ceve

Dadmin

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong privilege names or database names.

Mixing up usernames or hostnames.

This revokes the DELETE privilege on all tables in the marketing database from user eve at localhost.

Practice

1. What does the REVOKE statement do in MySQL?

easy

A. It removes specific privileges from a user.

B. It grants new privileges to a user.

C. It creates a new user in the database.

D. It deletes a database table.

Solution

1. Step 1: Understand the purpose of REVOKE

   The REVOKE statement is used to remove privileges that were previously granted to a user.

2. Step 2: Compare with other statements

   GRANT adds privileges, CREATE USER creates users, and DROP TABLE deletes tables, so they do not remove privileges.

3. Final Answer:

   It removes specific privileges from a user. -> Option A

4. Quick Check:

   REVOKE = Remove privileges [OK]

Hint: REVOKE always removes privileges, not adds [OK]

Common Mistakes:

• Confusing REVOKE with GRANT
• Thinking REVOKE creates users
• Assuming REVOKE deletes tables

2. Which of the following is the correct syntax to revoke SELECT privilege on the employees table from user 'john'@'localhost'?

easy

A. REVOKE SELECT ON database.employees FROM 'john'@'localhost';

B. REVOKE SELECT ON database.employees.* FROM 'john'@'localhost';

C. REVOKE SELECT ON employees.* FROM 'john'@'localhost';

D. REVOKE SELECT ON employees FROM 'john'@'localhost';

Solution

1. Step 1: Understand the syntax for REVOKE on a table

   The correct syntax requires specifying the database and table as database.table.

2. Step 2: Analyze each option

   REVOKE SELECT ON database.employees FROM 'john'@'localhost'; correctly uses database.employees format. Options A and C miss the database name, and B incorrectly adds .* after table name.

3. Final Answer:

   REVOKE SELECT ON database.employees FROM 'john'@'localhost'; -> Option A

4. Quick Check:

   REVOKE syntax needs database.table [OK]

Hint: Always specify database.table in REVOKE [OK]

Common Mistakes:

• Omitting database name
• Using .* after table name incorrectly
• Wrong user specification format

3. Given the following commands executed in MySQL:

GRANT SELECT, INSERT ON sales.orders TO 'anna'@'localhost';
REVOKE INSERT ON sales.orders FROM 'anna'@'localhost';
SHOW GRANTS FOR 'anna'@'localhost';

What privileges will 'anna'@'localhost' have on sales.orders after these commands?

medium

A. No privileges

B. SELECT and INSERT privileges

C. Only SELECT privilege

D. Only INSERT privilege

Solution

1. Step 1: Analyze the GRANT statement

   The user 'anna' is granted SELECT and INSERT privileges on sales.orders.

2. Step 2: Analyze the REVOKE statement

   The INSERT privilege is revoked from 'anna' on sales.orders, leaving only SELECT.

3. Final Answer:

   Only SELECT privilege -> Option C

4. Quick Check:

   REVOKE removes INSERT, SELECT remains [OK]

Hint: REVOKE removes specified privileges only [OK]

Common Mistakes:

• Assuming REVOKE removes all privileges
• Confusing which privilege was revoked
• Ignoring SHOW GRANTS output

4. Identify the error in this REVOKE statement:

REVOKE UPDATE ON mydb.* FROM 'mike'@'%';

medium

A. The user specification is incorrect.

B. The privilege name UPDATE is invalid.

C. The ON clause should specify a table, not a database.

D. The syntax is correct; no error.

Solution

1. Step 1: Check user specification

   'mike'@'%' is a valid user format allowing any host.

2. Step 2: Validate privilege and ON clause

   UPDATE is a valid privilege, and ON mydb.* means all tables in database mydb, which is allowed.

3. Final Answer:

   The syntax is correct; no error. -> Option D

4. Quick Check:

   REVOKE syntax with database.* is valid [OK]

Hint: ON database.* is valid for all tables [OK]

Common Mistakes:

• Thinking ON must specify a single table
• Confusing user host wildcard syntax
• Assuming UPDATE is invalid privilege

5. You want to revoke all privileges from user 'lisa'@'localhost' on the finance database but keep her SELECT privilege on the finance.reports table. Which sequence of commands achieves this?

hard

A. REVOKE SELECT ON finance.reports FROM 'lisa'@'localhost';REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost';

B. REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost';GRANT SELECT ON finance.reports TO 'lisa'@'localhost';

C. REVOKE ALL PRIVILEGES ON finance.reports FROM 'lisa'@'localhost';GRANT SELECT ON finance.* TO 'lisa'@'localhost';

D. REVOKE ALL PRIVILEGES ON finance.reports FROM 'lisa'@'localhost';REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost';

Solution

1. Step 1: Revoke all privileges on the entire finance database

   Use REVOKE ALL PRIVILEGES ON finance.* to remove all rights on all tables in finance.

2. Step 2: Grant back SELECT privilege on finance.reports table

   Use GRANT SELECT ON finance.reports to restore only SELECT on that specific table.

3. Final Answer:

   REVOKE ALL PRIVILEGES ON finance.* FROM 'lisa'@'localhost'; GRANT SELECT ON finance.reports TO 'lisa'@'localhost'; -> Option B

4. Quick Check:

   Revoke all then grant SELECT on reports [OK]

Hint: Revoke all then grant needed privilege back [OK]

Common Mistakes:

• Revoking SELECT first removes needed privilege
• Granting SELECT on whole database instead of table
• Revoking privileges in wrong order