第十一章《mysql用户与权限》

mysql关于用户的信息保存在mysql.user表当中，关于用户的权限主要是存储在mysql库中（user、db、host、tables_priv、columns_priv）这5个表中。

关于user表：
1.用户列：user表的用户列包括host、user、password（mysql5.7以后用authentication_string来代替password），分别表示允许登录的主机、用户名密码。
当我们登录mysql的时候，那么就要验证用户名、主机、以及密码是否和user表中的匹配了，这3个字段的值就是创建用户时保存的账户信息，修改用户密码时，实际就是修改user表的password字段。

2.权限列：权限列的字段决定了用户的权限，描述了在全局范围内允许定义数据和数据库进行的操作，包括查询权限，修改权限等普通权限，还包括关闭服务器、超级权限和加载用户等高级权限，普通权限用于操作数据库，高级权限用于数据库管理。
user表中对应的权限是针对所有用户数据库的这些字段值的类型为ENUM，可以取的值只能为Y和N，Y表示该用户有对应权限，N表示没有，查看user表可以发现普通用户的权限字段的默认值都是N，如果要修改权限，可以使用grant语句或者update语句更改user表的这些字段来修改用户对应的权限。

3.安全列：只有6个字段，其中两个时ssl相关的，2个是X509相关的，另外两个是授权插件相关的SSL用于加密，X509标准用于标识用户，plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用自带的授权验证机制验证用户身份。可以通过show variables like ‘have_openssl’语句来查询服务器是否支持ssl功能。

4.资源控制列：资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：
（1）max_questions：用户每小时允许执行的查询操作次数
（2）max_updates：用户每小时允许执行的更新操作次数
（3）max_connections：用户每小时允许执行的连接操作次数
（4）max_user_connections：用户允许同时建立连接的连接数

三、关于db表
用于决定哪些用户可以从哪些主机访问哪些数据库。包含在db表中的权限适用于这个表标识的数据库。

关于host表：
当你想在db表的范围之内扩展一个条目时，就会用到这个表。举例说明：如果某个db允许通过多个主机访问的话，那么超级用户就可以让sb表内将host列为空，然后将允许访问的主机名填充到host表中。

关于tables_priv表：该表结构与db表相似，不同之处就是它用于限制用户对表的权限，这个表还包含了其他字段类型，包括timestamp和grantor两个字段。用于存储时间戳和授权方

关于columns_priv：它提供的是针对某一个表的具体的某些列的权限

权限表的存取过程：
（1）先从user表中host、user、password这三个字段中判断连接的ip、用户名、密码是否存在表中，存在则通过身份验证；
（2）通过权限验证，进行权限分配时，按照user、db、tables_priv、columns_priv顺序进行分配，即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有的数据库的权限都为Y，将不在检查其他表，如果对应权限为N，则db表中检查此用户对应的具体数据库，得到对表中为Y的权限，如果db中对应的权限为N，则检查tables_priv表中对应的表的权限，如果tables_priv中对应的权限为N，再去检查columns_priv表中对应的字段权限。

创建用户：
1.CREATE USER ‘user_name’@‘hosts’ IDENTIFIED by [PASSWORD] ‘password’

mysql> create user ‘zhangsan’@‘localhost’ identified by ‘123.com’;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER ‘zhangsi’@‘localhost’ IDENTIFIED BY PASSWORD
‘*AC241830FFDDC8943AB31CBD47D758E79F7953EA’;
Query OK, 0 rows affected (0.00 sec)

2.使用grant语句创建新用户
GRANT privileges ON db.table TO ‘username’@‘hosts’ [ IDENTIFIED BY ‘PASSWORD’] [with grant option];

privileges ：表示用户的权限类型；
db.table：表示用户的权限所作用的数据库中的哪些表
identified by ‘password’： 给用户设置密码，不写这个选项代表密码为空；
with grant option：赋予新用户grant权限；

mysql> GRANT ALL PRIVILEGES ON . TO ‘zhangwu’@‘192.168.5.%’ IDENTIFIED
BY PASSWORD ‘*AC241830FFDDC8943AB31CBD47D758E79F7953EA’；
ALL PRIVILEGES：代表所有权限（不包括grant权限）

