- 1【论文笔记】RS-Mamba for Large Remote Sensing Image Dense Prediction（附Code）
- 2整理了适合新手的20个Python练手小程序
- 3从win10升级到win11后，安全中心没有病毒防护的解决办法
- 4Python爬虫request模块的get、post方法应用_python获取post登录后的页面内容
- 5树莓派4B之ubuntu系统配置UART
- 6AWVS 安装详细教程_awvs 客户端证书url
- 7在jenkins配置gitee，实现自动部署(二)（简易步骤&遇到的坑）_jenkins的gitee项目怎么导入2
- 8NAT的知识点和实现
- 9Web3 的正义理论（Web3 是我们打造更好互联网的机会）_web 3 convention benefits
- 10Qwen源码解析：finetune.py_qwen finetune
mysql 系统库（一） —— 权限系统表与访问权限控制系统_权限和表的完整性控制的异同
赞
踩
系列文章参考自《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
