Revoking privileges in MySQL - Mini Project: Build & Apply

Revoking Privileges in MySQL

📖 Scenario: You are a database administrator managing user access in a company database. Some users have been given extra permissions by mistake. You need to remove specific privileges from a user to keep the database secure.

🎯 Goal: Learn how to revoke specific privileges from a MySQL user using the REVOKE statement.

📋 What You'll Learn

Create a MySQL user with specific privileges

Identify the privileges to revoke

Use the REVOKE statement to remove privileges

Verify the privileges have been revoked

💡 Why This Matters

🌍 Real World

Database administrators often need to adjust user permissions to maintain security and proper access control.

💼 Career

Knowing how to revoke privileges is essential for managing database security and compliance in professional environments.

1

Create a MySQL user with privileges

Write a MySQL statement to create a user called 'testuser'@'localhost' with password 'TestPass123' and grant the privileges SELECT and INSERT on the database companydb.

MySQL

-- Create user 'testuser'@'localhost' with password 'TestPass123' and grant SELECT, INSERT on companydb
-- Your code here

Hint

Use CREATE USER to make the user, then GRANT to give privileges.

2

Identify privileges to revoke

Create a variable called privileges_to_revoke and set it to the string 'INSERT' to specify which privilege you want to remove from 'testuser'@'localhost'.

MySQL

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'TestPass123';
GRANT SELECT, INSERT ON companydb.* TO 'testuser'@'localhost';
-- Define privileges to revoke
-- Your code here

Hint

Use a variable to hold the privilege name as a string.

3

Revoke the specified privilege

Write a MySQL REVOKE statement to remove the privilege stored in @privileges_to_revoke from 'testuser'@'localhost' on the companydb database.

MySQL

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'TestPass123';
GRANT SELECT, INSERT ON companydb.* TO 'testuser'@'localhost';
SET @privileges_to_revoke = 'INSERT';
-- Revoke the INSERT privilege from testuser
-- Your code here

Hint

Use REVOKE INSERT ON companydb.* FROM 'testuser'@'localhost' to remove the privilege.

4

Verify the privileges have been revoked

Write a MySQL query to show the privileges of 'testuser'@'localhost' to confirm that the INSERT privilege has been revoked.

MySQL

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'TestPass123';
GRANT SELECT, INSERT ON companydb.* TO 'testuser'@'localhost';
SET @privileges_to_revoke = 'INSERT';
REVOKE INSERT ON companydb.* FROM 'testuser'@'localhost';
-- Show privileges for testuser
-- Your code here

Hint

Use SHOW GRANTS FOR 'testuser'@'localhost' to see current privileges.

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