您现在的位置是:首页 > 正文

mysql 系统库(一) —— 权限系统表与访问权限控制系统

2024-01-31 00:20:06阅读 0

系列文章参考自《MySQL 性能优化金字塔法则》,删除了书里重复说明和过于复杂的一些解释,完整版请参考原书。

前一篇有提到,information_schema 下的表都是非持久的,重启数据就会丢失,那么这些数据来自哪里呢?一部分来自ibdata共享表空间中的数据字典表的映射,而另一部分就来自 mysql 系统库下的持久表。

 

mysql 系统库中包含以下类型的表:

  • 权限系统表与访问权限控制系统
  • 数据库对象信息记录表
  • 统计信息记录表
  • 优化器成本记录表
  • 时区信息记录表
  • 复制信息记录表
  • 日志信息记录表

下面先来看第一部分,权限系统表与访问权限控制系统

 

一、 权限系统表

MySQL 访问权限系统表包含如下几张表:

  • user:包含用户、全局权限和其他非权限列表(安全配置选项和资源控制选项列)

  • db:数据库级别的权限表

  • tables_priv:表级别的权限表

  • columns_priv:列级权限表

  • procs_priv:存储过程和函数权限表

  • proxies_priv:代理用户权限表

 

1. user表

提供查询全局权限信息,可查询用户是否拥有该实例下所有数据库的相应全局权限。表中信息决定是否允许用户连接。

在user表中有任意一个权限列为Y的用户,就被认为拥有全局权限,所以用户在使用show databases或者使用information_schema的schemata表查询时,可以查询到所有数据库名称列表。

select * from mysql.user limit 1\G;

*************************** 1. row ***************************
              Host: %
              User: qfsys
       Select_priv: Y
       Insert_priv: N
       Update_priv: N
       Delete_priv: N
       Create_priv: N
         Drop_priv: N
       Reload_priv: Y
     Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
        Grant_priv: N
   References_priv: N
        Index_priv: N
        Alter_priv: N
      Show_db_priv: N
        Super_priv: Y
 Create_tmp_table_priv: N
  Lock_tables_priv: Y
      Execute_priv: N
   Repl_slave_priv: Y
  Repl_client_priv: Y
  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: *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F
  password_expired: N
 password_last_changed: 2017-07-01 14:37:32
 password_lifetime: NULL
    account_locked: N
1 row in set (0.00 sec)

表字段含义:

  • 范围列:host和user。

  • 权限列:Select_priv到Create_tablespace_priv,每列对应一个具体的权限,为Y代表有权限,为N代表没权限

  • 以下列官方称为安全列,与客户端与服务端之间的安全、加密通讯有关 
    * ssl_type:如果用户配置了使用加密ssl连接,则该字段记录用户使用的加密证书类型 
    * ssl_cipher:表示用于SSL连接握手中可能使用到的密码列表 
    * x509_issuer:x509证书相关字段 
    * x509_subject:x509证书相关字段 
    * plugin:该用户使用的密码认证插件名称 
    * authentication_string:表示用户密码的md5加密字符串 
    * password_expired:用户密码是否会过期,为Y表示用户密码会过期,为N表示用户密码永不过期 
    * password_last_changed:表示用户密码的最近一次修改时间,如果使用MySQL内建的认证插件(mysql_native_password or sha256_password)则该字段为非空,使用外部认证插件则该字段为空。
    * password_lifetime:如果password_expired字段为Y,则字段记录该用户密码距离过期剩余的天数;如果用户未单独指定该值,则使用default_password_lifetime的值代替;当password_lifetime为NULL且default_password_lifetime为0,或者password_lifetime为0时,表示该用户密码永不过期 
    * account_locked:代表用户当前状态是锁定状态还是处于激活可用状态

  • 以下列官方称为资源控制列,用于限制用户的一些访问资源 
    * max_questions:代表所有用户每小时的最大并发查询数 
    * max_updates:代表所有用户每小时最大并发更新次数 
    * max_connections:代表所有用户每小时的最大并发连接数 
    * max_user_connections:代表该用户每小时的最大并发连接数

 

