-de8bd8f33c3e44a59907dafe1884f228.png)
用户管理
MySQL 用户的定义格式：
<用户名>@<白名单地址>
白名单地址指的是允许登录数据库的主机地址，看如下定义示例：
zze@'%'：允许所有网络地址以zze用户登录数据库；zze@'10.0.1.%'：允许10.0.1.0/24网段以zze用户登录数据库；zze@'10.0.1.5%'：允许10.0.1.50-10.0.1.59段的地址以zze用户登录数据库；zze@'10.0.1.0/255.255.255.0'：同zze@'10.0.1.%'；zze@'localhost'：仅允许本机地址以zze用户登录数据库；zze@'db01'：允许主机名为db01的主机以zze用户登录数据库；zze@'zze.xyz'：允许对应域名为zze.xyz的 IP 以zze用户登录数据库；
查询用户
用户的信息保存在 MySQL 中的 mysql 库下的 user 表中，其中我们需要重点关注的列如下：
mysql> select user,host,authentication_string,plugin from mysql.user;
+---------------+-----------+-------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+---------------+-----------+-------------------------------------------+-----------------------+
| root | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF | mysql_native_password |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+---------------+-----------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)
上述几列的描述如下：
user：用户名；host：允许登录的主机，即白名单地址；autoentication_string：加密后的密码；plugin：密码加密使用的加密插件；
创建用户
创建一个用户，用户名为 zze，允许通过 10.0.1.0/24 网段的主机登录，并授权给其所有权限：
mysql> create user zze@'10.0.1.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to zze@'10.0.1.%';
MySQL 中关键字不区分大小写，字面量是区分大小写的，在上述 SQL 中 create user、identified by 是关键字，zze@10.0.1.% 和 123 是字面量。
在 MySQL 5.7 中也可通过如下单条命令同时完成上面的用户创建及授权的操作：
mysql> grant all on *.* to zabbix@'%' identified by '123';
但是不推荐这样使用，因为在 MySQL 8.0+ 中已经不支持 identified by 字句了。
MySQL 8.0+ 的版本采用了全新的密码加密插件 caching_sha_password，在主从、MHA、以及使用部分客户端的场景下，需要将加密插件替换为 5.7 版本支持 mysql_native_password。
下面是在 MySQL 8.0+ 中指定插件创建用户的一个示例：
mysql> create user zze@% identified with mysql_native_password by '123'`;
修改用户
以修改用户密码为 123456 为例：
mysql> alter user zze@'10.0.1.%' identified by '123456'
在 MySQL 8.0+ 可同时修改密码插件：
mysql> alter user zze@'10.0.1.%' identified with mysql_native_password by '123';
让密码过期禁止用户登录：
mysql> alter user zze@'10.0.1.%' password expire;
锁定与解锁用户：
-- 锁定
mysql> alter user zze@'10.0.1.%' account lock;
-- 解锁
mysql> alter user zze@'10.0.1.%' account unlock;
删除用户
以删除 zze@'10.0.1.%' 用户为例：
mysql> drop user zze@'10.0.1.%';
权限管理
MySQL 中权限管理的作用是用来约束、控制用户能对 MySQL 中的对象（库、表）做什么操作。
- 在 MySQL 8.0 以前，MySQL 仅支持基于命令的授权，即授权的最小粒度为命令。
- 在 MySQL 8.0 以后，MySQL 加入了 role（角色）的授权。
MySQL 中的用户权限是基于表进行存储的，有如下几个常用的授权表：
mysql.user：保存了所有用户的基本信息和和全库级别的权限信息；mysql.db：保存了用户在单库级别的权限信息；mysql.tables_priv：保存了用户在单表级别的权限信息；mysql.columns_priv：保存了用户在单列级别的权限信息；
查看可授权的权限
可通过如下 SQL 查看可授权的权限列表：
mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.01 sec)
其中 Usage 权限其实就是新创建用户未做任何授权的状态，仅允许登录数据库。
除上面列表中显示的权限之外，在授权时还可使用 all 关键字，它是一个权限的集合，包含了除 Grant option 的所有权限。
授权
以授权 zze@'10.0.1.%' 用户为超级管理员为例，即授予其所有权限，执行下面 SQL 即可：
mysql> grant all on *.* to zze@'10.0.1.%' identified by '123' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
上述 on *.* 指的是一个授权范围，其格式为 on <库>.<表>，* 表示所有，所以 on *.* 的含义就是授权所有库的所有表。
以授权一个普通用户 test@'10.0.1.%' 为例，让其拥有 test 库所有表的 select、update、delete 和 insert 权限，执行下面 SQL 即可：
mysql> grant select,update,delete,insert on test.* to test@'10.0.1.%' identified by '123';
多个权限之间用 , 隔开。
查看权限
以查看 test@'10.0.1.%' 拥有的权限为例，执行下面 SQL 即可：
mysql> show grants for test@'10.0.1.%';
+-----------------------------------------------------------------------+
| Grants for test@10.0.1.% |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'10.0.1.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'test'@'10.0.1.%' |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
撤销授权
以撤销 test@'10.0.1.%' 用户对 test 库中所有表的删除权限为例，执行下面 SQL 即可：
mysql> revoke delete on test.* from test@'10.0.1.%';
角色
在 MySQL 8.0+ 中新增了角色的功能，角色就是权限的集合，创建角色后可以通过将用户设定为指定的角色，从而间接的赋予对应角色拥有的所有权限给用户。
创建角色：
mysql> CREATE ROLE 'app_developer', 'app_read', 'app_write';
角色授权：
mysql> GRANT ALL ON app_db.* TO 'app_developer';
mysql> GRANT SELECT ON app_db.* TO 'app_read';
mysql> GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
创建用户：
mysql> CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
mysql> CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
mysql> CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
mysql> CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
授权用户角色：
mysql> GRANT 'app_developer' TO 'dev1'@'localhost';
mysql> GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
mysql> GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
扩展
查看表定义
可通过 desc <TABLE> 的方式查看指定表的定义信息：
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
...
45 rows in set (0.00 sec)
查看帮助
通过 help <关键字> 查看对应关键字的用法，以查看 create user 的用法为例：
mysql> help create user;
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
...
开启日志
以将所有执行过的 SQL 及其结果记录到 mysqlop.log 中为例：
mysql> tee mysqlop.log
Logging to file 'mysqlop.log'
要注意的是，此种方式仅会保存执行 tee 之后的执行的 SQL 及其结果。
评论区