1. 用户管理
1.1 登录MySQL服务器
启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：
mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
下面详细介绍命令中的参数：
-h接主机名或者主机IP，hostname为主机，hostIP为主机IP。默认为localhost-P接MySQL服务的端口。port为连接的端口号。默认端口是3306-u接用户名-p会提示输入密码。DatabaseName参数 指明登录到哪一个数据库中。(可不选)-e登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL 服务器
举例：
mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"
1.2 创建用户
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
- 用户名参数表示新建用户的账户，由 用户 和 主机名（Host） 构成；
- “[ ]”表示可选，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。使用 IDENTIFIED BY指定明文密码值。
- 可以同时创建多个用户。
举例：
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';
1.3 修改用户名
UPDATE mysql.user SET USER='li4' WHERE USER='wang5' [AND HOST ='localhost']; # 若USER字段相同则要＋ HOST 字段确定唯一性
FLUSH PRIVILEGES;
1.4 删除用户
方式1：使用DROP方式删除（推荐）
DROP USER user[,user]…;
举例：
DROP USER li4 ; # 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';
方式2：使用DELETE方式删除
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
FLUSH PRIVILEGES; # 执行完要 flush 生效
注意：不推荐通过 DELETE 进行删除，系统会有残留信息保留。而drop 命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表 的相应记录都消失了。
1.5 设置当前用户密码
- 使用
ALTER USER命令来修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';
- 使用SET语句来修改当前用户密码
SET PASSWORD='new_password';
该语句会自动将密码加密后再赋给当前用户。
1.6 修改其它用户密码
ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;
root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码：
SET PASSWORD FOR 'username'@'hostname'='new_password';
1.7 MySQL8 密码管理(了解)
过期后仍可登录，但无法进行查询
- 密码过期策略
- 数据库管理员可以
手动设置账号密码过期，也可以建立一个自动密码过期策略。 - 过期策略可以是
全局的，也可以为每个账号设置单独的过期策略。
ALTER USER user PASSWORD EXPIRE; # 手动设置立刻过期- 方式①：使用SQL语句更改该变量的值并持久化
SET PERSIST default_password_lifetime = 180; # 建立全局策略，设置密码每隔180天过期- 方式②：配置文件my.cnf中进行维护
[mysqld] default_password_lifetime=180 #建立全局策略，设置密码每隔180天过期 - 数据库管理员可以
手动设置指定时间过期方式2：单独设置
在 CREATE 和 ALTER 语句上加 入 PASSWORD EXPIRE 选项可实现单独设置策略。
#设置kangshifu账号密码每90天过期：
CREATE/ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
#设置密码永不过期：
CREATE/ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
#延用全局密码过期策略：
CREATE/ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
- 密码重用策略
- 全局
- SQL语句
SET PERSIST password_history = 6; #设置不能选择最近使用过的6个密码 SET PERSIST password_reuse_interval = 365; #设置不能选择最近一年内的密码- my.cnf配置文件
[mysqld] password_history=6 password_reuse_interval=365 - 单独设置
#不能使用最近5个密码：
CREATE/ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
#不能使用最近365天内的密码：
CREATE/ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
#既不能使用最近5个密码，也不能使用365天内的密码
CREATE/ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;
2. 权限管理
2.1 权限列表
MySQL到底都有哪些权限呢？
show privileges;
CREATE、DROP权限：创建/删除 数据库和表。SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。SELECT权限只有在它们真正从一个表中检索行时才被用到。INDEX权限允许创建或删除索引。适用于已有的表，如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。ALTER权限可以使用ALTER TABLE来更改表的结构和重新命名表。CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。FILE权限被授予FILE权限的用户能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。
2.2 授予权限的原则
经验原则 ：
- 只授予能满足需要的最小权限 ，防止用户干坏事。
- 创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
- 为用户设置满足密码复杂度的密码
- 定期清理不需要的用户，回收权限或者删除用户。
2.3 授予权限
给用户授权的方式有 2 种，分别是通过把 给用户赋予角色(第五节讲) 和 直接给用户授权
GRANT 操作为 权限叠加 而非权限覆盖
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
- 该权限如果发现没有该用户，则会直接新建一个用户。
GRANT SELECT,INSERT,DELETE,UPDATE ON db1.* TO li4@localhost ;
- 授权 u1 对所有库所有表的全部权限，密码设为123。注意这里
唯独不包括 grant的权限
GRANT ALL PRIVILEGES ON *.* TO u1@'%' [IDENTIFIED BY '123'] ; # 若已有该用户则无需密码
2.4 查看权限
- 查看当前用户权限
SHOW GRANTS;
- 查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址' ;
2.5 收回权限
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。
- 收回权限命令
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
- 举例：
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM u1@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM u1@localhost;
马老师，发生甚麽事了？
噢原来是 root 没有
system_user 权限啊
grant system_user on *.* to 'root';
- 注意： 须用户重新登录后才能生效
3. 权限表
3.1 user表
user表是MySQL中最重要的一个权限表， 记录 用户账号和权限信息
查看user表：
DESC mysql.user;
通过 host和user 联合主键
3.1.1 范围列(或用户列)
| 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
|---|---|---|---|---|
| Host | char(60) | NO | 无 | 主机名 |
| User | char(32) | NO | 无 | 用户名 |
| authentication_string | text | YES | 无 | 密码 |
- host
%表示所有远程通过 TCP方式的连接IP地址 如 (192.168.1.2、127.0.0.1) 通过制定ip地址进行的TCP方式的连接 机器名 通过制定网络中的机器名进行的TCP方式的连接::1IPv6的本地ip地址，等同于IPv4的 127.0.0.1localhost本地方式通过命令行方式的连接 ，比如mysql -u xxx -p xxx 方式的连接。
3.1.2 权限列
权限大致分为两大类，分别是高级管理权限和普通权限：
- 高级管理权限主要对数据库进行管理，例如关闭服务的权限、超级权限和加载用户等；
- 普通权限主要操作数据库，例如查询权限、修改权限等。
挑选了几个关键的展示
| 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
|---|---|---|---|---|
| Select/Insert/Update/Delete_priv | enum('N','Y') | NO | N | 是否可以增删查改 |
| Create/Drop_priv | enum('N','Y') | NO | N | 是否可以创建/删除数据库和表 |
| Shutdown_priv | enum('N','Y') | NO | N | 是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时，都应当非常谨慎 |
| Grant_priv | enum('N','Y') | NO | N | 是否可以将自己的权限再授予其他用户 |
| Super_priv | enum('N','Y') | NO | N | 是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程；使用SET GLOBAL命令修改全局MySQL变量，执行关于复制和日志的各种命令。（超级权限） |
| Execute_priv | enum('N','Y') | NO | N | 是否可以执行存储过程 |
3.1.3 安全列
安全列只有6个字段，其中两个是ssl相关的，用于 加密 ；两个 x509 相关的用于标识用户 ；两个Plugin字段用于 验证用户身份 的插件， 该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。
| 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
|---|---|---|---|---|
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | 支持ssl标准加密安全字段 | |
| ssl_cipher | blob | NO | 支持ssl标准加密安全字段 | |
| x509_issuer | blob | NO | 支持x509标准字段 | |
| x509_subject | blob | NO | 支持x509标准字段 | |
| plugin | char(64) | NO | mysql_native_password | 引入plugins以进行用户连接时的密码验证，plugin创建外部/代理用户 |
3.1.4 资源控制列
限制用户使用的资源
- 默认字段为 0 ， 表示没有限制 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 | | -------------------- | ---------------- | ---- | --- | ---------------- | | max_questions | int(11) unsigned | NO | 0 | 每小时允许执行查询的操作次数 | | max_updates | int(11) unsigned | NO | 0 | 每小时允许执行更新的操作次数 | | max_connections | int(11) unsigned | NO | 0 | 每小时允许执行的连接操作次数 | | max_user_connections | int(11) unsigned | NO | 0 | 允许同时建立的连接次数
3.2 db表
查看表
DESC mysql.db;
- 用户列
Host、User、Db。这3个字段分别表示主机名、用户名和数据库名。 表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。 - 权限列:与user表类似，只不过是对某数据库的权限。
3.3 tables_priv表和columns_priv表
tables_priv表用来 对表设置操作权限 ，columns_priv表用来对表的 某一列设置权限 。
tables_priv表有8个字段，
Host 、 Db 、 User 和 Table_name四个字段分别表示主机名、数据库名、用户名和表名。Grantor表示修改该记录的用户。Timestamp表示修改该记录的时间。Table_priv表示对象的操作权限。包括Select、Insert...Column_priv字段表示对表中的列的操作权限，包括Select...
3.4 procs_priv表
存储过程和存储函数设置操作权限
4. 角色管理
4.1 角色的理解
角色是权限的集合。引入角色的目的是 方便管理拥有相同权限的用户 。
4.2 创建角色
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为空。
练习：我们现在需要创建一个经理的角色，就可以用下面的代码：
CREATE ROLE 'manager'@'localhost';
4.3 给角色赋予权限
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
展示所有权限：
SHOW PRIVILEGES\G;
练习1：我们现在想给经理角色 db1.test表 的只读权限，就可以用下面的代码 来实现：
GRANT SELECT ON db1.test TO 'manager';
4.4 查看角色的权限
SHOW GRANTS FOR 'manager';
只要你创建了一个角色，系统就会自动给你一个 USAGE 权限，意思是 连接登录数据库的权限 。
4.5 回收角色的权限
REVOKE privileges ON tablename FROM 'rolename';
撤销刚刚赋予的权限：
revoke select on test from 'manager';
4.6 删除角色
DROP ROLE role1 [,role2]...
删除 manager 角色：
DROP ROLE 'manager';
4.7 给用户赋予角色
GRANT role [,role2,...] TO user1 [,user2,...];
4.8 激活角色
角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。
- 查看当前用户是否激活角色
SELECT CURRENT_ROLE()
- 方法1：
SET DEFAULT ROLE 'manager'@'%' TO user1 [,user2...];
# 激活用户拥有的角色
SET DEFAULT ROLE ALL TO user1 [,user2...];
- 方法2：
show variables like 'activate_all_roles_on_login'; # 默认为 OFF
SET GLOBAL activate_all_roles_on_login=ON; # 对所有角色永久激活
4.9 撤销用户的角色
REVOKE role FROM user;
举例：
REVOKE 'manager' FROM 'user'@'localhost';
4.10 设置强制角色(mandatory role)
给每个创建的用户的默认角色，无需手动设置，不能被 REVOKE 和 DROP
- 方法1：服务启动前设置
[mysqld]
mandatory_roles='role1,role2@localhost'
- 方式2：运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost'; #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost'; #系统重启后失效
