Menu
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.
573
One means of restricting client use of MySQL server resources is to set the global max_user_connections system variable to a nonzero value.
Figure 9-1 shows an example of the MySQL privilege hierarchy. There are four levels; reading from highest to lowest, these are global, database, table, and column.
Database privileges apply to specific databases in your MySQL instance and all of the objects within those databases (e.g. tables, columns, and views). You can also grant database privileges globally. Proxy privileges allow a user to act as if they have the privileges granted to another user.
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:
156
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