2. db表

提供库级别对象权限信息, 可查询用户是否有相应权限操作指定数据库下的所有对象。

select * from db limit 1\G;

*************************** 1. row ***************************
             Host: localhost
               Db: performance_schema
             User: mysql.session
      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
1 row in set (0.00 sec)

表字段含义:

  • 范围列:host、db、user三列

  • xxx_priv:与user表的xxx_priv字段含义相同。与user表相比少了部分字段,说明少的字段对应的权限是全局范围的,不区分库表级别

 

3. tables_priv

表级别权限信息,代表用户对表或列有哪些权限。

select * from tables_priv;
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
| Host      | Db     | User          | Table_name | Grantor        | Timestamp           | Table_priv                                                                                   | Column_priv          |
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
| localhost | sys    | mysql.sys     | sys_config | root@localhost | 2017-07-01 14:31:32 | Select                                                                                       |                      |
| localhost | mysql  | mysql.session | user       | root@localhost | 2017-12-11 23:41:19 | Select                                                                                       |                      |
| %         | sbtest | xx            | sbtest1    | root@localhost | 0000-00-00 00:00:00 |                                                                                              | Select,Insert,Update |
| %         | sbtest | test_table    | sbtest1    | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |                      |
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
4 rows in set (0.00 sec)

表字段含义:

  • 范围列:host、db、user、Table_name

  • 权限列:Table_priv和Column_priv。Table_priv对应表级的Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger权限;Column_priv对应列级的Select,Insert,Update,References权限

  • Grantor:该用户权限被谁授予的

  • Timestamp:授权时间

 

4. columns_priv

列级别权限信息,用户可以使用哪些权限操作某个表的指定列。该表的Column_priv列含义与tables_priv表中的Column_priv列含义相同。

root@localhost : mysql 12:55:13> select * from columns_priv;
+------+--------+------+------------+-------------+---------------------+----------------------+
| Host | Db     | User | Table_name | Column_name | Timestamp           | Column_priv          |
+------+--------+------+------------+-------------+---------------------+----------------------+
| %    | sbtest | xx   | sbtest1    | id          | 0000-00-00 00:00:00 | Select,Insert,Update |
+------+--------+------+------------+-------------+---------------------+----------------------+
1 row in set (0.00 sec)

 

5. procs_priv

存储程序权限信息,用户可以使用哪些权限操作指定的存储程序(存储过程和函数)

select * from procs_priv; -- 该表权限数据为空,目前并未找到使表中填充数据的方法

表字段含义:

  • 范围列:host、db、user、Routine_name、Routine_type

  • 权限列:Proc_priv。是一个集合类型,代表存储程序的Execute,Alter Routine,Grant权限

  • Timestamp与Grantor含义同tables_priv表

 

6. proxies_priv

代理用户权限信息。用户可以充当哪些用户的代理,以及是否可以将PROXY权限授予其他用户。

如果一个用户需要将它的PROXY权限授予其他帐号,那么它必须在该表中有一行权限信息,且With_grant字段必须为1。

root@localhost : mysql 12:58:16> select * from proxies_priv;
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| Host      | User | Proxied_host | Proxied_user | With_grant | Grantor              | Timestamp           |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| localhost | root |              |              |          1 | boot@connecting host | 0000-00-00 00:00:00 |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
1 row in set (0.00 sec)

表字段含义:

  • Proxied_host和Proxied_user表示被授予proxy权限的用户host和用户名

  • 其他列的含义和tables_priv含义相同

 

注意权限表中一些字段有长度存储限制,具体如下:

  • Host, Proxied_host:长度限制 60个字符

  • User, Proxied_user:长度限制 32个字符

  • Password:长度限制 41个字符

  • Db:长度限制 64个字符

  • Table_name:长度限制 64个字符

  • Column_name:长度限制 64个字符

  • Routine_name:长度限制 64个字符

 

