mysql--用户管理

MySQL 用户管理完整指南

！！给root用户配置远程登录

CREATE USER 'root'@'%' IDENTIFIED BY '123456';
#给所有权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

1. 查看用户信息

查看所有用户

SELECT User, Host, authentication_string FROM mysql.user;

查看用户详细信息

SELECT * FROM mysql.user \G

查看当前登录用户

SELECT CURRENT_USER();

查看特定用户的权限

SHOW GRANTS FOR 'username'@'host';

2. 创建用户

基本创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

创建用户并授予权限

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';
FLUSH PRIVILEGES;

创建用户并限制资源使用

CREATE USER 'username'@'host' 
IDENTIFIED BY 'password'
WITH 
  MAX_QUERIES_PER_HOUR 100
  MAX_UPDATES_PER_HOUR 20
  MAX_CONNECTIONS_PER_HOUR 50
  MAX_USER_CONNECTIONS 5;

3. 修改用户密码

修改当前用户密码

ALTER USER USER() IDENTIFIED BY 'new_password';

修改指定用户密码

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

使用SET PASSWORD修改密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

密码过期策略

ALTER USER 'username'@'host' PASSWORD EXPIRE;

4. 修改用户权限

授予所有数据库权限

GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

授予特定数据库权限

GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

授予特定表权限

GRANT SELECT, INSERT ON database.table TO 'username'@'host';

授予存储过程权限

GRANT EXECUTE ON PROCEDURE database.procedure_name TO 'username'@'host';

撤销权限

REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host';

刷新权限

FLUSH PRIVILEGES;

5. 修改用户属性

重命名用户

RENAME USER 'old_username'@'host' TO 'new_username'@'host';

修改用户主机限制

UPDATE mysql.user SET Host='new_host' WHERE User='username' AND Host='old_host';
FLUSH PRIVILEGES;

锁定/解锁用户

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

6. 删除用户

删除用户

DROP USER 'username'@'host';

删除多个用户

DROP USER 'user1'@'host1', 'user2'@'host2';

7. 用户资源限制

设置用户资源限制

ALTER USER 'username'@'host' 
WITH 
  MAX_QUERIES_PER_HOUR 100
  MAX_UPDATES_PER_HOUR 20
  MAX_CONNECTIONS_PER_HOUR 50
  MAX_USER_CONNECTIONS 5;

查看用户资源限制

SELECT * FROM mysql.user WHERE User='username' \G

8. 密码策略管理

查看密码策略

SHOW VARIABLES LIKE 'validate_password%';

修改密码策略

SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.length = 6;

9. 用户连接管理

查看当前连接用户

SELECT * FROM information_schema.processlist;

查看用户连接数

SELECT USER, COUNT(*) as connections 
FROM information_schema.processlist 
GROUP BY USER;

终止用户连接

KILL connection_id;

10. 安全建议

1. 避免使用root用户远程连接
2. 为每个应用创建单独的用户
3. 遵循最小权限原则
4. 定期审计用户权限
5. 使用强密码策略
6. 定期轮换密码
7. 删除不再使用的用户