MySQL用户与权限
Table of Contents
- mysql语句中所有加单引号的地方，也可以换成双引号，虽然官方例子都用单引号，但是我实际测试，无论单双引号都是一样的效果，没有区别(mysql8.0.29)。
- 所有大写的关键字，其实都可以小写，平时打命令的时候没必要大写，因为大小写转来转去很麻烦，没必要浪费时间在这上面，但规范上还是关键字要大写，这样看起来美观，而且方便让人知道那是关键字；
MySQL自带数据库
mysql自带四个数据库，我们用show databases;语句可以查看
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
information_schema
- 保存了MySQL服务所有数据库的信息；
- 具体MySQL服务有多少个数据库，各个数据库有哪些表，各个表中的字段是什么数据类型，各个表中有哪些索引，各个数据库要什么权限才能访问；
- 事实上这里面的表并不是真正存在的表，它们只不过是视图而已(见INFORMATION_SCHEMA Usage Notes)；
- > The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.
mysql
- 保存了MySQL的用户(在user表)以及对应的权限；
- 保存MySQL的权限、参数、对象和状态信息，如哪些user可以访问这个数据、DB参数、插件、主从等等。
performance_schema
- 主要用于收集数据库服务器性能参数；
- 提供进程等待的详细信息，包括锁、互斥变量、文件信息；
- 保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
- 对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。
sys
Sys库所有的数据源来自performance_schema库。目的是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容，让DBA更快的了解DB的运行情况。
用户相关操作
查看用户
由前面的知识可知，mysql的用户是保存在mysql自带的一个名为mysql的数据库中的user表中的，所以我们要查看当前数据库有哪些用户，直接查询mysql.user表就可以。
首先我们先来看一下mysql.user表中有哪些字段
mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
可以看到，它有Host和User两个字段，分别表示允许登录的主机和用户名，其它的基本上都是权限相关的，用于标记该用户是否有权限查看、插入、更新、删除数据等等，如果我们单纯想看用户名，那就只要Host和User两个字段就行。
查看当前数据库有哪些用户
SELECT host,user FROM mysql.user;
注：虽然这些字段都是大写开头，但其实我们写的时候为了方便，可以不大写开头也是一样的。
创建用户
语法
CREATE USER 用户名@允许登录主机 IDENTIFIED WITH 密码认证插件 BY '密码'；
密码认证插件有3种：mysql_native_password、caching_sha2_password、sha256_password。
举例
CREATE USER 'zhangsan'@'12.34.56.78' IDENTIFIED WITH mysql_native_password BY '123456'；
用户名和允许登录主机可以不加引号，但密码必须加引号(单引双引都可以)，否则报错
CREATE USER [email protected] IDENTIFIED WITH mysql_native_password BY '123456'；
默认情况下，以下两条语句等效
CREATE USER zhangsan IDENTIFIED BY '123456'；
CREATE USER zhangsan@'%' IDENTIFIED WITH caching_sha2_password BY '123456'；
也就是说，如果你不手动允许登录的主机，那么默认就是%，熟悉sql的童鞋应该知道，%在sql中是表示“所有”的意思，也就是说，这样创建的用户是不限制登录来源主机的。
而对于“caching_sha2_password”，它是mysql8.0默认的加密插件，如果你不用WITH mysql_native_password指定，那么它默认就会用“caching_sha2_password”作为加密插件，当然，你也可以在/etc/my.cnf文件中用
default_authentication_plugin = mysql_native_password
来指定默认插件，这样即使你不写，它默认也是“mysql_native_password”。
我们知道，登录mysql的命令为
# 完整写法
mysql -h localhost -u root -p
# -h和-u后面也可以不要空格(好像这种更多人用)
mysql -hlocalhost -uroot -p
# 简化写法(省略-h，则-h默认值为localhost)
mysql -uroot -p
“允许登录主机”的意思是，以前面的'zhangsan'@'12.34.56.78'为例，假设mysql服务器安装在“23.45.67.89”这台机中，你必须在”12.34.56.78″这台机器里用以下命令才能登录
mysql -h 23.45.67.89 -uroot -p
如果你不在”12.34.56.78″这台机器中，那么就算你知道用户名和密码也是无法登录的。
也就是说，mysql是会限制登录来源机器的，这样就保证了即使密码不小必泄漏，得到密码的人还是无法登录你的mysql服务器，因为你的数据库只能从”12.34.56.78″这台机器里去登录它，所以，除非它能进入你”12.34.56.78″这台机器里，否则它是无法登录的。
事实上，在mysql库的user表里，'zhangsan'@'locahost'和'zhangsan'@'127.0.0.1'是两条记录，也就是说，虽然用户名相同，但其内部其实是把它们当成完全不同的两个用户的，只不过恰好它们的用户名相同而已。
授予用户权限
添加用户后必须授予权限，否则该用户只能查看information_schema表，没有其它权限
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
授权语句其实就是一句英文，意思是：你要给谁授予“哪些数据库的哪些表”哪些权限，是否包括“授权”权限？
授予select、insert、update、delete权限
# 格式
GRANT select,insert,update,delete to 用户名@允许登录主机;
# 例：授予[email protected]这个用户select,insert,update,delete四个权限
GRANT select,insert,update,delete to [email protected];
授予所有权限(grant权限除外)
# ALL PRIVILEGES 表示所有权限，但不包括“授权”这个权限
GRANT ALL PRIVILEGES ON 数据库名.表名 to 用户名@允许登录主机;
# ALL PRIVILEGES 可简写为 ALL
GRANT ALL ON 数据库名.表名 to 用户名@允许登录主机;
授予所有权限(包括grant权限)
# ALL PRIVILEGES 表示所有权限，但不包括“授权”这个权限
GRANT ALL PRIVILEGES ON 数据库名.表名 to 用户名@允许登录主机 WITH GRANT OPTION;
# ALL PRIVILEGES 可简写为 ALL
GRANT ALL ON 数据库名.表名 to 用户名@允许登录主机 WITH GRANT OPTION;
with grant option意思是附加grant权限，如果不加这个，那么这个用户是无法使用grant语句的，而grant语句是用来授权用的，换句话说，如果你不带上with grant option，那么这个用户将无法给其它用户授予权限。
关于刷新权限问题
很多介绍grant/revoke语句的文章可能都会带有这个刷新权限的语句
flush privileges;
事实上我们真的要刷新权限吗？其实是不用的！因为grant/revoke语句会在把权限添加到user表(或db表)后，再刷新内存中的数据，所以我们根本不需要手动用flush privileges来刷新权限。
但要注意，如果被授予/撤消权限的用户已经登录mysql，那么你授予/撤消权限后，无论你是否执行刷新权限语句，它都不会生效，只有该用户重新登录，权限才会生效。
刷新权限其实是根据数据表中的权限在内存中重建一份数据(相当于我们的数据虽然在mysql中有，但是会在redis中存一份)，这个操作一般是用于有时候手动修改了user或db表中的权限，或者出于一种需要“确定”一遍的心理，才需要用到。
撤回用户权限
撤回用户权限的命令也是一句英文，意思是：从谁的手里撤回哪些数据库中的哪些表的哪些权限？
撤回update和delete权限
REVOKE update, delete ON *.* FROM [email protected];
撤回grant权限(grant权限是用来授权的权限)
REVOKE grant option ON *.* FROM [email protected];
注意：grant权限不像其它权限，其它权限都是直接一个单词，比如更新就是update，删除就是delete，但grant权限不能只写grant，而应该写成grant option。
撤回所有权限(all privileges)
REVOKE all privileges ON *.* FROM [email protected];
# all privileges 可简写为 all
REVOKE all ON *.* FROM [email protected];
删除用户
格式
# 删除一个用户
DROP USER '用户名'@'允许登录主机'
# 同时删除多个用户，每个用户之间用英文逗号隔开即可
DROP USER '用户名'@'允许登录主机', '用户名2'@'允许登录主机2'
举例
# 删除单个用户
DROP USER 'zhangsan'@'127.0.0.1'
# 删除多个用户，逗号隔开即可
DROP USER 'zhangsan'@'127.0.0.1', 'zhangsan'@'localhost';
有人可能会说，既然用户数据是存储在mysql.user表中，那么我直接删除user表中的用户不也能删除用户吗？理论上是，但事实上，创建一个用户，不只是在mysql.user表中添加一条数据，还可能会在db表中添加(当向指定数据库授予权限时，就会在db表中添加一条记录)，而且我不知道其它表还会不会添加，所以，如果我们单独删除一个表中的数据是不行的，因为有可能会留下垃圾数据，而用drop命令，它会删除所有表中该用户的相关的数据。
修改用户名
把“[email protected]”改为“zhangsan@localhost”
RENAME USER [email protected] to zhangsan2@localhost;
修改密码(>mysql8.0)
使用ALTER USER语句修改密码
# 只修改密码
ALTER USER [email protected] identified by "123";
# 同时修改认证插件和密码
ALTER USER [email protected] IDENTIFIED WITH caching_sha2_password by '123456';
认证插件有3种：mysql_native_password、caching_sha2_password、sha256_password。
使用SET PASSWORD方式修改密码
SET PASSWORD for [email protected]='123456';
# 如果是修改当前用户密码，则可简写为
SET PASSWORD='123456'
这种方法不太推荐，貌似会有历史记录，密码是明文，容易被别人从历史记录中看见。
特别注意：
- 1、以前设置密码，是要用
PASSWORD('123456')来加密密码的，但是实测发现，我这边8.0.29版本，已经没有PASSWORD()函数了，所以你用了这个函数就会报错，它现在会自动用认证插件加密后再保存到mysql.user表中的authentication_string字段中； - 2、因为移除了
PASSWORD()函数这个原因，以前可以直接用update语句直接update password字段的，现在也不可以用那种方法了，而且现在根本就没有password字体了，而是改成了authentication_string字段了； - 3、现在mysql可以设置两个密码(更改密码的同时，保留原密码，以方便暂时过渡使用)，但这个可能用的少，我这里就不讲了。
查看当前用户
Information Functions
查看当前用户，有两种方法，其中current_user可以要括号也可以不要括号
select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看当前有哪个用户正在对哪个库进行什么操作
mysql> select user,host,db,command from information_schema.processlist;
+-----------------+-----------------+--------------------+---------+
| user | host | db | command |
+-----------------+-----------------+--------------------+---------+
| root | localhost:64508 | mysql | Sleep |
| root | localhost:64510 | NULL | Sleep |
| root | localhost:64840 | information_schema | Sleep |
| root | localhost:64898 | information_schema | Sleep |
| root | localhost | mysql | Query |
| event_scheduler | localhost | NULL | Daemon |
+-----------------+-----------------+--------------------+---------+
6 rows in set (0.00 sec)
另外顺便提两个类似的这些内置函数：查看当前选择的数据库和当前数据库版本
查看当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
查看当前版本
mysql> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.29 |
+-----------+
1 row in set (0.01 sec)
