MYSQL暴力增加用户

背景

开发项目过程中我只知道某一个用户的账号信息(可以登陆)并且可以对 mysql 库进行操作。我需要新增一个和root一样角色的用户。

实现步骤

# 新增一个用户
insert into `mysql`.`user` (
  `Host`,
  `User`,
  `Select_priv`,
  `Insert_priv`,
  `Update_priv`,
  `Delete_priv`,
  `Create_priv`,
  `Drop_priv`,
  `Reload_priv`,
  `Shutdown_priv`,
  `Process_priv`,
  `File_priv`,
  `Grant_priv`,
  `References_priv`,
  `Index_priv`,
  `Alter_priv`,
  `Show_db_priv`,
  `Super_priv`,
  `Create_tmp_table_priv`,
  `Lock_tables_priv`,
  `Execute_priv`,
  `Repl_slave_priv`,
  `Repl_client_priv`,
  `Create_view_priv`,
  `Show_view_priv`,
  `Create_routine_priv`,
  `Alter_routine_priv`,
  `Create_user_priv`,
  `Event_priv`,
  `Trigger_priv`,
  `Create_tablespace_priv`,
  `ssl_type`,
  `ssl_cipher`,
  `x509_issuer`,
  `x509_subject`,
  `max_questions`,
  `max_updates`,
  `max_connections`,
  `max_user_connections`,
  `plugin`,
  `authentication_string`,
  `password_expired`,
  `password_last_changed`,
  `password_lifetime`,
  `account_locked`,
  `Create_role_priv`,
  `Drop_role_priv`,
  `Password_reuse_history`,
  `Password_reuse_time`,
  `Password_require_current`,
  `User_attributes`
) 
select 
  '%',
  'user',
  `Select_priv`,
  `Insert_priv`,
  `Update_priv`,
  `Delete_priv`,
  `Create_priv`,
  `Drop_priv`,
  `Reload_priv`,
  `Shutdown_priv`,
  `Process_priv`,
  `File_priv`,
  `Grant_priv`,
  `References_priv`,
  `Index_priv`,
  `Alter_priv`,
  `Show_db_priv`,
  `Super_priv`,
  `Create_tmp_table_priv`,
  `Lock_tables_priv`,
  `Execute_priv`,
  `Repl_slave_priv`,
  `Repl_client_priv`,
  `Create_view_priv`,
  `Show_view_priv`,
  `Create_routine_priv`,
  `Alter_routine_priv`,
  `Create_user_priv`,
  `Event_priv`,
  `Trigger_priv`,
  `Create_tablespace_priv`,
  `ssl_type`,
  `ssl_cipher`,
  `x509_issuer`,
  `x509_subject`,
  `max_questions`,
  `max_updates`,
  `max_connections`,
  `max_user_connections`,
  `plugin`,
  `authentication_string`,
  `password_expired`,
  `password_last_changed`,
  `password_lifetime`,
  `account_locked`,
  `Create_role_priv`,
  `Drop_role_priv`,
  `Password_reuse_history`,
  `Password_reuse_time`,
  `Password_require_current`,
  `User_attributes` 
from
  mysql.user 
where user = 'root' 
  and host = 'localhost' ;

# 生成密码 select password('123456')
# *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
update mysql.user set authentication_string = '*E9D233A8016FC733259BC25101AC63BDA00CE6C8' where user = 'user';

# 刷新权限
flush privileges;

# 可以登陆测试了

后记

此思路可以是哦那个多个场景，比如忘记密码等等。
兼容不支持 mysql_native_password客户端使用 update mysql.user set plugin = 'mysql_native_password' where user = 'user';

nstudy.org