二、 访问权限控制系统

mysql 系统库提供了前文中的六张表存放不同权限范围的用户权限数据,这些表共同组成了MySQL 的访问权限系统。MySQL在启动时将这些表的内容读入内存,后续针对用户的访问控制决策基于权限表的内存副本实现。

MySQL权限系统的主要功能是对从给定主机连接到MySQL Server的用户进行身份验证,并校验该用户在该Server中的数据库对象访问的权限,另外还包括管理匿名用户访问和授予特定的MySQL权限的功能(如执行 LOAD DATA INFILE 语句和管理操作权限等)。

 

1. 访问控制阶段

当用户使用客户端程序连接到MySQL时,MySQL的访问控制分为如下两个阶段:

  • 连接阶段:Server根据身份标识(host+user)在mysql.user表中查询相关信息,确定接受还是拒绝该用户的连接。如果查询到了用户记录,则校验用户提供的帐号密码是否正确(包括密码是否已过期),如果密码不正确或已过期则拒绝连接。

  • 权限验证阶段:用户连接成功之后,Server会检查其他权限表,判断用户发出的每个sql是否有足够权限执行。

如果某用户在建立连接后,权限发生了变更,该用户执行下一条语句时这些权限变更不一定会立即生效。如果未生效需要执行flush privileges;语句。

 

2、MySQL 提供了哪些权限

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)

