Mysql高级篇学习总结２：用户管理、权限管理
1、用户管理
Mysql用户可以分为普通用户和root用户。root用户是超级管理员、拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。
Mysql提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出Mysql服务器、创建用户、删除用户、密码管理和权限管理等内容。
Mysql数据库的安全性需要通过账户管理来保证。
1.1 登录Mysql服务器
启动Mysql服务后，可以通过mysql命令来登录Mysql服务器，命令如下：
mysql -h hostname|hostIP -P port -u username -p databaseName -e "sql语句"
下面详细介绍命令中的参数：
- -h参数后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
- -P参数后面接mysql服务端口，通过该参数连接到指定的端口。mysql服务的默认端口是3306，不使用该参数时，自动连接到3306端口。
- -u参数后面接用户名，username为用户名。
- -p参数会在下一行提示输入密码，当然也可以在后面接着写密码。
- databaseName参数指明登录到哪一个数据库中。
- -e参数后面可以直接加sql语句。
举个例子：
mysql -u root -h localhost -P 3306 -p mysql -e "select host,user from user"
1.2 创建用户
在Mysql数据库中，官方推荐使用CREATE USER语句创建新用户。
使用CREATE USER语句来创建新用户时，必须拥有CREATE USER权限。每添加一个用户，CREATE USER语句会在MYSQL.user表中添加一条记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句就会返回一条错误。
CREATE USER语句的基本语法形式如下：
CREATE USER 用户名 [IDENTIFIED BY '密码'][, 用户名 [IDENTIFIED BY '密码']];
- 用户名表示新建用户的账户，它是由用户（user）和主机名（host）构成。如果不填主机名的话，默认主机名为’%’，也就是该用户可以从任意主机IP进行登录。
- []表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
- CREATE USER可以同时创建多个用户。
举个例子：
# 使用zhangsan用户正确输入密码，可以从任意主机登录
CREATE USER 'zhangsan' IDENTIFIED BY '123456';
# 使用zhangsan用户正确输入密码，只能从mysql的主机进行登录
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';
1.3 修改用户
修改用户名，举例如下：
UPDATE mysql.user SET USER='lisi' WHERE USER='zhangsan';
FLUSH PRIVILEGES;
1.4 删除用户
在Mysql数据库中，可以使用DROP USER 语句来删除普通用户。
DROP USER 语句的基本语法形式如下：
DROP USER user [, user ...];
其中，user参数是需要删除的用户，由用户的用户名（User）和主机名（Host）组成。可以同时删除多个用户，各用户之间用逗号隔开。
举例如下：
# 默认删除host为'%'的list
DROP USER 'lisi';
DROP USER 'zhangsan'@'localhost';
1.5 设置当前用户密码
适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。
１、使用ALTER USER 命令来修改当前用户密码
用户可以使用ALTER来修改自身密码，如下语句代表修改当前登录用户的密码。基本语法如下：
ALTER USER USER() IDENTIFIED BY 'new_password';
比如当前用户是root，修改自己的密码为‘123456’:
ALTER USER USER() IDENTIFIED BY '123456';
２、使用SET语句来修改当前用户密码
使用SET语句的语法如下：
SET PASSWORD='new_password';
该语句会自动将密码加密后再赋给当前用户。
1.6 修改其他用户密码
root用户不仅可以修改自己的密码，还可以修改其他普通用户的密码。root用户登录Mysql服务器后，可以通过ALTER语句和SET语句来修改普通用户的密码。
１、使用ALTER语句来修改普通用户的密码
可以使用ALTER USER语句来修改普通用户的密码，基本语法如下：
ALTER USER user [IDENTIFIED BY 'new_password']
[, user [IDENTIFIED BY 'new_password']];
举例如下：
ALTER USER 'lisi'@'localhost' IDENTIFIED BY '123456';
２、使用SET命令来修改普通用户的密码
使用SE语句的基本语法如下：
SET PASSWORD FOR 'username'@'hostnam'='new_password';
举例如下：
SET PASSWORD FOR 'lisi'@'localhost'='123456';
1.7 Mysql 8.0 密码管理
Mysql中记录使用过的历史密码，目前包含如下密码管理功能：
１）密码过期：要求定期修改密码。
２）密码重用限制：不允许使用旧密码。
３）密码强度评估：要求使用高强度的密码。
1.7.1 密码过期策略
在Mysql中，数据库管理员可以手动设置账号密码过期，也可以建立一个自动密码过期策略。
过期策略可以是全局的，也可以为每个账号设置单独的过期策略。
手动设置账号密码过期，可以使用如下语句：
ALTER USER user PASSWORD EXPIRE;
举例：
ALTER USER 'lisi'@'%' PASSWORD EXPIRE;
该语句将用户lisi的密码设为过期，sili用户仍然可以登录进入数据库，但无法进行查询。密码过期后，只有重新设置了新密码，才能正常使用。
手动设置指定时间过期方式１：全局
如果密码使用的时间大于允许的时间，服务器会自动设置为过期，不需要手动设置。
Mysql使用default_password_lifetime系统变量建立全局密码过期策略。
- 它的默认值是０，表示禁用自动密码过期；
- 它允许的值是正整数N，表示允许的密码生存期。密码必须每隔N天进行修改。
１）使用SQL语句更改该变量的值并持久化：
# 建立全局策略，设置密码每隔180天过期
SET PERSIST default_password_lifetime = 180;
２）配置文件my.cnf进行维护
[mysqld]
default_password_lifetime=180
手动设置指定时间过期方式２：单独设置
每隔账号既可沿用全局密码过期策略，也可单独设置策略。在CREATE_USER 和ALTER_USER 语句上加入PASSWORD EXPIRE选项可以实现单独设置策略。
举例如下：
# 设置lisi账号密码每隔90天过期
CREATE USER 'lisi'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'lisi'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
# 设置lisi账号密码永不过期
CREATE USER 'lisi'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'lisi'@'localhost' PASSWORD EXPIRE NEVER;
# 设置lisi账号沿用全局密码过期策略
CREATE USER 'lisi'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'lisi'@'localhost' PASSWORD EXPIRE DEFAULT;
1.7.2 密码重用策略
Mysql限制使用已用过的密码。重用限制策略基于密码更改的数量和使用的时间。重用策略可以是全局的，也可以为每个账号设置单独的策略。
账号的历史密码包含过去该账号所使用的密码。如果账号的密码限制基于密码更改的数量，那么新密码不能从最近限制的密码数量中选择。如果账号密码限制基于时间，那么新密码不能从规定时间内选择。
Mysql使用password_history和password_reuse_interval系统变量设置密码重用策略：
- password_history：规定密码重用的数量
- password_reuse_interval：规定密码重用的周期
手动设置密码重用方式１：全局
１）使用SQL
# 设置不能选择最近使用过的６个密码
SET PERSIST password_history=6;
# 设置不能选择最近一年内的密码
SET PERSIST password_reuse_interval = 365;
２）my.cnf配置文件
[mysqld]
password_history=6
password_reuse_interval=365
手动设置密码重用方式２：单独设置
每个账号可以沿用全局密码重用策略，也可以单独设置策略。
# 不能使用最近６个密码
CREATE USER 'lisi'@'localhost' PASSWORD HISTORY 6;
ALTER USER 'lisi'@'localhost' PASSWORD HISTORY 6;
# 不能使用最近365天内的密码
CREATE USER 'lisi'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'lisi'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
# 既不能使用最近６个密码，也不能使用最近365天内的密码
CREATE USER 'lisi'@'localhost'
PASSWORD HISTORY 6
PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'lisi'@'localhost'
PASSWORD HISTORY 6
PASSWORD REUSE INTERVAL 365 DAY;
# 沿用全局策略
CREATE USER 'lisi'@'localhost'
PASSWORD HISTOR DEFAULT P
ASSWORD REUSE INTERVAL DEFAULT;
ALTER USER 'lisi'@'localhost'
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT;
2、权限管理
关于Mysql的权限简单的理解就是Mysql允许你做你权利以内的事情，不可以越界。比如只允许你执行SELECT操作，那么你就不能执行UPDATE操作。只允许你从某台机器上连接Mysql，那么你就不能从除那台机器以外的其他机器连接Mysql。
2.1 权限列表
Mysql到底有哪些权限呢？
show privileges;
root用户的权限列表如下
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.00 sec)
2.2 授予权限的原则
权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：
１、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update, insert 或者delete权限。
２、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
３、为每个用户设置满足密码复杂度的密码。
４、定期清理不需要的用户，回收权限或者删除用户。
2.3 授予权限
给用户授权的方式有２种，分别是通过把角色赋予用户给用户授权和直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。
授权命令如下：
GRANT 权限１,权限２,...权限n ON 数据库名称.表名称 TO '用户名'@'用户地址' [IDENTIFIED BY '密码'];
如果发现没有该用户，则会直接新建一个用户。
举个栗子：
# 给lisi用户用本地命令行方式，授予dbtest1库下所有表的增删改查权限
GRANT SELECT, INSERT, DELETE, UPDATE ON dbtest1.* TO lisi@localhost;
# 授予通过任意IP登录的lisi用户，对所有库所有表的全部权限，密码设为123456。注意这里唯独不包括grant权限。
GRANT ALL PRIVILEGES ON *.* lisi@'%' IDENTIFIED BY '123456';
如果需要赋予包括GRANT的权限，添加参数“WITH GRANT OPTION”这个选项即可，表示该用户可以将自己拥有的权限授权给别人。经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项，导致该用户后来不能使用GRANT命令创建用户或者给其他用户授权。所以如果没有指定该选项的话，就算该用户拥有所有权限，那也只是他自己能使用，无法赋予给其他人他所拥有的权限。
2.4 查看权限
查看当前用户权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();
查看某用户的全局权限：
SHOW GRANTS FOR '用户名称'@'主机地址';
2.5 收回权限
收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。Mysql中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限后，用户账户的记录将从db, host, tables_priv, columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。
收回权限命令REVOKE：
REVOKE 权限1, 权限2, ..., 权限n 数据库名称.表名称 FROM 用户名@主机地址;
举例：
# 收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM lisi@'%';
# 收回mysql库下的所有表的增删改查权限
REVOKE SELECT, INSERT, UPDATE, DELETE ON mysql.* FROM lisi@localhost;
3、权限表
Mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。
Mysql数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有tables_priv表、columns_priv表和proc_priv表等。
在启动时，服务器将这些数据库表中的权限信息的内容读入内存。然后在执行所有命令之前，都会先判断下是否有对应的权限。
3.1 user表
用来记录：用户账号和权限信息。
可以看到user表的主键是User+Host，所以前面我们在赋予权限的时候，写的都是：用户名称@主机地址。只有这２个值才能唯一确定该用户是否有权限。
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 | |
| 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) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.03 sec)
3.2 db表
db表里记录了数据库层级的权限：
和user表一样，可以看到db表的主键是User+Host+Db。某个用户能不能查看某个数据库，就查看下db表，看看有没有该行数据就知道了。
mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | 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 | |
| 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)
3.3 tables_priv表columns_priv表
同理：
tables_priv表记录了表层级的权限，它的主键是：User+Host+Db+Table_name。
columns_priv表记录了表层级的权限，它的主键是：User+Host+Db+Table_name+Column_name。
mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(93) | 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 mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | 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)
3.4 proces_priv表
proces_priv表记录了存储的过程和函数的权限。
4、访问控制
正常情况下，并不希望每个用户都可以执行所有的数据库操作。当Mysql允许一个用户执行各种操作时，它将首先核实该用户向Mysql服务器发送的连接请求，然后确认该用户的请求是否被允许，这个过程称为Mysql的访问控制过程。Mysql的访问控制分为２个阶段：连接核实阶段和请求核实阶段。
4.1 连接核实阶段
当用户视图连接Mysql服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码、Mysql服务器接收到用户请求后，会使用user表中的host, user, authentication_string 这３个字段匹配客户端提供信息。如果这３个字段匹配找到数据，就进入阶段２；否则就没有通过，服务器会完全拒绝访问。
4.2 请求核实阶段
连接通过后，确认权限时，Mysql会再去检查db表、tables_priv、columns_priv表等，来查看是否有权限。
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