创建完成后并没有生效，我们需要刷新权限才能生效： FLUSH PRIVILEGES;
grant这种方式创建用户，会自动帮我们将密码进行hash加密；

3.我们通过直接修改user表来创建用户；
INSERT INTO mysql.user （host，user，password） values（‘hosts’，‘username’，password（‘password’））；

第一种和第三种方式默认用户没有权限，我们需要用grant语句来给新用户授权；
GRANT privileges ON db.table TO username@hosts；

mysql> SELECT * FROM mysql.user WHERE USER='zhangqi'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: zhangqi
              Password: *AC241830FFDDC8943AB31CBD47D758E79F7953EA
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: NULL
1 row in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhangqi'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.user WHERE USER='zhangqi'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: zhangqi
              Password: *AC241830FFDDC8943AB31CBD47D758E79F7953EA
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: 
 authentication_string: NULL
1 row in set (0.00 sec)

用户修改密码：
参考笔记

MySQL——修改root密码的4种方法
方法1： 用SET PASSWORD命令 
首先登录MySQL。 
格式：mysql> set password for 用户名@localhost = password('新密码'); 
例子：mysql> set password for root@localhost = password('123'); 

方法2：用mysqladmin 
格式：mysqladmin -u用户名 -p旧密码 password 新密码 
例子：mysqladmin -uroot -p123456 password 123 

方法3：用UPDATE直接编辑user表 
首先登录MySQL。 
mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='localhost'; 
mysql> flush privileges; 

方法4：忘记root密码的时候
Vim /etc/my.cnf
在[mysqld]下面添加一行 skip-grant-tables
然后重启，再登录mysql（此时密码为空）使用命令update mysql.user set password=password(‘密码’) where user=root host=’localhost’;
回到配置文件，删除刚刚添加的那行，再重启服务就可以

删除普通用户：
1.使用DROP USER语句删除

2.使用delete语句来删除user表里面的记录
delete from mysql.user where host= ‘localhost ’ and user=‘zhangsi’；

尽量使用DROP USER 来删除用户。

权限管理：
权限管理主要是对登录到mysql的用户继续宁权限验证，所有用户的权限都存储mysql库里面的权限表里，不合理的权限规划会给mysql服务器带来安全隐患，mysql权限系统的主要功能是验证连接到mysql服务器的客户端的主机以及用户，并赋予该用户在数据库中的SELECT\INSERT\IPDATE\DELETE等权限，账户权限信息被存储在mysql库中的user、db、table_priv等表中，在msyql启动时，服务器将这些数据库表中的权限信息的内容读入内存。

1、CREATE和 DROP权限，可以创建新数据库和表，或删除（移掉）己有数据库和表。如果将 MySQL数据库中的 DROP权限授予某用户，用户可以删掉 MySQL访问权限保存的数据库。
2、SELECT、 INSERT、 UPDATE和 DELETE权限允许在一个数据库现有的表上实施操作。
3、SELECT权限只有在它们真正从一个表中检索行时才被用到。
4、INDEX权限允许创建或删除索引， INDEX适用己有表。如果具有某个表的 CREATE权限，可以在 CREATE TABLE语句中包括索引定义。
5、ALTER权限，可以使用 ALTER TABLE来更改表的结构和重新命名表。
6、CREATE ROUTINE权限来创建保存的程序（函数和程序）， ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限用来执行保存的程序。
7、GRANT权限允许授权给其他用户。可用于数据库、表和保存的程序。
8、FILE权限给予用户使用 LOAD DATA INFILE和 SELECT… INTO OUTFILE语句读或写服务器上的文件，任何被授予 FILE权限的用户都能读或写 MySQL服务器上的任何文件。（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。 FILE权限允许用户在 MySQL服务器具有写权限的目录下创建新文件，但不能覆盖己有文件。

