MENU


Locking and Dropping

Account Locking, Renaming, and Dropping

After ana's third consecutive wrong password the server blocks her for a day, and even the right one fails. ACCOUNT UNLOCK lifts the block; ACCOUNT LOCK shuts an account on purpose:

Failed logins block an account; ACCOUNT LOCK shuts itSQL
try() { mysql -uana -p"$1" -Nse "SELECT CURRENT_USER()" 2>&1 | grep -v Warning; }
try wrong1; try wrong2; try wrong3; try 'Ana#Pass-2026'
sudo mysql -e "ALTER USER 'ana'@'localhost' ACCOUNT UNLOCK"; try 'Ana#Pass-2026'
sudo mysql -e "ALTER USER 'ana'@'localhost' ACCOUNT LOCK"; try 'Ana#Pass-2026'
Output
ERROR 1045 (28000): Access denied for user 'ana'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user 'ana'@'localhost' (using password: YES)
ERROR 3955 (HY000): Access denied for user 'ana'@'localhost'. Account is blocked for 1 day(s)
  (1 day(s) remaining) due to 3 consecutive failed logins.
ERROR 3955 (HY000): Access denied for user 'ana'@'localhost'. Account is blocked for 1 day(s)
  (1 day(s) remaining) due to 3 consecutive failed logins.
ana@localhost
ERROR 3118 (HY000): Access denied for user 'ana'@'localhost'. Account is locked.

A lock stays in mysql.user across restarts; open sessions survive it (FLUSH, KILL and CLONE). RENAME USER moves every grant and DROP USER removes them, but neither rewrites the DEFINER of views, routines, triggers or events; MySQL 524 only raises warning 4005:

Renaming an account orphans the view it definedSQL
ALTER USER 'ana'@'localhost' ACCOUNT UNLOCK;
GRANT SELECT ON shop.customers TO 'ana'@'localhost';
CREATE DEFINER = 'ana'@'localhost' VIEW customer_names AS SELECT id, name FROM customers;
RENAME USER 'ana'@'localhost' TO 'ana.souza'@'localhost';
SELECT * FROM customer_names LIMIT 1;
DROP VIEW customer_names;
DROP USER 'ana.souza'@'localhost';
Output
ERROR 1449 (HY000) at line 5: The user specified as a definer ('ana'@'localhost') does not
  exist

Before renaming or dropping a person, find their objects in information_schema (VIEWS, ROUTINES, TRIGGERS, EVENTS by DEFINER) and move them to a service account.