MySQL8.0 权限体系

原创 CuiHulong 2023-04-28

1932

数据库系统中存储着大量重要数据和各类敏感信息，同时又是多个不同媒介的(这里包含开发人员，管理员，应用等)共享数据服务中心。所以需要特定的账号，访问和操作对应的数据。通过访问控制机制来降低未经授权访问其数据、资源和系统的风险。

数据库权限系统也是安全体系的一部分。完善权限管理机制可以有效阻断恶意攻击，数据不被篡改，隐私泄露等情况发生。

对于软件中常用的权限控制模型有：访问控制模型(ACL)，角色的访问控制模型，会话和角色的访问控制，风险自适应访问控制，风险自适应控制等。目前MySQL8.0是权限控制是对于用户分配具体权限访问和角色的权限访问。灵活的权限机制，大大提高了安全系数。虽然提供方法，但还需规范化的设置。

MySQL权限体系

MySQL数据库的权限体系是，通过制定不同权限，给数据库对象和用户赋予不同的权限进行控制。满足绝大部分场景权限分配机制。

权限对象

那么MySQL的权限体系可以做到哪些对象的控制。针对最小授权原则，MySQL里对象的权限的粒度可以分为以下几个级别：

1.db：一般来说，一个数据库实例内部，会存在多个SCHEMA（库），对线上环境应用来说，大部分情况下，特定应用只使用特定的SCHEMA，在这种情况下，可以考虑针对指定的应用的数据库用户，授予整个schema的权限。
2.table:有时候会有这种情况，一个大应用下属多个应用，分别是多个应用方开发，如果授权是库级别的话，可能会导致不必要的数据泄漏，这种情况下，可以考虑，针对指定应用的数据库用户，只授予必要的相关的表的授权。
3.column: 出于保护数据的考虑，MySQL支持了针对数据库列的授权，如果有应用，只需要表的特定一些列的修改权限，比如用户表的居住信息和密码信息一般是由不同应用修改的，可以在这种情况下，只授予相关列的授权，列授权最多只能新增，修改，查询有权限列的数据，不能删除数据。
4.view，procedure，trigger，function，event：如果想提供数据出去，但不想被人直接看到表结构以及数据结构，可以考虑采用视图的方式提供数据。如：查看视图创建语句的权限与查询视图数据的权限是分开的，可以避免数据结构的外露丢失。
5.数据关联有一种特殊情况，有张中心表的数据非常重要，但如果有应用采用外键关联到这个表，可以通过碰撞检测的方式，盗窃出列的数据。针对这种情况，MYSQL的外键关联的建立，需要有单独的授权才可以建立，避免数据的丢失。

权限数据

MySQL将权限信息存储在mysql系统库的授予表中。相关权限信息主要存储在mysql.user、mysql.db、mysql.servers、mysql.table_priv、mysql.column_priv，mysql.proxies_priv和mysql.global_grants表中。

本身权限表信息数据量小，而且访问又比较频繁，所以MySQL在启动时就会将所有的权限信息都Load到内存中保存。并根据授予表的内存副本做出访问控制决策。所以手动修改了权限相关的表后，都需要通过执行"FLUSH PRIVILEGES" 命令重新加载MySQL的权限信息。

user表:对于允许的连接，用户表中授予的任何权限都表示用户的静态全局权限。此表中授予的任何权限都适用于服务器上的所有数据库。
global_grants表: 列出了当前为用户帐户分配的动态全局权限。对于每一行，范围列确定哪个用户具有在特权列中命名的特权。
db表: 确定哪些用户可以从哪些主机访问哪些数据库。特权列确定允许的操作。在数据库级别授予的特权适用于数据库和数据库中的所有对象，如表和存储的程序。
tables_priva和columns_priv表: 类似于db表，但更细粒度：应用于表和列级别，而不是数据库级别。在表级别授予的特权适用于表及其所有列。在列级别授予的特权仅适用于特定列。
procs_priva表: 适用于存储例程（存储过程和函数）。
proxys_priv表: 指哪些用户可以充当其他用户的代理，以及用户是否可以向其他用户授予代理权限。

权限都有哪些

先看下user表的权限列，就能了解大部分权限都包括哪些。user表中的权限相关字段大致可以分为几大类。如：用户列、权限列、安全列和资源控制列等。

还有权限大致又分为两大类，分别是高级管理权限和普通权限：

