您现在的位置是：网站首页> 编程资料编程资料
MySQL用户账户管理和权限管理深入讲解_Mysql_
2023-05-27
461人已围观
简介 MySQL用户账户管理和权限管理深入讲解_Mysql_
前言
MySQL 的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。
下面话不多说了，来一起看看详细的介绍吧
mysql 的权限体系大致分为5个层级:
全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。
数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。
子程序层级
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
这些权限信息存储在下面的系统表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
当用户连接进来，mysqld会通过上面的这些表对用户权限进行验证！
一、权限表的存取
在权限存取的两个过程中，系统会用到 “mysql” 数据库(安装 MySQL 时被创建，数据库名称叫“mysql”) 中 user、host 和 db 这3个最重要的权限表。
在这 3 个表中，最重要的表示 user 表，其次是 db 表，host 表在大多数情况下并不使用。
user 中的列主要分为 4 个部分：用户列、权限列、安全列和资源控制列。
通常用的最多的是用户列和权限列，其中权限列又分为普通权限和管理权限。普通权限用于数据库的操作，比如 select_priv、super_priv 等。
当用户进行连接时，权限表的存取过程有以下两个过程：
先从 user 表中的 host、user 和 password 这 3 个字段中判断连接的 IP、用户名、和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。
如果通过身份验证、则按照以下权限表的顺序得到数据库权限：user -> db -> tables_priv -> columns_priv。
在这几个权限表中，权限范围依次递减，全局权限覆盖局部权限。上面的第一阶段好理解，下面以一个例子来详细解释一下第二阶段。
为了方便测试，需要修改变量 sql_mode，不然会报错，如下
MySQL [(none)]> grant select on *.* to xxx@localhost; ERROR 1133 (42000): Can't find any matching row in the user table MySQL [(none)]> SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected, 2 warnings (0.07 sec) MySQL [(none)]> grant select on *.* to xxx@localhost; Query OK, 0 rows affected, 2 warnings (0.10 sec)
// sql_mode 默认值中有 NO_AUTO_CREATE_USER (防止GRANT自动创建新用户，除非还指定了密码) SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
1. 创建用户
xxx@localhost，并赋予所有数据库上的所有表的 select 权限
先查看user表显示的权限状态
MySQL [mysql]> select * from user where user="xxx" and host='localhost' \G; *************************** 1. row *************************** Host: localhost User: xxx Select_priv: Y 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: mysql_native_password authentication_string: password_expired: N password_last_changed: 2018-12-03 17:34:49 password_lifetime: NULL account_locked: N
再查看db表的权限状态
MySQL [mysql]> select * from db where user="xxx" and host='localhost' \G; Empty set (0.03 sec)
可以发现user表中Select_priv: Y，其他均为N
DB表中则无记录
也就是说，对所有数据库都具有相同的权限的用户并不需要记录到 db 表，而仅仅需要将 user 表中的 select_priv 改为 “Y” 即可。换句话说，user 表中的每个权限都代表了对所有数据库都有权限。
2. 将 xxx@localhost 上的权限改为只对 db1 数据库上所有表的 select 权限。
MySQL [mysql]> create database db1; Query OK, 1 row affected (0.01 sec) MySQL [mysql]> re^C MySQL [mysql]> revoke select on *.* from xxx@localhost; Query OK, 0 rows affected, 1 warning (0.06 sec) MySQL [mysql]> grant select on db1.* to xxx@localhost; Query OK, 0 rows affected, 1 warning (0.09 sec) MySQL [mysql]> select * from user where user='xxx'\G; *************************** 1. row *************************** Host: localhost User: xxx Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N
MySQL [mysql]> select * from db where user='xxx'\G; *************************** 1. row *************************** Host: localhost Db: db1 User: xxx Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N
这时候发现，user 表中的 select_priv 变为 “N” ，而 db 表中增加了 db 为 xxx 的一条记录。也就是说，当只授予部分数据库某些权限时，user 表中的相应权限列保持 “N”，而将具体的数据库权限写入 db 表。table 和 column 的权限机制和 db 类似。
3. tables_priv记录表权限
MySQL [db1]> create table t1(id int(10),name char(10)); Query OK, 0 rows affected (0.83 sec) MySQL [db1]> grant select on db1.t1 to mmm@localhost; Query OK, 0 rows affected, 2 warnings (0.06 sec) MySQL [mysql]> select * from user where user='mmm'\G; *************************** 1. row *************************** Host: localhost User: mmm Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ... MySQL [mysql]> select * from db where user='mmm'\G; Empty set (0.00 sec) MySQL [mysql]> select * from tables_priv where user='mmm'\G; *************************** 1. row *************************** Host: localhost Db: db1 User: mmm Table_name: t1 Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select Column_priv: 1 row in set (0.00 sec) ERROR: No query specified MySQL [mysql]> select * from columns_priv where user='mmm'\G; Empty set (0.00 sec)
可以看见tables_priv表中增加了一条记录，而在user db columns_priv三个表中没有记录
从上例可以看出，当用户通过权限认证，进行权限分配时，将按照 ==user -> db -> tables_priv -> columns_priv== 的顺序进行权限分配，即先检查全局权限表 user，如果 user 中对应 权限为 “Y”，则此用户对所有数据库的权限都为“Y”，将不再检查 db、tables_priv 和 columns_priv；如果为“N”，则到 db 表中检查此用户对应的具体数据库，并得到 db 中为 “Y”的权限；如果 db 中相应权限为 “N”，则再依次检查tables_priv 和 columns_priv 中的权限，如果所有的都为“N”，则判断为不具备权限。
二. 账户管理
授权 grant
grant不仅可以用来授权，还可以用来创建用户。
授权的语法：
grant 权限列表 on 库名.表名 to 用户@主机 identified by '密码';
创建用户 p1 ，权限为可以在所有数据库上执行所有权限，只能从本地进行连接
MySQL [mysql]> grant all privileges on *.* to p1@localhost; Query OK, 0 rows affected, 2 warnings (0.03 sec) MySQL [mysql]> select * from user where user='p1'\G *************************** 1. row *************************** Host: localhost User: p1 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: mysql_native_password authentication_string: password_expired: N password_last_changed: 2018-12-03 18:11:01 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)
除了 grant_priv 权限外，所有权限在user 表里面都是 “Y”。
增加对 p1 的 grant 权限
MySQL [mysql]> grant all privileges on *.* to p1@localhost with grant option; Query OK, 0 rows affected, 1 warning (0.03 sec) MySQL [mysql]> select * from user where user='p1'\G *************************** 1. row *************************** Host: localhost User: p1 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: Y 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: mysql_native_password authentication_string: password_expired: N password_last_changed: 2018-12-03 18:11:01 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)
设置密码赋予grant权限
MySQL [mysql]> grant all privileges on *.* to p1@localhost identified by '123' with grant option; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> select * from user where user='p1'\G *************************** 1. row *************************** Host: localhost User: p1 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: Y 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
相关内容
- windows 环境下 MySQL 8.0.13 免安装版配置教程_Mysql_
- MYSQL8.0.13免安装版配置教程实例详解_Mysql_
- Centos7.5安装mysql5.7.24二进制包方式部署_Mysql_
- 使用PDO防sql注入的原理分析_Mysql_
- 基于更新SQL语句理解MySQL锁定详解_Mysql_
- MySQL 8.0.13 下载安装教程图文详解_Mysql_
- Linux下安装mysql的教程详解_Mysql_
- MySQL5.6的zip包安装教程详解_Mysql_
- 如何批量生成MySQL不重复手机号大表实例代码_Mysql_
- mysql 8.0.12 安装图文教程_Mysql_
点击排行
本栏推荐
