Bug #9736 DISABLE USER

• Submitted: 7 Apr 2005 22:25
• Modified: 15 Dec 2010 9:57
• Reporter: Carsten Pedersen
• Status: Verified
• Category: MySQL Server: General
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[7 Apr 2005 22:25] Carsten Pedersen

Description:
During certain adminstrative actions, it makes sense to temporarily block access to one or more user accounts.

A command such as DISABLE USER could set a Disabled field in the mysql.user table to be checked both at connect and when executing a query.

A similar command ENABLE USER would reset that flag.

This would allow for a user to be disabled temporarily, without touching any of the other GRANT settings. 

How to repeat:
This is a feature request.

[7 Apr 2005 22:28] Carsten Pedersen

Feature request, not a bug

[24 Nov 2008 9:32] Hans Ginzel

I vote for this feature.

[19 Mar 2009 15:00] Carlos Hernandez

what about this feature?

[20 Apr 2009 9:40] Andy Pieters

I vote for this feature too!

[8 Dec 2010 5:08] prasad chowdary

Yes, it's more useful to have Enable/Disable user a/c's

-Guru Nutheti

[16 May 2012 6:20] Dan Kloke

Here's a workaround I use:
- create a separate table for disabled users:
create table mysql.users_off like mysql.users;
- copy selected accounts to a separate tables:
replace into table mysql.users_off select * from mysql.users where user like ('Bob',Steve');
- drop the regular account records:
delete from mysql.users where user like ('Bob',Steve');
flush privileges;
- restore when needed:
insert ignore into mysql.users select * from mysql.users_off where user like ('Bob',Steve');
flush privileges;
delete from mysql.users_off where user like ('Bob',Steve');

Use additional tables if you need to save/delete/restore table privileges (mysql.tables_priv), column privileges (mysql.columns_priv), and procedure privileges (procs_pirv), although my experience on 5.1 has been that these records will not be removed if the user table records are dropped manually.

The remove and restore sequences can be placed into stored procedures for convenient use.