高级管理权限：主要对数据库进行管理。例如：关闭服务、超级权限，加载系统外文件，复制等权限；
普通权限：主要操作数据库。例如增，删，改，查，执行等权限。

mysql> SELECT  * FROM user limit 1\G;
*************************** 1. row ***************************
                    Host: %
                    User: dbadmin
             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
        Create_role_priv: Y
          Drop_role_priv: Y
。。。

字段说明：

定义的权限：

允许的静态权限：
允许的动态权限：

备注：
这些不同的权限最主要的目的，毕竟还是要让合适的人，只能看到应该看到的数据，只能操作应该能做的操作，阻止不合理，非法的数据查询操作。

授权管理

MySQL里通过GRANT,REVOKE命令实现授权管理，同时更新到内存结构中的权限信息。MySQL8.0版本数据库授权系统的实现，分为用户登录权限和以及每个授权级别单独的授权表。用户定义通过"user"@"host"方式赋予登录权限(USAGE权限)，而不是用户名认为是单独的用户，这点需要注意。

授权管理的语法，主要可以分为3个命令：

1. GRANT

GRANT是赋予权限命令，需要管理员权限。在早期版本里，GRANT语法也可以用于创建用户，所以GRANT语法也支持创建用户以及相关的选项。但到8.0版本需要把用户创建和授权分开执行。

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

权限赋予各种方式：

Global

mysql> GRANT ALL ON *.* TO 'tester'@'localhost';
mysql> GRANT SELECT, INSERT ON *.* TO 'tester'@'localhost';

Database Table Column

#指定库
mysql> GRANT ALL ON db1.* TO 'tester'@'localhost';

#指定表
mysql> GRANT SELECT, INSERT, UPDATE ON db1.table1 TO 'tester'@'localhost';

#指定column
mysql> GRANT SELECT (col1), INSERT (col1, col2) ON db3.table1 TO 'tester'@'localhost';

存储过程的

mysql> GRANT CREATE ROUTINE ON mydb.* TO 'tester'@'localhost';

mysql> GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'tester'@'localhost';

Proxy

mysql> GRANT PROXY ON 'proxyuser'@'localhost' TO 'tester'@'localhost';

Role

mysql>  GRANT 'role1', 'role2' TO 'tester1'@'localhost', 'tester2'@'localhost';

2. REVOKE

REVOKE 主要用于回收权限,这些特权和角色可以从用户帐户和角色中撤销。需要管理员权限。其基本语法与GRANT类似。

REVOKE [IF EXISTS]
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

权限回收各种方式：

mysql> REVOKE INSERT ON *.* FROM 'tester'@'localhost';

mysql> REVOKE 'role1', 'role2' FROM 'tester1'@'localhost', 'tester2'@'localhost';

mysql> REVOKE SELECT ON db1.* FROM 'tester'@'localhost';

IF EXISTS用法：
如果目标用户或角色存在，但由于任何原因没有发现分配给目标的特权或角色，则会发出警告，而不是错误；

mysql> REVOKE IF EXISTS SELECT ON db1.table FROM 'tester'@'127.0.0.1';

IGNORE UNKNOWN USER用法：
则该语句会对语句中指定但未找到的任何目标用户或角色发出警告；

mysql> REVOKE SELECT ON db1.table FROM 'tester'@'127.0.0.1' IGNORE UNKNOWN USER;

3. SHOW GRANTS

显示分配给MySQL用户帐户或角色的权限和角色。

SHOW GRANTS
    [FOR user_or_role
        [USING role [, role] ...]]

显示权限方式：

