MySQL教程 – 权限管理

更新记录
转载请注明出处。
2022年9月5日 发布。
2022年9月5日 从笔记迁移到博客。

MySQL中用户类型

普通用户
root（超级管理员）

MySQL中系统自带数据库

information_schema
mysql
performance_schema
sys

mysql数据库的作用

权限管理

user权限表

说明

表示用户信息和用户权限信息

作用

管理用户的全局DDL、DQL、DML、DCL权限
管理用户的密码

属性分类

• 第一部分：用户信息的属性
  主要包含HOST/USER/PASSWORD等属性，这是核心信息
• 第二部分：以priv结尾的属性
  用户DDL、DQL、DML、DCL的权限控制，值是Y或者N
• 第三部分：SSL相关的属性
  默认没有SSL，所以属性值为空
• 第四部分：用户资源限制属性
  主要包含对用户请求、连接数量等的限制，默认不限制

用户信息列

Host
User
password_expired
password_last_changed
password_lifetime
account_locked

权限列

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Show_db_priv
Super_priv
Create_tmp_table_priv
Lock_tables_priv
Execute_priv
Repl_slave_priv
Repl_client_priv
Create_view_priv
Show_view_priv
Create_routine_priv
Alter_routine_priv
Create_user_priv
Event_priv
Trigger_priv
Create_tablespace_priv

安全列

ssl_type
ssl_cipher
x509_issuer
x509_subject
plugin
authentication_string

资源控制列

max_questions
max_updates
max_connections
max_user_connections

图示

db权限表

说明

数据库的操作权限

用户列

Host
Db
User

权限列

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Create_tmp_table_priv
Lock_tables_priv
Create_view_priv
Show_view_priv
Create_routine_priv
Alter_routine_priv
Execute_priv
Event_priv
Trigger_priv

图示

host权限表

说明

主机的操作权限

主要属性

第一部分：用户、数据库信息

​ 主要包含Host、DB、User列

第二部分：权限列

​ 主要包含数据库相关的DDL/DQL/DML/DCL的权限

用户列

Host
Db

权限列

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Create_tem_table_priv
Lock_tables_priv
Create_view_priv
Show_view_priv
Create_routine_priv
Alter_routine_priv
Execute_priv
Trigger_priv

图示

tables_priv权限表

说明

表权限操作

主要字段

Host
Db
User
Table_name
Grantor
Timestamp
Table_priv
Column_priv

图示

columns_priv权限表

说明

列权限操作

主要字段

Host
Db
User
Table_name
Column_name
Timestamp
Column_priv

图示

proces_priv权限表

说明

存储过程和函数的权限

主要字段

Host
Db
User
Routine_name
Routine_type
Grantor
Proc_priv
Timestamp

图示

授予权限

语法 使用GRANT：

GRANT [ALL] 权限,权限,... ON 数据库.数据表 TO 用户名@主机 
WITH [限制选项];

备注：权限可以是上个小节中的权限名称的集合，EG：SELECT,UPDATE
限制选项可以是以下的一个或多个:

MAX QUERIES PER HOUR v      		//表示每小时可以查询v次
MAX UPDATES PER HOUR v         //表示每小时可以更新v次
MAX CONNECTIONS PER HOUR v     //表示每小时可以建立v次连接
MAX USER_CONNECTIONS    v      //表示该用户可以同时有的连接数量为v个
GRANT OPTION                   //表示该用户可以将同样的权限给别的用户

限制选项是可选的
选择所有数据库可以使用.
实例：

GRANT SELECT,UPDATE,INSERT ON *.* TO user@localhost IDENTIFIFD BY '123456' WITH GRANT OPTION;

回收权限
回收权限-REVOKE
语法 使用REVOKE：

REVOKE [ALL] 权限,权限,... ON 数据库.数据表 FROM '用户名'[@'主机'];

回收所有权限的语法：

REVOKE ALL PRIVILEGES,GRANT OPTION FROM '用户名'@'主机';

回收权限-操作user表

UPDATE mysql.user SET 权限名称 = '权限值' WHERE user= '用户名';

查看权限

语法一 使用SHOW GRANT：

SHOW GRANT FOR '用户名'[@'主机'];

语法二 查看USER表：

SELECT * FROM mysql.user;

权限控制流程

检测用户名、密码和主机是否匹配

检测是否有权限

流程：用户发起请求 -> 检测User表权限 -> 检测db表权限 -> 检测tables_priv表权限 -> 检测columns_priv表权限

权限对应的列