MySQL服务权限针对用户设置，用来控制及约束用户在数据库里对不同对象(库、表) 都能干啥，比如增删改查等。
数据库服务常见的权限管理定义设置：（采用具有的字符参数信息进行定义,并且权限是赋予数据库管理用户的）
1. 查看所有权限
数据库服务中可以授权的所有权限信息查看：
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 role | Server Admin | To create new roles |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
......
- 输出展示的信息表示内容：
| 序号 | 输出列 | 解释说明 |
|---|---|---|
| 01 | Privilege | 表示可以授权的所有权限信息展示 |
| 02 | Context | 表示设置的权限可以对数据库服务哪些资源进行操作 |
| 03 | Comment | 对相应的权限功能进行简单解释说明 |
- 核心重要授权权限总结：
| 序号 | 权限 | 授权资源 | 解释说明 |
|---|---|---|---|
| 01 | Select | Tables | 可以对表进行操作，查询表中数据信息 |
| 02 | Insert | Tables | 可以对表进行操作，插入表中数据信息 |
| 03 | Update | Tables | 可以对表进行操作，修改表中数据信息 |
| 04 | Delete | Tables | 可以对表进行操作，删除表中数据信息 |
| 05 | Alter | Tables | 可以对表进行操作，修改表中结构信息（元数据-属性） |
| 06 | Index | Tables | 可以对表进行操作，删除或创建表中的索引信息 |
| 07 | Create | Databases,Tables | 可以对表和库进行操作，用于创建数据库和表信息 |
| 08 | Drop | Databases,Tables | 可以对表和库进行操作，用于删除数据库和表信息 |
- ALL PRIVILEGES（也可省略为ALL），包含除了
grant option以外的所有权限。 在工作中授权时，授权用户应尽量授权最小的满足业务需求的权限， 而不是直接授权“ALL”。grant option是一个特殊的权限，就是允许管理员创建新用户的权限，此权限只建议给root@localhost用户。- 如果想要创建一个和root用户权限一样的用户的话：
# 创建admin用户，使之可以本机登录 mysql> create user admin@'localhost' identified by '123'; # 授权和管理员root一样的权限，注意结尾with grant option。 mysql> grant all on *.* to admin@'localhost' with grant option; mysql> flush privileges;
- 数据库服务中用户授权信息的存放位置：
mysql.授权表
# 切换进入到默认mysql数据库中
mysql> use mysql;
# 查看数据库中的信息
mysql> show tables;
# 查看mysql数据库中的表信息
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
38 rows in set (0.04 sec)
常见的授权表信息说明：
| 序号 | 授权表名称 | 解释说明 |
|---|---|---|
| 01 | user | 主要用于存储用户、主机域、密码、加密插件等信息，同时还存储了*.*级别的权限(全局权限) |
| 02 | db | 主要用于存储用户、主机域等信息，同时还存储了单库级别的权限(局部权限) |
| 03 | tables_priv | 主要用于存储用户、主机域等信息，同时还存储了单表级别的权限(局部权限) |
说明：以上授权表信息了解作用即可，不建议采用手工方式对授权表中的数据进行修改调整
2. 数据库用户授权
创建完用户信息，还需要对创建的用户进行授权，就是让不同用户拥有管理库和表对象的权限。
grant all on *.* to 'test'@'localhost';
grant | all | on | *.* | to | 'test'@'localhost'; |
|---|---|---|---|---|---|
| 授权命令 | 权限 | 固定用法 | 库.表 | 固定用法 | 用户@域 |
- 进入到数据库环境中，执行以下SQL语句进行设置：
# 对数据库和表同时进行授权方法
MySQL> grant all on *.*
-- 表示对所有的数据库和所有的表进行授权
MySQL> grant all on test.*
-- 表示对指定的test数据库中所有的表进行授权
MySQL> grant all on test.t1
-- 表示对指定的test数据库中指定的t1表进行授权
- 具体授权权限操作步骤：
# 创建test用户赋予所有库表权限，从localhost主机登录管理数据库，密码test123。
mysql> create user 'test'@'localhost' identified by 'test123';
mysql> grant all on *.* to 'test'@'localhost';
mysql> flush privileges;
# 创建blog用户赋予test库里表增和查权限，允许登录网段为10.0.0.0/24，密码是123。
mysql> create user blog@'10.0.0.0/24' identified by '123';
mysql> grant insert,select on test.* to blog@'10.0.0.0/24';
mysql> flush privileges;
# 创建用户zhangsan，具备mysql库user表增查权限，登录网段为10.0.0.0/24，密码是123。
mysql> create user zhangsan@'10.0.0.0/24' identified by '123';
mysql> grant insert,select on mysql.user to zhangsan@'10.0.0.0/24';
mysql> flush privileges;
3. 查看数据库用户权限
# 查看blog@10.0.0.0/24用户的权限
mysql> show grants for 'blog'@'10.0.0.0/24';
+------------------------------------------------------------+
| Grants for blog@10.0.0.0/24 |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `blog`@`10.0.0.0/24` |
| GRANT SELECT, INSERT ON `test`.* TO `blog`@`10.0.0.0/24` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
# 授权范围说明
*.* #<==授权[所有库.所有表],查mysql.user，里面存放全局授权
test.* #<==单库范围，test库里的所有表，查mysql.db，里面存放单库范围的用户授权
test.t1 #<==单表范围，test库里的t1表。查mysql.tables_priv，里面存放单表范围的用户授权
# 其他级别的权限
mysql> show tables from mysql like '%priv%';
+--------------------------+
| Tables_in_mysql (%priv%) |
+--------------------------+
| columns_priv | # 列级别
| procs_priv | # 存储过程
| proxies_priv | # 代理
| tables_priv |
+--------------------------+
4 rows in set (0.01 sec)
# 举例说明：
#所有库所有表：授权情况查看mysql.user表
# mysql.user表字段说明：https://www.qycn.com/xzx/article/15408.html
mysql> select * from mysql.user where user='test' and host='localhost'\G
#库级别：授权情况查看mysql.db表
mysql> select * from mysql.db where user='blog'\G
#表级别：授权情况查看mysql.tables_priv表
mysql> select * from mysql.tables_priv where user='zhangsan'\G
4. 回收数据库服务用户权限
- 语法：
revoke delete on *.* from test@'10.0.0.0/24';
revoke | delete | on | *.* | from | 'test'@'10.0.0.0/24'; |
|---|---|---|---|---|---|
| 授权命令 | 权限 | 固定用法 | 库.表 | 固定用法 | 用户@域 |
- 具体回收权限操作步骤
# 查看用户权限
mysql> show grants for 'blog'@'10.0.0.0/24';
+------------------------------------------------------------+
| Grants for blog@10.0.0.0/24 |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `blog`@`10.0.0.0/24` |
| GRANT SELECT, INSERT ON `test`.* TO `blog`@`10.0.0.0/24` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
# 回收blog用户的insert权限
mysql> revoke insert on test.* from 'blog'@'10.0.0.0/24';
Query OK, 0 rows affected (0.01 sec)
# 再次查看用户权限
mysql> show grants for 'blog'@'10.0.0.0/24';
+----------------------------------------------------+
| Grants for blog@10.0.0.0/24 |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `blog`@`10.0.0.0/24` |
| GRANT SELECT ON `test`.* TO `blog`@`10.0.0.0/24` |
+----------------------------------------------------+
2 rows in set (0.00 sec)
注意：回收和授权命令的
库.表、用户@主机域，要严格对应，否则收不回来。
5. 生产中grant授权说明
Web和App服务连接数据库的用户授权说明
grant授权是针对表内容授权，而不是针对库表本身授权，授权针对内容的select、insert、update、delete。
- 合理授权方式：
grant select,insert,update,delete on test.* to test@'10.0.0.%';
除了增删改查权限外的权限，用户用不到的权限，例如create，drop等都应该控制在DBA人员手里，授权最小的权限，才会拥有最大的安全。
- 以安装wordpess举例
安装时候授权：select,insert,update,delete,create,drop ；安装后回收create,drop，仅剩余select,insert,update,delete 。
注意：授予用户权限时有如下3条安全红线不要轻易跨过。
* 权限一定不用all，而是select、insert、update、delete权限；
* 不要授权具体库表，而应用test.*格式具体到库更为妥当；
* 主机域不能仅用%，而应用内网的IP段，即'172.16.1.%'格式；
结论：只要是应用服务器连接用户授权，99.99%都只授权
增删改查，更严格可以去掉delete权限，让开发使用update伪删除替代delete。 对于表.库对象的修改一定是管理员操作，不能授权应用服务或者交给开发管理。
生产中用户类型规范：
- 管理员: ALL
- 开发 : Select,Show databases,Show view
- 监控 : select, replication slave, client,supper
- 备份 : ALL
- 主从 : replication slave
- 业务 : insert, delete, update,select
6. 用户访问控制流程
MySQL权限控制主要集中在mysql库中5张表里：user，db，tables_priv，columns_priv，procs_priv。每次启动会直接加载到内存中。
- 用户连接检查阶段
- 用户连接时，MySQL首先从user表里匹配
host，user，password，匹配不到则拒绝用户连接。 - 检查mysql.user表
max_connections和max_user_connections，如果超过上限则拒绝连接。 - 检查user表的SSL安全连接，配置SSL，检查用户证书。
- 三个检查通过，MySQL server端与client端建立连接，当用户执行SQL语句时，执行SQL语句检查。
- 用户连接时，MySQL首先从user表里匹配
- 执行SQL语句时检查
- 从user表里检查
max_questions和max_updates，超过则拒绝执行SQL。 - 检查user表，看是否具有相应的全局性权限，如果有则执行，没有则继续下一步检查。
- 接着到db表，看是否具有数据库级别的权限，如果有则执行，没有则继续下一步检查。
- 最后到
tables_priv，columns_priv，procs_priv表里查看是否具有相应对象的权限。
- 从user表里检查
企业生产情况授权思路：企业生产库表授权一定要更小，最小的授权最大的安全，给业务用户常规授权，包含对内容的增删改查。
| 序号 | 权限 | 互联网应用 |
|---|---|---|
| 01 | SELECT 查询数据 | 访问网站页面时，表示浏览查看数据信息； |
| 02 | INSERT 插入数据 | 访问网站页面时，表示发表文章数据信息； |
| 03 | UPDATE 修改数据 | 访问网站页面时，表示修改文章数据信息； |
| 04 | DELETE 删除数据 | 访问网站页面时，表示删除文章数据信息； |
- 生产常规授权实践
# 生产常规授权实践
MySQL> create database bbs;
-- 创建数据库操作
MySQL> create user bbs@'10.0.0.%' identified by '123';
-- 创建管理数据库用户
MySQL> grant insert,select,update,delete on bbs.* to bbs@'10.0.0.%';
-- 进行管理用户授权
# 授权等价于root管理用户
MySQL> grant all on *.* to admin@'localhost' identified by '123123' with grant option;
MySQL> flush privileges;
7. 用户资源管理
7.1 密码过期时间
- 查看密码过期时间（两种方法）
mysql> select @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.01 sec)
mysql> show variables like '%lifetime%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
1 row in set (0.15 sec)
# ---- 默认值为0，表示禁用自动密码过期。
# ---- 正整数N，则表示允许的密码生存期，单位为天，因此必须每N天更改一次密码。
- 设置全局用户密码过期时间
# 设置用户密码过期时间都为100天（全局设置）
mysql> SET PERSIST default_password_lifetime = 100;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like '%lifetime%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 100 |
+---------------------------+-------+
1 row in set (0.00 sec)
#设置用户密码不过期（全局设置）
mysql> SET PERSIST default_password_lifetime = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%lifetime%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)
# 修改配置文件进行配置（全局配置）
[mysqld]
default_password_lifetime=100 # 100天过期
- 设置单个用户密码过期时间
# 设置单个用户90天过期
CREATE USER 'test'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; # 创建用户时设置
ALTER USER 'test'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; # 修改
# 设置单个用户不过期
CREATE USER 'test'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'test'@'localhost' PASSWORD EXPIRE NEVER;
7.2 密码重用
限制已使用的密码重复使用，可以根据密码修改次数以及密码使用时间间隔两个维度进行限制。同样的既可以设置一个全局策略，也可以对每个用户设置单独的策略。
- 修改配置文件方式，重启生效
[mysqld]
password_history=6
password_reuse_interval=365
- 在线修改并持久化
mysql> SET PERSIST password_history = 6;
Query OK, 0 rows affected (0.00 sec)
mysql> SET PERSIST password_reuse_interval = 365;
Query OK, 0 rows affected (0.00 sec)
- 创建用户时指定
mysql> create user 'test'@'localhost' PASSWORD HISTORY 6 PASSWORD REUSE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,Password_reuse_history,Password_reuse_time from mysql.user where user='test';
+------------+-----------+------------------------+---------------------+
| user | host | Password_reuse_history | Password_reuse_time |
+------------+-----------+------------------------+---------------------+
| test | localhost | 6 | 365 |
+------------+-----------+------------------------+---------------------+
1 row in set (0.00 sec)
- 修改现有的用户配置
mysql> alter user 'test'@'localhost' PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 60 DAY;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,Password_reuse_history,Password_reuse_time from mysql.user where user='test';
+------------+-----------+------------------------+---------------------+
| user | host | Password_reuse_history | Password_reuse_time |
+------------+-----------+------------------------+---------------------+
| test | localhost | 3 | 60 |
+------------+-----------+------------------------+---------------------+
1 row in set (0.01 sec)
- 修改成默认值(修改和全局一致的值)
mysql> alter user 'test'@'localhost' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,Password_reuse_history,Password_reuse_time from mysql.user where user='test';
+------------+-----------+------------------------+---------------------+
| user | host | Password_reuse_history | Password_reuse_time |
+------------+-----------+------------------------+---------------------+
| test | localhost | NULL | NULL |
+------------+-----------+------------------------+---------------------+
1 row in set (0.00 sec)
7.3 账号资源限制
MAX_QUERIES_PER_HOUR count # 用户每小时最多查询次数 MAX_UPDATES_PER_HOUR count # 用户每小时最多更新次数 MAX_CONNECTIONS_PER_HOUR count # 用户每小时内能建立的最大连接数 MAX_USER_CONNECTIONS count # 最大用户连接数
- 查看单个设置项
mysql> show variables like '%max_user_connections%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_user_connections | 0 | # 0表示不限制资源
+----------------------+-------+
1 row in set (0.00 sec)
- 创建用户时，添加设置项
# 格式
with
MAX_QUERIES_PER_HOUR n
MAX_UPDATES_PER_HOUR n
MAX_CONNECTIONS_PER_HOUR n
MAX_USER_CONNECTIONS n;
# 举例
mysql> create user test@'10.0.0.%' identified by '123' WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.06 sec)
# 验证
mysql> SELECT host,user,max_questions,max_updates,max_connections,max_user_connections FROM mysql.user WHERE user='test';
+-----------+--------+---------------+-------------+-----------------+----------------------+
| host | user | max_questions | max_updates | max_connections | max_user_connections |
+-----------+--------+---------------+-------------+-----------------+----------------------+
| 10.0.0.% | test | 20 | 20 | 5 | 2 |
+-----------+--------+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)
- 修改已有的用户限制
mysql> alter user test@'10.0.0.%' identified by '123' WITH MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
Query OK, 0 rows affected (0.09 sec)
# 或者
mysql> grant select on *.* to test@'10.0.0.%' identified by '123' WITH MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
Query OK, 0 rows affected (0.09 sec)
# 验证
mysql> SELECT host,user,max_questions,max_updates,max_connections,max_user_connections FROM mysql.user WHERE user='test';
+-----------+--------+---------------+-------------+-----------------+----------------------+
| host | user | max_questions | max_updates | max_connections | max_user_connections |
+-----------+--------+---------------+-------------+-----------------+----------------------+
| 10.0.0.% | test | 0 | 0 | 0 | 0 |
+-----------+--------+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)
账号资源限制除非是做精细化管理，否则在一般环境上比较鸡肋，创建账号使用默认设置即可。
8. 角色管理（8.0新增）
8.0增加了数据库服务中对用户的角色（role）支持，可以将常用的权限汇总打包为一个特定的role，再赋予给指定用户。
- 查看帮助
# 指定用户操作特定数据库时，具有共性相同的权限：分为运维权限（rw） 分为开发权限（ro）
mysql> help create role; # 查看role创建的方法帮助
- 创建指定的role信息
# 创建格式
CREATE ROLE 'admin','developer';
CREATE ROLE 'webapp'@'localhost';
# 创建role信息的语句结构信息，不加@信息表示角色白名单为%方式，实际还要根据具体用户定义。
# 创建两个不同的role
mysql> create role test_rw,test_r;
# 创建两个不同的role信息
-- test_rw: test.* rw(select,update,insert,delete)
-- test_r: test.* r(select)
# 进行role权限信息设定
mysql> grant select on test.* to test_r;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,update,insert,delete on test.* to test_rw;
Query OK, 0 rows affected (0.00 sec)
- 将指定role信息与相应的用户信息进行捆绑
# 创建需要捆绑role权限的用户信息
mysql> create user user01@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> create user user02@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
# 实现不同的role信息与不同的用户进行捆绑
mysql> grant test_r to user01@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant test_rw to user02@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
- 进行角色信息激活
mysql> set default role all to user01@'10.0.0.%';
mysql> set default role all to user02@'10.0.0.%';
# user01登录数据库，会显示对应激活的角色信息
mysql> select current_role();
+---------------------+
| current_role() |
+---------------------+
| `test_r`@`10.0.0.%` |
+---------------------+
1 row in set (0.00 sec)
# user02登录数据库，会显示对应激活的角色信息
mysql> select current_role();
+----------------------+
| current_role() |
+----------------------+
| `test_rw`@`10.0.0.%` |
+----------------------+
1 row in set (0.00 sec)
实现自动激活用户角色信息：
set global activate_all_roles_on_login=on;
3964
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
