MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限。MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，普通用户只拥有被授予的各种权限。
12.1、权限表
MySQL服务器通过权限来控制用户对数据库的访问,权限表存放在MySQL数据库中,由MySQL_install_db脚本初始化.存储账户权限信息表主要有：user,db,host，table_priv、columns_priv、procs_priv。
user表
user表是MySQL中最重要的一个权限表，记录允许连接到服务器的帐号信息，里面的权限是全局级的。
mysql> use mysql;
Database changed
mysql> DESC user;
+------------------------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| 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 | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type |enum('','ANY','X509','SPECIFIED')| NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| 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 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+------------------------------+------+-----+---------+-------+- 用户列
user表的用户列包括Host、User、Password。其中User和Host为User表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配User表中的对应字段，只有3个值都匹配的时候，才允许连接建立。
- 权限列
权限列的字段决定了用户的权限，描述了全范围内允许对数据和数据库进行的操作。user表中对应的权限针对多有用户数据库的。这些字段值的类型为ENUM，可以取的值只有Y和N，Y表示该用户有对应权限，N表示用户没有对应的权限。
- 安全列
安全列只有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。ssl用于加密；x509标准可用于标识用户；Plugin字段标识可用于验证用户身份的插件。
- 资源控制列
资源控制列的字段用来限制用户使用的资源，包含4各字段：
max_questions：用户每小时允许执行的查询操作次数
max_updates：用户每小时允许执行的更新操作次数
max_connections：用户每小时允许执行的连接操作数
max_user_connections：用户允许同时建立的连接次数
db表和host表
db表和host表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。host表中存储了某个主机对数据库的操作权限。
mysql> USE mysql;
Database changed
mysql> DESC db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | 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 | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql> DESC host;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | 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 | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.00 sec)- 用户列
db表用户列有3个字段，分别是Host、User、Db，标识从某主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。
host表不存储用户名称，用户列只有两个字段，分别是Host和Db，表示从某个主机连接的用户对某个数据库的操作权限，其逐渐包括Host和Db两个字段。
- 权限列
表中create_routine_priv和alter_routine_priv这两个字段表名用户是否有创建和修改存储过程的权限。
user表中的权限是针对所有数据库的，当希望用户只对某个数据库有操作权限，需要将user表中对应的权限设置为N,然后在db表中设置对应数据库的操作权限。
table_priv表和columns_priv表
table_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。
mysql> DESC tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(77) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
mysql> DESC columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)Host、Db、User、Table_name：分别表示主机名、数据库名、用户名和表名 |
Grantor：修改该记录的用户 |
Timestamp：修改该记录的时间 |
Table_priv：对表的操作权限 |
Column_priv：对表中的列的操作权限 |
procs_priv表
procs_priv表可以对存储过程和存储函数设置操作权限。
mysql> DESC procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Routine_name | char(64) | NO | PRI | | |
| Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| Grantor | char(77) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)Host、Db、User：分别表示主机名、数据库名、用户名 |
Routine_name：存储过程或函数的名称 |
Routine_type：存储过程或函数的类型 |
Grantor：插入或修改该记录的用户 |
Prov_priv：拥有的权限 |
Timestamp：记录更新时间 |
12.2、账户管理
MySQL提供许多语句用来管理用户账号，这些语句用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。
登录和退出MySQL服务器
使用root用户登录到本地mysql服务器的test库中
[root@mylinux ~]# mysql -uroot -p -hlocalhost test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.56-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>使用root用户登录到本地mysql服务器的test库中，执行一条查询语句
[root@mylinux ~]# mysql -uroot -p -hlocalhost test -e "DESC person;"
Enter password:
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(40) | NO | | | |
| age | int(11) | NO | | 0 | |
| info | char(50) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+新建普通用户
- 使用CREATE USER语句创建新用户
在执行CREATE USER或CRANT与举止，服务器会修改相应的用户权限表，添加或修改用户及权限。CREATE USER语法格式为：
CREATE USER auth_option [, auth_option] ...
auth_option:
user@host
[
IDENTIFIED BY PASSWORD 'hash_string'
IDENTIFIED WITH auth_plugin AS 'hash_string'
]user：创建的用户名称 |
host：允许登录的用户主机名称 |
IDENTIFIED BY：用来设置用户的密码 |
PASSWORD:使用哈希值设置密码 |
hash_string：用户登录时使用的普通明文密码 |
IDENTIFIED WITH：为用户指定一个身份验证插件 |
auth_plugin：插件的名称，可以是带单引号的字符串，或者带引号的字符串 |
使用CREATE USER创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)- 使用CRANT创建新用户
GRANT语句是添加新用户并授权它们访问MySQL对象的首选方法，其语法格式为：
GRANT privileges ON db.table
TO user@host [IDENTIFIED BY 'password'] [,user [IDENTIFIED BY 'password']]
[WITH GRANT OPTION]使用GRANT语句创建一个新的用户testUser，密码为testpwd。
用户 testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限
mysql> GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host,User,Select_priv,Update_priv FROM mysql.user where user='testUser';
+-----------+----------+-------------+-------------+
| Host | User | Select_priv | Update_priv |
+-----------+----------+-------------+-------------+
| localhost | testUser | Y | Y |
+-----------+----------+-------------+-------------+
1 row in set (0.00 sec)- 直接操作MySQL用户表
使用INSERT语句创建新用户的基本语法为：
INSERT INTO mysql.user(Host,User,Password,[privilegelist])
VALUES('localhost','username',PASSWORD('password'),privilegevaluelist)使用INSERT创建一个新账户，其用户名称为customer1，主机名称为localhost，密码为customer1：
mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','customer1',PASSWORD('cust
omer1'));
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1364 | Field 'ssl_cipher' doesn't have a default value |
| Warning | 1364 | Field 'x509_issuer' doesn't have a default value |
| Warning | 1364 | Field 'x509_subject' doesn't have a default value |
+---------+------+---------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT host,user,password FROM user;
+-----------+-----------+-------------------------------------------+
| host | user | password |
+-----------+-----------+-------------------------------------------+
| localhost | root | |
| mylinux | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| mylinux | | |
| localhost | jeffrey | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| localhost | testUser | *22CBF14EBDE8814586FF12332FA2B6023A7603BB |
| localhost | customer1 | *73DA97747611396FD898E4A7E42B1097B0780646 |
+-----------+-----------+-------------------------------------------+
9 rows in set (0.00 sec)由于ssl_cipher、x509_issuer、x509_subject这三个字段在user表中没有定义默认值，所以新用户创建失败。
删除普通用户
使用DROP USER删除用户'jeffrey'@'localhost'
mysql> DROP USER 'jeffrey'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT host,user,password FROM user;
+-----------+-----------+-------------------------------------------+
| host | user | password |
+-----------+-----------+-------------------------------------------+
| localhost | root | |
| mylinux | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| mylinux | | |
| localhost | testUser | *22CBF14EBDE8814586FF12332FA2B6023A7603BB |
| localhost | customer1 | *73DA97747611396FD898E4A7E42B1097B0780646 |
+-----------+-----------+-------------------------------------------+
8 rows in set (0.00 sec)使用DELETE删除用户'customer1'@'localhost'
mysql> DELETE FROM mysql.user WHERE host='localhost' and user='customer1';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT host,user,password FROM user;
+-----------+----------+-------------------------------------------+
| host | user | password |
+-----------+----------+-------------------------------------------+
| localhost | root | |
| mylinux | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| mylinux | | |
| localhost | testUser | *22CBF14EBDE8814586FF12332FA2B6023A7603BB |
+-----------+----------+-------------------------------------------+
7 rows in set (0.00 sec)root用户修改自己的密码
- 使用mysqladmin命令行指定新密码
mysqladmin命令的基本语法格式为：
mysqladmin -u username -h localhost -p password "newpwd"使用mysqladmin将root用户的密码修改为“123456”
[root@mylinux ~]# mysqladmin -u root -p password "123456" Enter password:
- 修改mysql数据库的user表
使用UPDATE语句修改root用户密码的语句为：
UPDATE mysql.user set password=PASSWORD('ROOTPWD') WHERE User='root' AND Host='localhost'使用UPDATE语句将root用户的密码修改为“rootpwd2”：
mysql> UPDATE mysql.user set Password=password("rootpwd2")
-> WHERE User="root" and Host="localhost";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)- 使用SET语句修改root用户密码
SET PASSWORD语句可以用来重新设置其他用户的登录密码或自己使用的账号密码，语法格式为：
SET PASSWORD=PASSWORD('ROOTPWD')使用SET语句将root用户的密码修改为“rootpwd3”：
mysql> SET PASSWORD=password("rootpwd3");
Query OK, 0 rows affected (0.00 sec)root用户修改普通用户密码
使用SET语句将testUser用户的密码修改为“newpwd”：
SET PASSWORD FOR 'testUser'@'localhost'=password("newpwd");使用UPDATE语句将testUser用户的密码修改为“newpwd2”：
UPDATE mysql.user set Password=PASSWORD("newpwd2")
WHERE User="testUser" and Host="localhost";使用GRANT语句将testUser用户的密码修改为“newpwd3”：
GRANT USAGE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'newpwd3';普通用户修改密码
普通用户登录MySQL服务器后，通过SET语句设置自己的密码
SET PASSWORD = PASSWORD("newpassword")testUser用户使用SET语句将自身的密码修改为“newpwd4”：
SET PASSWORD = PASSWORD("newpwd4");root用户密码丢失的解决办法
1、使用--skip-grant-tables选项启动MySQL服务 2、使用root用户登录，重新设置密码 3、加载权限表，使用FLUSH PRIVILEGES语句 |
12.3、权限管理
权限管理主要是对登录到MySQL的用户进行权限验证。
MySQL的各种权限
账户权限信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和proc_priv表中。
授权
授权就是为某个用户授予某个权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRRANT
语句授予权限。授权可以分为多个层级：
全局层级：适用于给定一个服务器中的所有数据库 数据库层级：适用于给定一个数据库中的所有目标 表层级：适用于给定一个表中所有列 列层级：适用于给定表中单独一列 子程序层级：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、和GRANT权限适用于已存储的子程序 |
使用GRANT语句创建一个新的用户grantUser，密码为“grantpwd”。用户grantUser对所有的数据有查询、插入权限，并授于GRANT权限。
mysql> GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost' IDENTIFIED BY 'grantpwd' WIT
H GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host,User,Select_priv,Insert_priv, Grant_priv FROM mysql.user where user='g
rantUser';
+-----------+-----------+-------------+-------------+------------+
| Host | User | Select_priv | Insert_priv | Grant_priv |
+-----------+-----------+-------------+-------------+------------+
| localhost | grantUser | Y | Y | Y |
+-----------+-----------+-------------+-------------+------------+
1 row in set (0.00 sec)收回权限
使用REVOKE语句取消用户testUser的更新权限。
mysql> REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host,User,Select_priv,Update_priv,Grant_priv FROM mysql.user where user='tes
tUser';
+-----------+----------+-------------+-------------+------------+
| Host | User | Select_priv | Update_priv | Grant_priv |
+-----------+----------+-------------+-------------+------------+
| localhost | testUser | Y | N | N |
+-----------+----------+-------------+-------------+------------+
1 row in set (0.00 sec)查看权限
使用SHOW GRANTS语句查询用户testUser的权限信息
mysql> SHOW GRANTS FOR 'testUser'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for testUser@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'testUser'@'localhost' IDENTIFIED BY PASSWORD '*22CBF14EBDE8814586FF12332FA2B6023A7603BB' |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)12.4、访问控制
当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的请求，然后确认用户的操作请求是否被允许。MySQL的访问控制分为两个阶段：连接核实请求，请求核实阶段。
当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝用户。MySQL中使用user表中的3个字段(Host、User、Password)执行身份检查，只有3个字段同时匹配，才接受连接。
建立连接后，服务器进入访问控制的阶段2.对在此连接上的请求，服务器检查用户要执行的操作，然后检查是否有足够权限来执行它。
https://blog.51cto.com/yinsuifeng/1954229
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
