MySQL数据库用户管理和授权

一、数据库用户管理

1. 创建用户

要创建新用户，可以使用 CREATE USER 语句。语法如下：

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];

• '用户名'：指定要创建的用户名。
• '来源地址'：指定用户可以从哪些主机登录，使用 IP 地址、网段、主机名的形式，本地用户可用 localhost，允许任意主机登录可用通配符 %。
• '密码'：若使用明文密码，直接输入 '密码'，数据库会自动加密；若使用加密密码，需要先使用 SELECT PASSWORD('密码'); 获取密文，再在语句中使用 PASSWORD '密文'；若省略 IDENTIFIED BY 部分，则用户的密码为空（不建议使用）。

示例：

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
SELECT PASSWORD('abc123');
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2. 查看用户信息

用户信息保存在 MySQL 数据库的 user 表中，可以使用以下语句查看：

USE mysql;
SELECT User, authentication_string, Host FROM user;

3. 重命名用户

要重命名用户，可以使用 RENAME USER 语句：

RENAME USER '旧用户名'@'来源地址' TO '新用户名'@'来源地址';

示例：

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

4. 删除用户

要删除用户，可以使用 DROP USER 语句：

DROP USER '用户名'@'来源地址';

示例：
DROP USER 'lisi'@'localhost';

5. 修改密码

• 修改当前用户密码：

SET PASSWORD = PASSWORD('新密码');

• 修改其他用户密码：

SET PASSWORD FOR '用户名'@'来源地址' = PASSWORD('新密码');

示例：

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123T');

6. 忘记 root 密码的解决办法

① 修改 /etc/my.cnf 配置文件，添加 skip-grant-tables 以免密登录 MySQL：

[mysqld]
skip-grant-tables

② 重启 MySQL 服务：

systemctl restart mysqld

③ 直接登录 MySQL：

mysql

④ 使用 SQL 语句修改 root 密码：

UPDATE mysql.user SET authentication_string = PASSWORD('新密码') WHERE user = 'root';
FLUSH PRIVILEGES;

使用 FLUSH PRIVILEGES; 命令使得密码更改立即生效。

在修改后quit退出mysql，再把/etc/my.cnf 配置文件里的skip-grant-tables 删除或注释，并重启mysql服务

二 、数据库用户授权

1. 授予权限

要授予用户权限，可以使用 GRANT 语句：

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

1.1 权限列表

• all (所有权限)
• insert (插入数据)
• select (查询数据)
• update (更新表的数据)
• delete (删除表中数据)
• create (创建库，表)
• drop (删除库，表)
• refernces (创建外键引用)
• index (建立索引)
• alter (更改表属性)
• create temp orary tableslock tables (锁表)
• execute (执行存储过程)
• create view (创建视图)
• show view (显示视图)
• create routine (创建存储过程)
• alter routine (修改存储过程)
• event (事件)
• trigger on (创建触发器)
   

1.2 示例：

允许用户 zhangsan 在本地查询 school 数据库中的所有表的数据记录：

GRANT SELECT ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';

允许用户 lisi 在所有终端远程连接 MySQL，并拥有所有权限：

GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';

允许用户 lisi 在指定 IP 地址上连接，并拥有 kgc 数据库的所有权限：

GRANT ALL PRIVILEGES ON kgc.* TO 'lisi'@'192.168.10.2' IDENTIFIED BY 'abc123';

2. 查看权限

要查看用户的权限，可以使用 SHOW GRANTS 语句：

SHOW GRANTS FOR '用户名'@'来源地址';

示例：
SHOW GRANTS FOR 'lisi'@'%';

3. 撤销权限

要撤销用户的权限，可以使用 REVOKE 语句：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'来源地址';

示例：

撤销用户 lisi 在所有终端的所有权限：

REVOKE ALL ON *.* FROM 'lisi'@'%';

查看用户 lisi 撤销后的权限：

SHOW GRANTS FOR 'lisi'@'%';

注意： USAGE 权限仅允许用户登录数据库，不能执行任何操作，而且 USAGE 权限不能被撤销，即 REVOKE 不能删除用户。

4. 刷新权限

在修改权限后，需要刷新权限以使更改生效：

FLUSH PRIVILEGES;