Revoking Privileges & FLUSH PRIVILEGES0%

Revoking Privileges & FLUSH PRIVILEGES

Intermediate11 min read•Updated: 2026-09-12

Revoking Privileges & FLUSH PRIVILEGES

Granting permissions is only half of access control; maintaining a secure infrastructure requires periodically stripping unnecessary permissions or revoking access when team roles change.

MySQL provides the REVOKE statement to remove permissions and the FLUSH PRIVILEGES statement to reload internal grant tables.

The REVOKE Statement Syntax

1REVOKE privilege_name [, ...] 
2ON database_name.table_name 
3FROM 'username'@'hostname';

Exact Match Rule: The ON scope specified in the REVOKE statement must match the exact scope level at which the privilege was originally granted. If granted at ecommerce.*, you cannot revoke it at *.*!

Practical Examples of Revoking Privileges

Example 1: Stripping DELETE Privileges

Prevent an application user from deleting records directly:

1REVOKE DELETE 
2ON ecommerce.* 
3FROM 'webapp'@'localhost';

Example 2: Revoking All Privileges Simultaneously

To completely remove all privileges from an account without dropping the user identity:

1-- Revoke all table-level and db-level rights
2REVOKE ALL PRIVILEGES, GRANT OPTION 
3FROM 'intern'@'%';

Demystifying FLUSH PRIVILEGES: When Is It Actually Needed?

There is widespread confusion among developers regarding when to run FLUSH PRIVILEGES;.

What FLUSH PRIVILEGES Does:

It instructs the MySQL server to re-read the grant tables (mysql.user, mysql.db, mysql.tables_priv, etc.) from disk into memory.

When You DO NOT Need It:

When you modify accounts using standard Account Management statements:

CREATE USER
ALTER USER
DROP USER
GRANT
REVOKE

MySQL automatically updates in-memory privilege caches immediately when executing these statements! Running FLUSH PRIVILEGES after GRANT or REVOKE is redundant.

When You DO Need It:

You must execute FLUSH PRIVILEGES if and only if you manually modify the underlying system tables using raw DML:

1-- Manual table edit (Not recommended!):
2UPDATE mysql.user SET max_connections = 50 WHERE user = 'app';
3 
4-- Must flush because MySQL was bypassed:
5FLUSH PRIVILEGES;

Auditing Privileges via Information Schema

To audit and review permissions across your cluster programmatically:

1-- Check database-level permissions
2SELECT * FROM information_schema.schema_privileges;
3 
4-- Check table-level permissions
5SELECT * FROM information_schema.table_privileges;
6 
7-- Check column-level permissions
8SELECT * FROM information_schema.column_privileges;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL statement removes previously assigned permissions from a MySQL user?

Question 2

Is FLUSH PRIVILEGES required immediately after executing a standard GRANT or REVOKE statement?

Question 3

When is executing FLUSH PRIVILEGES genuinely necessary?

Question 4

What happens if you try to execute REVOKE SELECT ON . from a user who was granted SELECT ON sales.*?

Question 5

Which statement strips all privileges and grant delegation from a user?

Next Lesson

Password Management & Account Locking

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Granting Privileges (GRANT Statement)	Password Management & Account Locking

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Password Management & Account Locking

Revoking Privileges & FLUSH PRIVILEGES

Intermediate11 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Revoking Privileges & FLUSH PRIVILEGES

Granting permissions is only half of access control; maintaining a secure infrastructure requires periodically stripping unnecessary permissions or revoking access when team roles change.

MySQL provides the REVOKE statement to remove permissions and the FLUSH PRIVILEGES statement to reload internal grant tables.

The REVOKE Statement Syntax

1REVOKE privilege_name [, ...] 
2ON database_name.table_name 
3FROM 'username'@'hostname';

Practical Examples of Revoking Privileges

Example 1: Stripping DELETE Privileges

Prevent an application user from deleting records directly:

1REVOKE DELETE 
2ON ecommerce.* 
3FROM 'webapp'@'localhost';

Example 2: Revoking All Privileges Simultaneously

To completely remove all privileges from an account without dropping the user identity:

1-- Revoke all table-level and db-level rights
2REVOKE ALL PRIVILEGES, GRANT OPTION 
3FROM 'intern'@'%';

Demystifying FLUSH PRIVILEGES: When Is It Actually Needed?

There is widespread confusion among developers regarding when to run FLUSH PRIVILEGES;.

What FLUSH PRIVILEGES Does:

It instructs the MySQL server to re-read the grant tables (mysql.user, mysql.db, mysql.tables_priv, etc.) from disk into memory.

When You DO NOT Need It:

When you modify accounts using standard Account Management statements:

CREATE USER
ALTER USER
DROP USER
GRANT
REVOKE

MySQL automatically updates in-memory privilege caches immediately when executing these statements! Running FLUSH PRIVILEGES after GRANT or REVOKE is redundant.

When You DO Need It:

You must execute FLUSH PRIVILEGES if and only if you manually modify the underlying system tables using raw DML:

1-- Manual table edit (Not recommended!):
2UPDATE mysql.user SET max_connections = 50 WHERE user = 'app';
3 
4-- Must flush because MySQL was bypassed:
5FLUSH PRIVILEGES;

Auditing Privileges via Information Schema

To audit and review permissions across your cluster programmatically:

1-- Check database-level permissions
2SELECT * FROM information_schema.schema_privileges;
3 
4-- Check table-level permissions
5SELECT * FROM information_schema.table_privileges;
6 
7-- Check column-level permissions
8SELECT * FROM information_schema.column_privileges;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL statement removes previously assigned permissions from a MySQL user?

Question 2

Is FLUSH PRIVILEGES required immediately after executing a standard GRANT or REVOKE statement?

Question 3

When is executing FLUSH PRIVILEGES genuinely necessary?

Question 4

What happens if you try to execute REVOKE SELECT ON . from a user who was granted SELECT ON sales.*?

Question 5

Which statement strips all privileges and grant delegation from a user?

Next Lesson

Password Management & Account Locking

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Granting Privileges (GRANT Statement)	Password Management & Account Locking

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next