权限含义如下:

  • ALL或ALL PRIVILEGES:包含除grant option之外表中其他所有权限

  • ALTER:使用ALTER TABLE语句更改表的结构(ALTER TABLE语句除了该权限之外还需要CREATE和INSERT权限。ALTER TABLE RENAME语句需要旧表上的ALTER和DROP权限,新表上的CREATE和INSERT权限)。

  • ALTER ROUTINE:修改或删除存储过程或存储函数。

  • CREATE:创建库和表。

  • CREATE ROUTINE:创建存储过程或函数。

  • CREATE TABLESPACE:创建、修改、删除表空间文件和日志组文件。

  • CREATE TEMPORARY TABLES:创建临时表,创建后会话对该临时表有所有权限

  • CREATE USER:可使用ALTER USER、CREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES语句。

  • CREATE VIEW:创建视图

  • DELETE:从表中删除数据。

  • DROP:删除现有库、或表、或视图等对象。注意在分区表上使用ALTER TABLE ... DROP PARTITION语句必须要有表的DROP权限,执行TRUNCATE TABLE也需要DROP权限。

  • EVENT:创建、更改、删除或查看Event Scheduler事件。

  • EXECUTE:执行存储过程或函数。

  • FILE:执行LOAD DATA INFILE和SELECT ... INTO OUTFILE语句以及LOAD_FILE()来读取和写入Server主机上的文件。具有FILE权限的用户可读取datadir目录中的所有文件,还能在MySQL Server有写入权限的任何目录下创建新文件。5.7版本中,可以使用secure_file_priv系统变量限制FILE权限的读写目录。

  • GRANT OPTION:授予或回收其他用户或自己拥有的权限。

  • INDEX:创建或删除索引。INDEX权限适用于在已存在的表上使用CREATE INDEX语句,如果用户具有CREATE权限,则可以在CREATE TABLE语句中包含索引定义语句。

  • INSERT:向表中插入数据。ANALYZE TABLE、OPTIMIZE TABLE和REPAIR TABLE表维护语句也需要INSERT权限。

  • LOCK TABLES:对表显式加锁,持有表锁的用户对该表有读写权限,未持有表锁的用户对表的读写访问会被阻塞。

  • PROCESS:显示有关在Server内执行的线程信息。拥有该权限的用户在使用SHOW PROCESSLIST语句或mysqladmin processlist命令时可以查看到属于其他帐号的线程信息。另外,使用SHOW ENGINE语句以及查看information_schema 数据字典库中的相当一部分表也需要该权限。

  • PROXY:该权限使用户能够代理另一个用户。

  • REFERENCES:在创建外键约束时,需要用户具有父表的REFERENCES权限。

  • RELOAD:允许用户使用FLUSH语句及与FLUSH操作等效的mysqladmin子命令:flush-hosts,flush-logs,flush-privileges,flush-status,flush-tables,flush-threads,refresh和reload

  • REPLICATION CLIENT:可使用SHOW MASTER STATUS、SHOW SLAVE STATUS和SHOW BINARY LOGS语句。

  • REPLICATION SLAVE:从库服务器可连接到主库服务器并请求主库binlog 日志。

  • SELECT:该权限用于从数据库表中查询数据行记录。另外,UPDATE或DELETE语句使用where子句指定条件时也需要该列的SELECT权限。否则你会发现可以update更新全表,却不能使用where语句指定更新某些行。对基表或视图使用EXPLAIN语句也需要用户对表或视图具有该权限。

  • SHOW DATABASES:用于执行SHOW DATABASE语句,若没有此权限则只能看到具有对应访问权限的数据库列表

  • SHOW VIEW:用于执行SHOW CREATE VIEW语句。对视图使用EXPLAIN语句也需要此权限。

  • SHUTDOWN:用于执行SHUTDOWN语句、mysqladmin shutdown命令和mysql_shutdown() C API函数。

  • SUPER:用于以下操作和Server行为: 
    * 修改全局系统变量及某些会话级别系统变量
    * 对全局事务特征的更改(start transaction语句)。
    * 从库服务器用于执行启动和停止复制的语句,包括组复制,以及执行CHANGE MASTER TO和CHANGE REPLICATION FILTER语句 。
    * 执行PURGE BINARY LOGS和BINLOG语句 。
    * 如果视图或存储程序定义了DEFINER属性,则拥有SUPER权限的用户就算不是该视图或存储程序的创建者,仍然可以执行该视图或存储程序 。
    * 执行CREATE SERVER、ALTER SERVER和DROP SERVER语句 。
    * 执行mysqladmin debug命令 。
    * 用于InnoDB key自旋 。
    * 用于执行通过DES_ENCRYPT()函数启用读取DES密钥文件 。
    * 用于执行用户自定义函数时启用版本令牌 。
    * 超过最大连接数之后具有SUPER的帐户还可以执行的操作:
      1) 使用KILL语句或mysqladmin kill命令来终止属于其他帐户的线程。
      2) 即使Server总连接数达到max_connections系统变量定义的值,也会接受具有SUPER权限的用户一个额外的连接
      3) 即使Server启用了read_only系统变量,具有SUPER权限的用户仍然可以执行数据更新,还有GRANT和REVOKE语句
      4) SUPER客户端连接Server时,Server不执行init_connect系统变量指定的内容 。
      5) 处于脱机模式(已启用offline_mode系统变量)的Server不会中断具有SUPER权限用户的连接,且仍然接收具有SUPER权限用户的新连接请求 。

  • TRIGGER:创建、删除、执行或查看触发器。

  • UPDATE:更新表数据

  • USAGE:除能连接到数据库外基本无任何权限

 

参考

https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html

https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

https://dev.mysql.com/doc/refman/5.7/en/user-account-management.html

https://mp.weixin.qq.com/s/BWfiTmAYy7z9Ok6dbjkd6w

https://mp.weixin.qq.com/s/Bw6V0X-jZBPsic1xwJX39w