授权
授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授予权限。
授予的权限可以分为多个层级：
1、全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON . 和REVOKE ALL ON . 只授予和撤销全局权限。
2、数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db 和mysql.host 表中。GRANT ALL ON db_name 和 REVOKE ALL ON db_name.* 只授予和撤销数据库权限。
3、表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。GRANT ALL ON db_name.tb1_name 和 REVOKE ALL ON db_name.tb1_name只授予和撤销表权限。
4、列层级
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。
5·子程序层级
CREATE ROUTINE、 ALTER ROUTINE、 EXCUTE和 GRANT权限适用于己存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在 mysql.procs_priv表中。
在 MySQL中，必须是拥有 GRANT权限的用户才可以执行 GRANT语句。要使用 GRANT或 REVOKE,必须拥有 GRANT OPTION权限，并且必须用于正在授予或撤销的权限。
GRANT的语法如下：
GRANT priv_type[(columns)] [,priv_type[(columns)]]…
ON [object_type] table1,table2…
TO user [IDENTIFIED BY [password] ’password’]
[with grant option]
Object_type=TABLE | FUNCTION | PROCEDURE
GRANT OPTION取值：
|MAX_QUERIES_PER_HOUR count
|MAX_UPDATE_PER_HOUR count
|MAX_CONNECTIONS_PER_HOUR count
|MAX_USER_PER_HOUR count

GRANT OPTION的取值有5个，意义：
1、GRANT OPTION将自己的权限赋予其他的用户。
2、|MAX_QUERIES_PER_HOUR count设置每个小时可以执行count次查询
3、|MAX_UPDATE_PER_HOUR count设置每个小时可以执行count次更新
4、|MAX_CONNECTIONS_PER_HOUR count设置每个小时可以建立count个连接
5、|MAX_USER_PER_HOUR count 设置单个用户可以同时建立count个连接
创建权限并查看
mysql> GRANT SELECT,INSERT ON . TO ‘tom’@‘localhost’
-> IDENTIFIED BY ‘grantpwd’;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Host,User,Select_priv,Insert_priv,Grant_priv from mysql.user where user=‘tom’;
±———-±—–±————±————±———–+
| Host | User | Select_priv | Insert_priv | Grant_priv |
±———-±—–±————±————±———–+
| localhost | tom | Y | Y | N |
±———-±—–±————±————±———–+
1 row in set (0.01 sec)

收回权限
收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限，使用REVOKE收回权限之后，用户账号的记录将从db,host,user,tables_priv和columns_priv表中删除，但是用户账号记录仍然在user表中保存（删除user表中的账户记录，用DROP USER语句），
REVOKE语句有两种用法，第一种语法是收回所有用户的所有权限，此语法用于取消对已命名的用户的所有全局层级，数据库层级，表层级和列层级的权限，收回所有权限，我们的用户仍然有usage权限，这个是允许用户登录（连接mysql服务器）的权限。
REVOKE ALL PRIVILEGES /GRANT OPTION
FROM ‘user’@‘localhost’，‘user’@‘localhost’…
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM ‘zhangqi’@‘localhost’;
Query OK, 0 rows affected (0.00 sec)
第二种语法是长格式的REVOKE语句
REVOKE priv_type [(columns)],priv_type[(columns)]…
ON table1,table2…
FROM ‘user’@’localhost’;

mysql> REVOKE INSERT ON . FROM ‘tom’@‘localhost’;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Host,User,Select_priv,Insert_priv,Grant_priv from mysql.user where user=‘tom’;
±———-±—–±————±————±———–+
| Host | User | Select_priv | Insert_priv | Grant_priv |
±———-±—–±————±————±———–+
| localhost | tom | Y | N | N |
±———-±—–±————±————±———–+
1 row in set (0.00 sec)

查看权限
SHOW GRANTS语句可以显示指定用户的权限信息
SHOW GRANTS FOR ‘user’@‘host’
例如
mysql> SHOW GRANTS FOR ‘tom’@‘localhost’\G
*************************** 1. row ***************************
Grants for tom@localhost: GRANT SELECT ON . TO ‘tom’@‘localhost’ IDENTIFIED BY PASSWORD ‘*22CEBAE0504818FBD7906DF771DE7C3810D9F159’
1 row in set (0.00 sec)

用select语句查看user表中的各个权限字段以确定用户的权限信息
SELECT privileges_list FROM user WHERE user=’username’,host=’hostname’;
mysql> SELECT Host,User,Select_priv,Insert_priv,Grant_priv from mysql.user where user=‘tom’;
±———-±—–±————±————±———–+
| Host | User | Select_priv | Insert_priv | Grant_priv |
±———-±—–±————±————±———–+
| localhost | tom | Y | N | N |
±———-±—–±————±————±———–+
1 row in set (0.00 sec)