背景

OS Version : rhel 6.4
MySQL Version : 5.6.31

权限

权限列表

Privilege	Column	Context
CREATE	Create_priv	databases, tables, or indexes
DROP	Drop_priv	database, tables, or views
GRANT OPTION	Grant_priv	database, tables, or stored routines
LOCK TABLES	lock_tables_priv	databases
REFERENCES	References_priv	databases or tables, columns
EVENT	Event_priv	databases
ALTER	Alter_priv	tables
DELETE	Delete_priv	tables
INDEX	Index_priv	tables
INSERT	Insert_priv	tables or columns
SELECT	Select_priv	tables or columns
UPDATE	Update_priv	tables or columns
CREATE TEMPORARY TABLES	Create_tmp_table_priv	tables
TRIGGER	trigger_priv	tables
CREATE VIEW	Create_view_priv	views
SHOW VIEW	Show_view_priv	views
ALTER ROUTINE	alter_routine_priv	stored routines
CREATE ROUTINE	create_routine_priv	stored routines
EXECUTE	execute_priv	stored routines
FILE	file_priv	file access on server host (Global)
CREATE TABLESPACE	create_tablespace_priv	server administration (Global)
CREATE USER	create_user_priv	server administration (Global)
PROCESS	process_priv	server administration (Global)
PROXY	proxy_priv	server administration (user to user)
RELOAD	reload_priv	server administration (Global)
REPLICATION CLIENT	repl_client_priv	server administration (Global)
REPLICATION SLAVE	repl_slave_priv	server administration (Global)
SHOW DATABASES	show_db_priv	server administration (Global)
SHUTDOWN	shutdown_priv	server administration (Global)
SUPER	super_priv	server administration (Global)
ALL [PRIVILEGES]		server administration
USAGE		server administration

按权限的种类分类

数据访问相关权限
库表结构相关权限
服务相关权限

权限相关表单

MySQL权限信息存储在“mysql”数据中
其中:

“user”表负责存储用户信息，及用户本身所拥有的权限；
“db”表负责存储用户与db的权限关系;
“tables_priv”负责存在用户与table的权限关系;
“columns_priv”负责存储用户与column的权限关系;

user表结构

Field	Type	Null	Key	Default
Host	char(60)	NO	PRI
User	char(16)	NO	PRI
Password	char(41)	NO
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(11) unsigned	NO		0
max_updates	int(11) unsigned	NO		0
max_connections	int(11) unsigned	NO		0
max_user_connections	int(11) unsigned	NO		0
plugin	char(64)	YES		mysql_native_password
authentication_string	text	YES		NULL
password_expired	enum(‘N’,’Y’)	NO		N

db表结构

Field	Type	Null	Key	Default
Host	char(60)	NO	PRI
Db	char(64)	NO	PRI
User	char(16)	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

tables_priv表结构

Field	Type	Null	Key	Default	Extra
Host	char(60)	NO	PRI
Db	char(64)	NO	PRI
User	char(16)	NO	PRI
Table_name	char(64)	NO	PRI
Grantor	char(77)	NO	MUL
Timestamp	timestamp	NO		CURRENT_TIMESTAMP	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

columns_priv表结构

Field	Type	Null	Key	Default	Extra
Host	char(60)	NO	PRI
Db	char(64)	NO	PRI
User	char(16)	NO	PRI
Table_name	char(64)	NO	PRI
Column_name	char(64)	NO	PRI
Timestamp	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP
Column_priv	set(‘Select’, ‘Insert’, ‘Update’, ‘References’)	NO

验证流程

mysql_privilege_flow