网站文章

  • java -jar 指定端口_Java可视化故障处理工具远程调试配置

    java -jar 指定端口_Java可视化故障处理工具远程调试配置

    本文介绍的几种工具适用于监控与分析Jvm堆内内存、栈、垃圾回收的状况。对于堆外内存的情况,只适用于DirectByteBuffer类,不适用于Unsafe类。JConsoleVisualVMJprof...

    2024-01-31 00:19:40
  • 机器人学习之项目- Project4:Home Sevice Robot

    机器人学习之项目- Project4:Home Sevice Robot

    这个项目的下一个任务是选择两个不同的目标,根据目标确定方向,以测试机器人达到的能力。我们将这些目标称为取货区和卸货区区域。该部分仅用于测试目的,以确保机器人能够在自主命令它向这些位置移动之前,可到达这...

    2024-01-31 00:19:33
  • 微服务访问安全设计方案全探索

    微服务访问安全设计方案全探索

    今天给大家带来的是 数人云工程师文权在高效运维线上群的分享实录。从传统单体应用架构到微服务架构,安全问题一直是人们关注的重点,文权与大家分享了关于微服务访问安全设计方案的探索与实践。 我们首先从传统单体应用架构下的访问安全设计说起,然后分析现代微服务架构下,访问安全涉及的原则,接着讨论目前常用的几种微服务架构下的访问安全设计方案。最后,详析Spring Cloud微服务架构下如何解决访问安...

    2024-01-31 00:19:27
  • 【计算机网络】TCP / IP 四层协议

    【计算机网络】TCP / IP 四层协议

    TCP / IP 是一个四层的体系结构,它从上至下包含应用层、运输层、网际层(强调本层可解决不同网络的互连问题)、网络接口层,在学习时,可结合OSI 和 TCP / IP 的特点,采用一种仅有五层的体系结构。

    2024-01-31 00:19:20
  • Android kotlin:json自动生成bean

    1. 取得json字符串 { "memo_id": 351, "items": [{ "language": "en",...

    2024-01-31 00:18:51
  • vue3.0中echarts实现中图地图的省份切换,并解决多次切换后地图卡死的情况

    vue3.0中echarts实现中图地图的省份切换,并解决多次切换后地图卡死的情况

    实现中国地图的省份切换

    2024-01-31 00:18:44
  • openssl运行出现 no OPENSSL_Applink解决办法

    openssl运行出现 no OPENSSL_Applink解决办法

    在使用openssl里面的test目录下ecdhtest.c运行时出错,如下: 百度到解决办法http://www.bubuko.com/infodetail-1082732.html,但是对我来说直接这么写错误还是没解决,原来是extern “C”语法写错了,语法参照这个博客:http://blog.csdn.net/daydreamingboy/article/details/877

    2024-01-31 00:18:37
  • 数据结构和算法:预排序遍历树算法(MPTT)

    数据结构和算法:预排序遍历树算法(MPTT)

    一、多级分类问题 在实际开发的过程中,会经常遇到多级分类的问题。譬如,导航栏、菜单、商品种类、多级联动、字典表等等的多级分类问题。这时可以新增一个 pid 字段进行数据关联,它本质上其实就是一棵树。树...

    2024-01-31 00:18:06
  • 华为matebook重装系统后,指纹模块失效,无法识别指纹设备,华为电脑管家无法解决

    Windows hello 指纹无法使用,重新安装指纹驱动没用。 解决方法,按如下顺序重新安装驱动,每次安装时如果需要重启,请立即重启,确保按顺序安装完成。 1-主板芯片组驱动程序 2-串口驱动程序 3-ME驱动程序(注意不是ME固件) 4-SGX驱动程序 5-指纹驱动程序 指纹驱动安装成功后,重启,即可正常使用指纹,设备管理器里可以看到生物识别设备。

    2024-01-31 00:18:01
  • Python + Requests + Scrapy + Fcntl: 双引擎下载小说(简述)

    Python + Requests + Fcntl: 双引擎下载小说前言关于双引擎原理流程库RequestsScrapyFcntl前言略关于双引擎原理流程库Requests导入包含requests、自...

    2024-01-31 00:17:53