MySQL-05 用户管理
学习目标
- 权限表及其用法
- 账户管理
- 权限管理
- 访问控制
权限表
MySQL权限表存放在MySQL数据库里，由mysql_install_db脚本初始化。这些MySQL权限表分别为user、db、proxies_prv、servers、table_priv、 columns_priv、procs_priv表。
user表
user表记录允许连接到服务器上的用户账号信息，其中的权限是全局级的。
该表一共有45个字段，分为四种字段类型：
- 用户列：host、user、authentication_string。分别表示主机、用户名、密码。host和user为复合主键。登陆MySQL服务器必须匹配这三个字段，才允许用户建立连接；创建账户时保持了这三个字段的信息，修改密码需要修改authentication_string字段的值，使用SQL语句：
UPDATE mysql.user SET authentication_string=PASSWORD('密码') WHERE user='root';
- 权限列：数据类型为ENUM类型，Y表示拥有此权限，N表示没有权限。修改权限使用GRANT语句或者UPDATE语句更改表字段值进行修改.
- 安全列
plugin为使用的安全验证插件，默认为mysql内置验证安全插件。
使用命令查询mysql是否开启ssl支持。默认为禁用状态。
SHOW VARIABLES LIKE 'have_openssl';
- 资源控制列
MAX_QUERIES_PER_HOUR //每小时可发出的查询数 MAX_UPDATES_PER_HOUR //每小时可发出的更新数 MAX_CONNECTIONS_PER_HOUR //每小时可以链接服务器的次数 MAX_USER_CONNECTIONS //单一账户同时链接的数量
db表和servers表
db表存储了用户对某个数据库的操作权限，决定用户能够从哪个主机访问数据库。
servers表存储了主机和用户的对应关系，配合db表对给定主机上的数据库级操作权限进行进一步控制。
tables_priv表和columns_priv表
tableS_priv表用来对表设置操作权限。grantor表示修改该记录的用户。
columns_priv表用来对表中的某一列设置权限。
procs_priv表
该表对存储过程和函数设置操作权限。
账户管理
登陆和退出MySQL服务器
安装完成后mysql的超级用户默认密码为空，添加bin目录系统环境变量或者从wamp的控制台登录mysql服务器。
登陆：
完整语法格式：
mysql -h 主机 -u 用户名 -p密码 -P 端口号 数据库名 -e 执行SQL语句 //-p密码 中间不允许空格
登陆到myschool数据库：
登陆到myschool数据库并查询全部年级信息：
退出登录：mysql>exit
创建普通用户
创建用户的三种方式：
CREATE USER：在mysql.user表中添加用户。但是用户没有任何权限。
GRANT语句：添加用户的同时授予权限。
操作MySQL授权表：操作复杂。
1.使用CREATE USER创建用户（不推荐使用）
登陆数据库，创建用户tom、andy、mike，密码为123。
andy没有指定主机，默认为‘%’。（表示所有主机可以访问）
查询mysql.user表：
查询创建的三个用户，密码123被转换为：
所以在执行IDENTIFIED BY '密码'的时候，调用了PASSWORD()函数，生成明文密码的散列值。
使用‘123’密码的散列值设置sam用户的密码。使用IDENTIFIED BY PASSWORD。
CREATE USER 'sam'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257';
密码：
指定主机的表示方式：
"::1"、或者“127.0.0.1”、“localhost”：本机
“%”：所有主机
“192.168.10.%”：以192.168.10为网段的IP地址主机允许访问mysql服务器。
删除用户：
2.使用GRANT语句创建新用户（推荐使用）
使用CREATE USER创建的用户，需要使用GRANT命令进行授权。
使用GRANT语句创建的账户，在创建的时候可以同时进行授权，同时指定账户其他特性，包括安全连接、限制使用服务器资源等。
示例：创建tom用户，只能查询myschool.grade表数据。创建andy用户，只能向myschool.grade表添加和修改数据。创建sam用户，从任何一台主机登陆，对所有数据库和数据表都有CRUD权限。
#示例： # 创建tom用户，只能查询myschool.grade表数据。 # 创建andy用户，只能向myschool.grade表添加和修改数据。 # 创建sam用户，从任何一台主机登陆，对所有数据库和数据表都有CRUD权限。 GRANT SELECT ON myschool.grade TO 'tom'@'localhost' IDENTIFIED BY '123'; GRANT INSERT,UPDATE ON myschool.grade TO 'andy'@'localhost' IDENTIFIED BY '123'; GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'sam'@'%' IDENTIFIED BY '123';
对tom用户进行测试：
删除用户：
DROP USER 'tom'@'localhost'; DROP USER 'andy'@'localhost'; DROP USER 'sam'@'%';
3.直接操作user表（略，不推荐使用）
使用insert语句向mysql.user表插入用户。
root用户修改自身密码
1.使用mysqladmin命令指定新密码
语法格式（在命令行窗口运行）
mysqladmin -u 用户名 -h localhost -p password "新密码"
示例：
注意：新密码必须使用双引号；提示输入密码为旧密码。
2.修改mysql.user表
使用UPDATE语句修改root用户的密码。注意，5.7版本的mysql密码字段为：authentication_string，其余5.x版本的字段为password。
分为三步骤进行：第一步登陆；第二部UPDATE语句修改；第三部刷新权限表。注意在集成环境中SQL语句的字符串必须使用单引号，但是在控制台中单双引号都支持。
使用新密码登录：（-p后的密码不带空格）
3.使用SET语句修改密码
root用户登录到mysql服务器后使用SET命令修改：
修改完密码后需要刷新权限表或者重启mysql服务。
root用户修改普通用户密码
root用户拥有最高权限，可以使用SET、UPDATE和GRANT修改普通用户密码。
1.使用SET命令修改
语法格式：
SET PASSWORD FOR '用户名'@‘主机’ = password('新密码')
如果是普通用户登录mysql服务器自行修改，语法格式如下：
SET PASSWORD = password('新密码')
示例：使用root用户修改tom的密码为456，在使用tom自行登录修改为123
示例中，使用root登陆，修改tom密码后，tom自行登录，再改回原密码123。
2.使用UPDATE语句修改
例如把tom用户的密码修改为888，修改后需要刷新权限表或者重启mysql服务。
UPDATE mysql.user SET authentication_string=PASSWORD('888') WHERE user='tom' AND host='localhost';
FLUSH PRIVILEGES;
3.使用GRANT语句修改
例如把tom的密码修改为tom123
GRANT USAGE ON myschool.grade TO 'tom'@'localhost' IDENTIFIED BY 'tom123';
普通用户修改密码
普通用户登录mysql服务器后，使用以下命令修改：
SET PASSWORD = password('新密码')
root用户密码遗失处理方法
1.停止mysql服务
如果mysql有注册为系统服务，使用命令net stop mysql停止服务。wamp则直接停止所有服务。
2.使用--skip-grant-tables选项启动mysql
在Linux系统中，使用mysqld_safe --skip-grant-tables user=mysql启动。
初次使用，windows防火墙会提示是否允许该进程访问网络，选择允许。
3.另外打开一个命令行窗口，使用root空密码登录，执行UPDATE命令，更新user表的密码
4.刷新权限表
1-4步骤如下图所示：
关闭所有命令行窗口，启动wamp，登陆mysql。
注意：可能导致mysql无法完全启动（不影响mysql操作），需要重启wamp或者windows系统。
授权管理
MySQL权限系统的主要功能是验证给定一台主机的用户，是否具有对数据库进行CRUD操作的权限。
MySQL的权限
账户权限信息保持在user、db、services、tables_priv、columns_priv、proc_priv表中。
1.权限
GRANT（授权）和REVOKE（取消授权）语句所涉及的权限名称如下表所示：
|
限 |
权限级别 |
权限说明 |
|
CREATE |
数据库、表或索引 |
创建数据库、表或索引权限 |
|
DROP |
数据库或表 |
删除数据库或表权限 |
|
GRANT OPTION |
数据库、表或保存的程序 |
赋予权限选项 |
|
REFERENCES |
数据库或表 |
|
|
ALTER |
表 |
更改表，比如添加字段、索引等 |
|
DELETE |
表 |
删除数据权限 |
|
INDEX |
表 |
索引权限 |
|
INSERT |
表 |
插入权限 |
|
SELECT |
表 |
查询权限 |
|
UPDATE |
表 |
更新权限 |
|
CREATE VIEW |
视图 |
创建视图权限 |
|
SHOW VIEW |
视图 |
查看视图权限 |
|
ALTER ROUTINE |
存储过程 |
更改存储过程权限 |
|
CREATE ROUTINE |
存储过程 |
创建存储过程权限 |
|
EXECUTE |
存储过程 |
执行存储过程权限 |
|
FILE |
服务器主机上的文件访问 |
文件访问权限 |
|
CREATE TEMPORARY TABLES |
服务器管理 |
创建临时表权限 |
|
LOCK TABLES |
服务器管理 |
锁表权限 |
|
CREATE USER |
服务器管理 |
创建用户权限 |
|
PROCESS |
服务器管理 |
查看进程权限 |
|
RELOAD |
服务器管理 |
执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
|
REPLICATION CLIENT |
服务器管理 |
复制权限 |
|
REPLICATION SLAVE |
服务器管理 |
复制权限 |
|
SHOW DATABASES |
服务器管理 |
查看数据库权限 |
|
SHUTDOWN |
服务器管理 |
关闭数据库权限 |
|
SUPER |
服务器管理 |
执行kill线程权限 |
MySQL权限授权，主要是针对表和字段进行权限设定，如下表说明：
|
权限分布 |
可能的设置的权限 |
|
表权限 |
'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter' |
|
列权限 |
'Select', 'Insert', 'Update', 'References' |
|
过程权限 |
'Execute', 'Alter Routine', 'Grant' |
2.权限设定原则：
1.最小权限原则；
2.限制用户的登录主机；
3.删除没有密码的用户，删除、禁用root用户或者修改root用户密码；
4.每个用户的密码规则符合安全性要求；
5.定期清理未使用的用户；回收用户权限或者取消相应权限。
3.mysqladmin的使用
权限的实施一般采用SQL语句或者mysqladmin程序进行。
mysqladmin 工具的使用格式：
mysqladmin [OPTIONS] command command....
OPTIONS:
-u, --user=name 指定用户名 -h, --host=name 指定主机名 -p, --password 指定密码 -P, --port 指定端口
mysqladmin常用命令参数：
mysqladmin [OPTIONS] COMMAND COMMAND....
COMMAND：
create [DB_NAME]
drop [DB_NAME]
debug 打开调试日志并记录于error log中
status 输出服务器的基本状态信息
--sleep：status的子参数，多久刷新一次
--count：status的子参数，显示的批次
extended-status 显示扩展的状态信息，等于：SHOW GLOBAL STATUS;
flush-hosts 清空主机相关的缓存，包括：DNS解析缓存、连接错误次数过多而被拒绝访问mysqld的主机等
flush-logs 关闭日志，打开新日志对二进制、中继日志进行滚动
flush-privileges 刷新配置
flush-status 重置状态变量
flush-tables 关闭当前打开的表文件句柄
flush-threads 清空线程缓存池
kill 杀死指定的线程
password 修改指定用户的密码
ping 探测服务器是否在线
processlist 显示mysql线程列表
reload 相当于flush-privileges
refresh 相当于同时使用flush-logs和flush-hosts
shutdown 关闭mysql服务
start-slave 启动从服务器线程
stop-slave 关闭从服务器线程
variables 输出mysqld的服务器变量
version 显示mysql服务器版本
例如查看mysql的版本和mysql服务器内执行的线程信息：
授权
MySQL使用GRANT语句为用户授权，分别有5个层级的授权：全局、数据库、表、列、过程或者函数层级。
要使用GRANT或REVOKE，您必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。
GRANT语法格式：
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
object_type = TABLE | FUNCTION | PROCEDURE
with_option = GRANT OPTION #将自己的权限赋予其他用户
| MAX_QUERIES_PER_HOUR count #设置每小时可以执行count次查询
| MAX_UPDATES_PER_HOUR count #设置每小时可以执行count次更新
| MAX_CONNECTIONS_PER_HOUR count #设置每小时可以建立count个连接
| MAX_USER_CONNECTIONS count #设置单个用户可以同时建立count个连接
1.全局权限的授权和撤销
#授权全局权限 GRANT ALL ON *.* #撤销全局权限 REVOKE ALL ON *.*
2.数据库权限的授权和撤销
#授权数据库权限 GRANT ALL ON 数据库名.* #撤销数据库权限 REVOKE ALL ON 数据库名.*
3.数据库字段的授权和撤销
适用于给定表单一列。相关权限存储在mysql.columns_priv中；取消授权时，必须指定与被授权相同的列。
4.子程序的授权和撤销
CREATE ROUTINE， ALTER ROUTINE， EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
示例：创建新用户test，密码为123。test用户对所有数据库有查询、插入权限，并授予GRANT权限。
使用root用户登录，GRANT OPTION表示test用户可以创建其他用户，在user表中体现为grant_priv字段的值为Y。
收回权限
使用REVOKE语句取消用户的权限，权限会从db、servers、tables_priv、columns_priv表中删除，但是用户账号仍然存在于user表中，必须使用DROP语句删除账户记录。
1.收回用户的所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
2.收回指定权限
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
示例：使用REVOKE撤销test用户的INSERT权限。
REVOKE INSERT ON *.* FROM 'test'@'localhost';
执行该语句前后user表中test字段的变化：
查看用户权限
可以使用SHOW GRANTS和SELECT 权限字段 FROM user表 WHERE user=‘用户名’ AND host=‘主机’。
示例：查看test用户的权限
或者
访问控制
当客户端连接到服务器时，MySQL访问控制有两个阶段：
- 连接验证：连接到MySQL数据库服务器的客户端需要有一个有效的用户名和密码。此外，客户端连接的主机必须与MySQL授权表中的主机相匹配。与user表中的user、password和host进行验证。
- 请求验证：当连接成功建立后，对于客户端发出的每个语句，MySQL会检查客户端是否具有足够的权限来执行该特定语句。 MySQL能够检查数据库，表和字段级别的权限。分别与db、tables_priv、columns_priv表中的权限进行验证。
综合练习
上机练习1：root用户和admin用户管理
需求描述：
- 修改root的密码，不允许为空密码
- root密码遗忘，需要重置root密码
- 使用root用户登录，创建admin用户，密码为：*****，允许从互联网访问，对myschool数据库有全部权限，不允许创建其他用户，每小时最大连接数为50个
- 从user表、table_priv和columns_priv表中查询admin用户的权限信息
- 使用SHOW GRANTS语句查看admin的权限信息
- 使用admin用户登录MySQL服务器
- 使用admin用户分别向grade表中插入一条数据（grade为实训）并查询结果，查询student表中的学号、学生姓名、电话号码信息
- 退出admin用户登录，使用root用户登录，并收回admin的其他权限，只保留查询权。确认admin的权限。
- 删除admin的账户信息：取消授权后查询相关权限表；删除用户后查询user表。
上机练习2：myschool数据库和guest用户
需求描述：
- 创建账户guest，允许通过互联网访问数据库，密码为123321
- 授权guest对myschool.student表有SELECT和INSERT的权限，同时对address字段有UPDATE权限
- 修改guest的密码为abc123
- 刷新权限表
- 查看guest用户的权限
- 收回guest用户的INSERT权限
- 删除guest账户信息
本博客文章未经许可，禁止转载和商业用途！
如有疑问，请联系： 2083967667@qq.com
