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

Need a 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

Need a 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

Need a 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

Need a hint?

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