Mike's Database Place

MySQL Users

Databases

MySQL

MySQL Security

Percona Toolkit

Check

SELECT host,

user,

account_locked,

plugin,

password_expired,

password_last_changed,

password_lifetime

FROM mysql.user;

SHOW CREATE USER user@host;

SHOW GRANTS FOR user@host;

IT Security

MySQL Authentication

User authentication related configuration parameters etc.

MySQL Authorisation

Roles & Privileges

mysql_config_editor

Encrypted password data store for automatic login

CREATE

CREATE USER 'myuser'@'myhost.mydomain' IDENTIFIED BY RANDOM PASSWORD;

Generated random passwords have a length of 20 characters. This length is controlled by the generated_random_password_length system variable.This option not available in older versions of MySQL or derivatives.

If you need control over what the password will be, then use...

CREATE USER 'myuser'@'myhost.mydomain' IDENTIFIED BY 'myPassword'

GRANT

GRANT ALL PRIVILEGES ON database.* TO myuser@%;

To limit login attempts (e.g. to reduce risks of a Denial Of Service style of attack)...

GRANT USAGE ON *.* TO 'myuser'@'myhost' WITH MAX_CONNECTIONS_PER_HOUR 50;

Limits number of connection attempts by 'myuser'@'myhost to any database to 50 per hour

See other pages for examples of specific GRANT options...

MySQL Stored Procedures

REVOKE

REVOKE ALL PRIVILEGES ON database.* FROM myuser@%;

ALTER

ALTER USER 'myuser'@'myhost.mydomain' IDENTIFIED BY RANDOM PASSWORD;

NOTE: On older versions of MySQL (5.7 and earlier) you may need to change passwords using...SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('mypassword');

DROP

DROP USER 'myuser'@'localhost';

Duplicate

Percona Toolkit

If you have the Percona Toolkit installed you can dump the CREATE statements and GRANTs for all users...

pt-show-grants --ask-pass --user root

MySQL Backup & Recovery

Bibliography

https://dev.mysql.com/doc/refman/5.7/en/create-user.htmlhttps://dev.mysql.com/doc/refman/5.7/en/show-create-user.html
https://dev.mysql.com/doc/refman/8.0/en/create-user.htmlhttps://dev.mysql.com/doc/refman/8.0/en/roles.htmlhttps://dev.mysql.com/doc/refman/8.0/en/grant.htmlhttps://dev.mysql.com/doc/refman/8.0/en/privileges-provided.htmlhttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
https://mysqlserverteam.com/everything-about-mysql-logins/https://linuxize.com/post/how-to-create-mysql-user-accounts-and-grant-privileges/#display-mysql-user-account-privileges https://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database/ https://stackoverflow.com/questions/5376427/cant-connect-to-local-mysql-server-through-socket-var-mysql-mysql-sock-38
https://stackoverflow.com/questions/20698335/error-1396-hy000-operation-drop-user-failed-for-userlocalhosthttps://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw
Reset Root Passwordhttps://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.htmlhttps://www.techrepublic.com/article/how-to-set-change-and-recover-a-mysql-root-password/https://www.percona.com/blog/reset-mysql-root-password-without-restarting-mysql-no-downtime/
Password Managementhttps://dev.mysql.com/doc/refman/8.0/en/password-management.htmlhttps://dev.mysql.com/doc/refman/5.7/en/set-password.html
MAX_CONNECTIONS_PER_HOURhttps://serverfault.com/questions/65255/log-mysql-login-attempts

Page updated

Google Sites

Report abuse