#指定用户权限显示
mysql> SHOW GRANTS FOR 'tester'@'localhost';
+------------------------------------------------------------------+
| Grants for jeffrey@localhost                                     |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tester`@`localhost`                      |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `tester`@`localhost` |
+------------------------------------------------------------------+

#显示当前用户和角色等方式
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR CURRENT_USER;
mysql> SHOW GRANTS FOR CURRENT_USER();

如何权限设计

对于这么多权限，应该如何设计运维权限体系。是来一个需求就赋予权限吗。
推荐采用最小授权法则，也就是，列，表，库级别，授予应用可以运行的最小授权，避免危险。对ddl等操作的执行需要专职的人员来执行。

对于数据库运维来说，按照安全，最小授权法则，可以按照不同的抽象角色设置权限。如：开发人员，开发DBA，运维DBA。避免开发运维过程中，未注意的错误调用导致的问题。

以下主要介绍的是，针对这些不同角色：

1.开发人员

这里定义的开发人员，主要是连接数据库程序的开发，对数据库的数据拥有增删查改（DML）的权限，但没有数据结构变更的权限。可以执行的操作进行分类。比如:

报表用户，一般只需要SELECT查询权限，而不必授予其他权限。
数据导入专用的用户，INSERT一般就足够了，不需要有DELETE的权限。
对于做了读写分离的用户，最推荐的方式是，对读请求，采用单独的用户访问数据库，只授予SELECT权限，避免开发过程中，未注意的错误调用导致的问题。

2.开发DBA

这里定义的开发DBA，主要是协助开发人员的dba，主要工作职责是对数据库表结构的变更，除此之外，也包含部分数据的一般变更，比如数据的批量操作，存储过程的创建这些。

生产环境一般变更DDL（库表结构变更）
生产环境的库表变更，主要指针对库表的DDL语句的执行。因为直接牵涉到数据安全以及业务连续性（特定操作有时候会导致业务停止服务），对权限授权的限制也需要相对更严格的限制。
如果存在CI（持续集成）系统，可以在发布阶段，测试审批完成后，按照在测试阶段相同的流程，由CI系统执行DDL而不是人工执行。避免授权外泄。如果没有CI系统，可以考虑对DDL设置单独的变更系统，由相关人审批后程序自动执行，避免人工参与。
如果无法避免人工操作，操作的时候，需要严格限制操作范围以及时间区间。一般来说，需要在操作的时候，授予需要操作的表的表结构变更权限，操作完成之后，马上回收授权。
数据批量操作（导入导出）
数据维护工作其之一就是批量数据操作，比如数据导入导出这些。导入导出对生产环境的主要干扰有以下一些：
1.批量操作的时候，会与正常线上操作产生资源争抢，影响生产环境性能。
2.导入导出很多时候涉及到数据库服务器文件的上传权限，控制不好会导致数据库的安全受到影响。
一般情况下，对于普通的批量操作，可以采用资源限制的方式，避免对生产数据库造成压力，当然，实际上需要与数据批量操作的速度达成一个平衡，既要避免对生产数据库的压力冲击，也要避免数据导入时间持续过长。
3.对于涉及到文件上传操作的导入导出，推荐的情况是，对文件做权限审查，避免可执行文件的上传。在具体授权的控制上，可以只在必要的时候授予权限，之后回收权限的方式，避免危险权限的外泄。
4.存储过程创建。前面提到的一点，为了安全，只对调用方开放调用存储过程的方式操控数据，对应过来，就需要有用户创建存储过程了，存储过程牵涉的，主要是商业逻辑的安全。

3.管理DBA

主要是对数据库实例的操作，包括备份恢复，拓扑结构变更（扩容，切换主库）这些。

备份恢复：
MYSQL并没有独立的备份恢复安全体系。物理备份的话，只需要有对数据文件，日志文件的文件系统访问权限，就可以执行备份恢复操作；逻辑备份的话，需要的是对数据的访问权限，不需要也做不到使用其他授权限制。
复制
mysql一般的部署结构都是一主多从，部署结构相关的变更指两个情况：一是从库扩容，也就是新增从库；二是主库变更，也就是在情况可控的情况下，采用一个从库作为主库替换原先的主库，原由一般是原先的主库有些无法简单解决的问题。
这两种操作，都需要有主从之间复制的授权，除此之外，也需要dba具有相关的操作权限。
mysql对这部分授权是单独管理的，dba可以单纯只获取到主从变更相关的授权来操作，避免丢失。
日常操作支持
开发DBA无法评估执行的一些操作。比如：影响数据库运行和性能的操作，大批量导入导出，ddl命令需要DBA进行审核之后执行。

总结

数据库权限体系在数据库管理体系中，是比较重要的一个环节。“授权属于预防，防患于未然“。但实际情况中，常常没有注意到的防护。比如：误删数据，删库，执行高危命令等。这时候，就需要一个很好的权限机制。

MySQL权限3个准则：

权限最小化原则配置所需用户；
在满足业务诉求的基础上赋予用户最少的权限；
影响全局的需要专业人员审核执行；

墨力计划 mysql

最后修改时间：2023-04-28 14:52:48

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

专家专栏 | 崔虎龙（共162篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。