Best practices for temporarily enabling and disabling a MySQL user account?

Question

I have a MySQL user account that is used (only) as part of the deployment process to make changes to the database (add/drop tables and columns, etc). Because this user account has these high privileges, I want to keep it disabled most of the time, and only enable it when we are actually doing a deployment that involves database changes. What would be the best way to do this? Something in a couple stored procedures (proc_enable, proc_disable or similar) would be perfectly fine but I couldn't seem to find any best practices around this and MySQL doesn't seem to have an easy enable/disable toggle.

Aubrey Lavigne · Answer 1 · 2021-06-14T15:28:11.963

17

For MySQL versions 5.7.6 and later (and MariaDB 10.4.2 and later), user accounts can be locked and unlocked with the following commands:

ALTER USER 'user_name'@'host' ACCOUNT LOCK;
ALTER USER 'user_name'@'host' ACCOUNT UNLOCK;

When the account is locked, attempting to log in will result with the message:

Access denied for user 'user_name'@'host'.
Account is locked.

Sources:

edited Jun 14 '21 at 15:28

answered Sep 03 '20 at 16:52

Aubrey Lavigne

542
6
12

2

Works on MaxScale + MariaDB 10.5 – sparse Jun 11 '21 at 07:34
@sparse I've updated the answer to include version information for MariaDB. They were a bit delayed implementing this feature after it was implemented in MySQL, so the explicit version should be helpful as well – Aubrey Lavigne Jun 14 '21 at 15:31
1

This doesn't lock the account completely. If I enter, ALTER USER 'user01'@'localhost', they can still log on remotely using the host's IP. "ALTER USER 'user01' ACCOUNT LOCK;" completely locks the account. – Benjamin Hastings Nov 23 '22 at 00:20

score 7 · Accepted Answer · answered May 07 '14 at 00:52

7

After experimenting with various methods, I went with setting the user's host field to something meaningless, then back to a valid value to re-enable the account when desired. This method is easily done through an admin tool or the mysql command prompt from an account with appropriate privileges, and doesn't require saving the password hash for later restore.

answered May 07 '14 at 00:52

Peter

29,498
21
89
122

Does that preserves privileges attached to the user? – dolmen Oct 11 '21 at 09:27
@dolmen I tested this on MySQL 8.0.25. Created a user, added SELECT on *.* and INSERT on db.*, then flushed privileges. Then I changed the host to another value, then flushed privileges, and the INSERT was gone but the SELECT was still there (this is strange to me why one would stay attached to the user but the other didn't). Did the same thing in reverse, and both INSERT and SELECT privileges were attached. That is, **After restoring to the original host, privileges were preserved**, at least for this test. – Aubrey Lavigne Oct 13 '21 at 15:45
@dolmen yes, it does. – Peter Oct 15 '21 at 22:31

score 2 · Answer 3 · answered Apr 24 '14 at 20:21

2

Set the user's password hash (in mysql.user.Password) to an invalid dummy value (e.g, "!") to disable the account, and set it back to the original value to reenable it.

answered Apr 24 '14 at 20:21

1

Fun fact: When you think you're clever and just replace the leading `*` with `!`, so that you can easily reenable the account again, it doesn't work. That's ignored. Not saying that your answer is wrong though. Replacing the whole string works of course. – fancyPants Apr 25 '18 at 07:07
@fancyPants Did you `FLUSH PERMISSIONS` after updating the table? – Apr 25 '18 at 07:09
2

Yes, I did of course, it's `FLUSH PRIVILEGES` though. – fancyPants Apr 25 '18 at 07:42

Best practices for temporarily enabling and disabling a MySQL user account?

3 Answers3