MySQL 账号和权限
权限认证的过程
- 对连接的用户进行身份认证,合法的用户通过认证,不合法的用户拒绝连接
- 对通过认证的合法用户赋予相应的权限,用户可以在这些权限范围内对数据库做相应的操作
权限表
"mysql"数据库中的user、db和host是3个最重要的权限表("mysql"数据库在安装MySQL时被创建,host在5.7版本已经删除)。
表名 | user | db | 说明 |
---|---|---|---|
用户列 | Host | Host | |
User | User | ||
authentication_string | Db | ||
权限列 | Select_priv | Select_priv | 是否可以通过SELECT 命令查询数据 |
Insert_priv | Insert_priv | 是否可以通过 INSERT 命令插入数据 | |
Update_priv | Update_priv | 是否可以通过UPDATE 命令修改现有数据 | |
Delete_priv | Delete_priv | 是否可以通过DELETE 命令删除现有数据 | |
Create_priv | Create_priv | 是否可以创建新的数据库和表 | |
Drop_priv | Drop_priv | 是否可以删除现有数据库和表 | |
Reload_priv | 是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令,包括日志、权限、主机、查询和表 | ||
Shutdown_priv | 是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时,都应当非常谨慎 | ||
Process_priv | 是否可以通过SHOW PROCESSLIST命令查看其他用户的进程 | ||
File_priv | 是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令 | ||
Grant_priv | Grant_priv | 是否可以将自己的权限再授予其他用户 | |
References_priv | References_priv | 是否可以创建外键约束 | |
Index_priv | Index_priv | 是否可以对索引进行增删查 | |
Alter_priv | Alter_priv | 是否可以重命名和修改表结构 | |
Show_db_priv | 是否可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库 | ||
Super_priv | 是否可以执行某些强大的管理功能,例如通过KILL命令删除用户进程;使用SET GLOBAL命令修改全局MySQL变量,执行关于复制和日志的各种命令。(超级权限) | ||
Create_tmp_table_priv | Create_tmp_table_priv | 是否可以创建临时表 | |
Lock_tables_priv | Lock_tables_priv | 是否可以使用LOCK TABLES命令阻止对表的访问/修改 | |
Execute_priv | Execute_priv | 是否可以执行存储过程 | |
Repl_slave_priv | 是否可以读取用于维护复制数据库环境的二进制日志文件 | ||
Repl_client_priv | 是否可以确定复制从服务器和主服务器的位置 | ||
Create_view_priv | Create_view_priv | 是否可以创建视图 | |
Show_view_priv | Show_view_priv | 是否可以查看视图 | |
Create_routine_priv | Create_routine_priv | 是否可以更改或放弃存储过程和函数 | |
Alter_routine_priv | Alter_routine_priv | 是否可以修改或删除存储函数及函数 | |
Create_user_priv | 是否可以执行CREATE USER命令,这个命令用于创建新的MySQL账户 | ||
Event_priv | Event_priv | 是否可以创建、修改和删除事件 | |
Trigger_priv | Trigger_priv | 是否可以创建和删除触发器 | |
Create_tablespace_priv | 是否可以创建表空间 | ||
安全列 | ssl_type | 支持ssl标准加密安全字段 | |
ssl_cipher | 支持ssl标准加密安全字段 | ||
x509_issuer | 支持x509标准字段 | ||
x509_subject | 支持x509标准字段 | ||
plugin | 引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户 | ||
password_expired | 密码是否过期 (N 未过期,y 已过期) | ||
password_last_changed | 记录密码最近修改的时间 | ||
password_lifetime | 设置密码的有效时间,单位为天数 | ||
account_locked | 用户是否被锁定(Y 锁定,N 未锁定) | ||
资源控制列 | max_questions | 规定每小时允许执行查询的操作次数 | |
max_updates | 规定每小时允许执行更新的操作次数 | ||
max_connections | 规定每小时允许执行的连接操作次数 | ||
max_user_connections | 规定允许同时建立的连接次数 |
权限大致分为两类:
- 普通权限:数据库的操作,如查询权限、修改权限等
- 高级管理权限:数据库的管理操作,如关闭服务的权限等
对所有数据库都具有相同权限的用户记录只需记录user表为Y,并不需要记入db表;当只授予部分数据库某些权限时,user表中的相应权限保持N,而将具体的数据库权限写入db表。
也就是说:在user表里启用的所有权限都是全局级的,适用于所有数据库。
权限认证时,先检查user表,如果权限为Y就不再检查db表;如果权限为N,检查db表中对应的权限。
账号管理
创建用户
// 创建用户test,只能从本地连接,密码为123
create user 'test'@'localhost' identified by '123';
// 创建用户test,可以从任意IP连接,密码为123
create user 'test'@'%' identified by '123';
host字段
- host值可以是主机名或IP号,或"localhost"指出本地主机
- 可以在host值使用通配符"%"和"_"
- host值"%"匹配任何主机名,空host值等价于"%"。它们的含义与LIKE操作符的模式匹配操作相同。例如"%"的host值与所有主机名匹配,而"%.mysql.com"匹配mysql.com域的所有主机
- host值为"%"时不包括localhost,因此必须单独创建'test'@'localhost',赋予权限时同理
修改密码
alter user test'@'localhost' identified with mysql_native_password by "123";
mysql_native_password为密码认证方式,在user表中的plugin字段存储,MySQL5.7默认为mysql_native_password,8.0默认为caching_sha2_password
查看所有用户
select User, Host from mysql.user
删除用户
drop user 'test'@'%';
赋予权限
// 例1. 权限为可以在所有数据库上执行所有权限(除了Grant_priv)
grant all privileges on *.* to 'test'@'localhost';
// 例2. 在例1的基础上,增加grant权限
grant all privileges on *.* to 'test'@'localhost' with grant option;
// 例3. 权限为对test1数据库里所有的表进行SELECT,INSERT, UPDATE, DELETE操作
grant select, insert, update, delete on test1.* to 'test'@'%' identidied by "123";
// 例4. 授予SUPER、PROCESS、FILE权限
// 因为这几个权限都属于管理员权限,因此不能够制定某个数据库,on后面必须跟*.*,否则会报错
grant super, process, file on *.* to 'test'@'localhost';
// 例5. 创建用户test,可以从任何IP连接,只有登陆权限
grant usage on *.* to 'test'@'%';
查看用户权限
show grants for 'test'@'%'
回收权限
revoke select on *.* from 'test'@'%';
usage权限不能回收,也就是说revoke不能删除用户。
刷新权限
flush privileges;