MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db脚本初始化。存储账户权限信息的表主要有user、db、host、tables_priv、columns_priv和procs_priv。
权限表
user表
user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。
mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.01 sec)
mysql>
- 用户列
user表的用户列包括Host、User、authentication_string，分别表示主机名、用户名和密码。其中User和Host为User表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。 - 权限列
权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。user表中对应的权限是针对所有用户数据库的。这些字段值的类型为ENUM，可以取的值只能为Y和N，Y表示该用户有对应的权限；N表示用户没有对应的权限。可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限。 - 安全列
安全列有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。ssl用于加密；x509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。 资源控制列
- max_questions——用户每小时允许执行的查询操作次数。
- max_updates——用户每小时允许执行的更新操作次数。
- max_connections——用户每小时允许执行的连接操作次数。
- max_user_connections——用户允许同时建立的连接次数。
db表
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)
- 用户列
db表用户列有3个字段，分别是Host、User、Db，标识从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。 - db表中create_routine_priv和alter_routine_priv这两个字段表明用户是否有创建和修改存储过程的权限。user表中的权限是针对所有数据库的，如果希望用户只对某个数据库有操作权限，那么需要将user表中对应的权限设置为N，然后在db表中设置对应数据库的操作权限。
tables_priv表和columns_priv表
mysql> desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | varchar(288) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)
mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)
tables_priv表有8个字段
- Host、Db、User和Table_name 4个字段分表示主机名、数据库名、用户名和表名。
- Grantor表示修改该记录的用户。
- Timestamp字段表示修改该记录的时间。
- Table_priv表示对表的操作权限，包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter。
- Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。
- columns_priv表只有7个字段，分别是Host、Db、User、Table_name、Column_name、Timestamp、Column_priv。其中，Column_name用来指定对哪些数据列具有操作权限。
procs_priv表
procs_priv表可以对存储过程和存储函数设置操作权限。
mysql> desc procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Routine_name | char(64) | NO | PRI | | |
| Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| Grantor | varchar(288) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.01 sec)
procs_priv表包含8个字段:
- Host、Db和User字段分别表示主机名、数据库名和用户名。Routine_name表示存储过程或函数的名称。 -
- Routine_type表示存储过程或函数的类型。Routine_type字段有两个值，分别是FUNCTION和PROCEDURE：FUNCTION表示这是一个函数，PROCEDURE表示这是一个存储过程。
- Grantor是插入或修改该记录的用户。
- Proc_priv表示拥有的权限，包括Execute、Alter Routine、Grant 3种。
- Timestamp表示记录更新时间。
权限管理
权限管理主要是对登录到MySQL的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限。账户权限信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。
- CREATE和DROP权限，可以创建新数据库和表，或删除（移掉）已有数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户可以删掉MySQL访问权限保存的数据库。
- SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。
- SELECT权限只有在它们真正从一个表中检索行时才被用到。
- INDEX权限允许创建或删除索引，INDEX适用已有表。如果具有某个表的CREATE权限，可以在CREATE TABLE语句中包括索引定义。
- ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表。
- CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。
- GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。
- FILE权限给予用户使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件
授权
语法：
GRANT priv_type [ (columns) ] [，priv_type [ (columns)]] . . .
ON [object_type]
tablel, table2.... tablen
TO user [WITH GRANT OPTION]
object_ type = TABLEI | FUNCTION | PROCEDURE- priv_type参数表示权限类型；columns参数表示权限作用于哪些列上，不指定该参数，表示作用于整个表；
- table1,table2,…,tablen表示授予权限的列所在的表；
- object_type指定授权作用的对象类型包括TABLE（表）、FUNCTION（函数）和PROCEDURE（存储过程）；
- 当从旧版本的MySQL升级时，要使用object_tpye子句，必须升级授权表；user参数表示用户账户，由用户名和主机名构成，形式是“'username'@'hostname'”；
- IDENTIFIED BY参数用于设置密码。
WITH关键字后可以跟一个或多个with_option参数。
- GRANT OPTION：被授权的用户可以将这些权限赋予别的用户。
- MAX_QUERIES_PER_HOUR count：设置每个小时可以执行count次查询。
- MAX_UPDATES_PER_HOUR count：设置每小时可以执行count次更新。
- MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。
- MAX_USER_CONNECTIONS count：设置单个用户可以同时建立count个连接。
mysql> create user ronie@localhost identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for ronie@localhost;
+-------------------------------------------+
| Grants for ronie@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `ronie`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> grant all on *.* to 'ronie'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ronie@localhost \G;
*************************** 1. row ***************************
Grants for ronie@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `ronie`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for ronie@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `ronie`@`localhost` WITH GRANT OPTION
2 rows in set (0.00 sec)
ERROR:
No query specified收回权限
REVOKE语句，REVOKE语句必须和FROM语句一起使用。FROM语句指明需要收回权限的账户。
REVOKE priv_type [(columns)] [, priv_type [(columns)]] ...
ON table1, table2,…, tablen
FROM 'user'@'host'[, 'user'@ 'host' ...]mysql> show grants for ronie@localhost \G;
*************************** 1. row ***************************
Grants for ronie@localhost: GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `ronie`@`localhost` WITH GRANT OPTION
查看权限
语法：SHOW GRANTS FOR 'user'@'host';
mysql> show grants for ronie@localhost \G;
*************************** 1. row ***************************
Grants for ronie@localhost: GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `ronie`@`localhost` WITH GRANT OPTION
Comments | NOTHING