Revoking privileges in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the REVOKE command do in MySQL?

The REVOKE command removes specific privileges from a user, stopping them from performing certain actions on the database.

beginner

Syntax of REVOKE command to remove SELECT privilege on a database from a user.

REVOKE SELECT ON database_name.* FROM 'username'@'host';

intermediate

Can you revoke multiple privileges at once in MySQL? How?

Yes, you can revoke multiple privileges by listing them separated by commas, for example: REVOKE SELECT, INSERT ON db.* FROM 'user'@'host';

beginner

What happens if you revoke all privileges from a user?

The user loses all the specified permissions and cannot perform any actions on the database objects for which privileges were revoked.

intermediate

Why is it important to revoke privileges carefully?

Revoking privileges carefully ensures users only have access they need, improving security and preventing accidental or malicious data changes.

Which command removes privileges from a MySQL user?

A REVOKE

B GRANT

C DELETE

D DROP

REVOKE is used to remove privileges; GRANT is used to give privileges.

How do you revoke the INSERT privilege on all tables in a database named 'shop' from user 'alice'?

A GRANT INSERT ON shop.* TO 'alice'@'localhost';

B REVOKE INSERT ON shop.* FROM 'alice'@'localhost';

C REVOKE INSERT ON shop FROM 'alice';

D DROP USER 'alice'@'localhost';

Option B correctly revokes INSERT privilege on all tables in 'shop' from user 'alice'.

Can you revoke privileges from a user that does not have them?

A Yes, it will silently succeed.

B No, it causes an error.

C Yes, but only for SELECT privilege.

D No, you must grant first.

Revoking privileges the user does not have does not cause an error; it just has no effect.

Which of these is NOT a valid privilege to revoke?

A UPDATE

B INSERT

C CREATE USER

D SELECT

CREATE USER is an administrative privilege, not revoked with REVOKE on database objects.

What is the effect of revoking ALL PRIVILEGES from a user?

A User is deleted.

B User can still read data.

C User can still write data.

D User loses all database permissions.

Revoking ALL PRIVILEGES removes all permissions, so the user cannot read or write data.

Explain how to revoke the SELECT and UPDATE privileges from a user on a specific database in MySQL.

Think about how to list privileges separated by commas and specify the user with host.

Why is it important to manage user privileges carefully using REVOKE in a database system?

Consider what could happen if users have more access than needed.

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