MySQL用户权限验证与管理方法详解
本文实例讲述了MySQL用户权限验证与管理方法。分享给大家供大家参考,具体如下:
一、Mysql权限分两阶段验证
1. 服务器检查是否允许连接:用户名、密码,主机地址。
2. 检查每一个请求是否有权限实施。
二、Mysql权限列表
权限 | 权限级别 | 权限说明 |
create | 数据库、表或索引 | 创建数据库、表或索引权限 |
drop | 数据库或表 | 删除数据库或表权限 |
grant option | 数据库、表或保存的程序 | 赋予权限选项 |
references | 数据库或表 | 外键权限 |
alter | 表 | 更改表,比如添加字段、索引、修改字段等 |
delete | 表 | 删除数据权限 |
index | 表 | 索引权限 |
insert | 表 | 插入权限 |
select | 表 | 查询权限 |
update | 表 | 更新权限 |
create view | 视图 | 创建视图权限 |
show view | 视图 | 查看视图权限 |
alter routine | 存储过程 | 更改存储过程权限 |
create routine | 存储过程 | 创建存储过程权限 |
execute | 存储过程 | 执行存储过程权限 |
file | 服务器主机上的文件访问 | 文件访问权限 |
create temporary tables | 服务器管理 | 创建临时表权限 |
lock tables | 服务器管理 | 锁表权限 |
create user | 服务器管理 | 创建用户权限 |
proccess | 服务器管理 | 查看进程权限 |
reload | 服务器管理 | 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
replication client | 服务器管理 | 复制权限 |
replication slave | 服务器管理 | 复制权限 |
show databases | 服务器管理 | 查看数据库权限 |
shutdown | 服务器管理 | 关闭数据库权限 |
super | 服务器管理 | 执行kill线程权限 |
三、Mysql用户权限管理操作
1. 权限查询:
(1)查看mysql的所有用户及其权限:
1
|
select * from mysql. user \G; |
(格式化显示)
(2)查看当前mysql用户权限:
1
|
show grants; |
(3)查看某个用户的权限:
1
|
show grants for 用户名@主机; |
示例:
1
|
show grants for root@localhost; |
2. Mysql用户创建:
方法一:使用create user命令创建。
1
|
create user '用户名' @ '主机' identified by '密码' ; |
示例:
1
|
create user 'wjt' @ 'localhost' identified by 'wujuntian' ; |
方法二:直接向数据表mysql.user中插入一条用户记录。
示例:
注意:
使用方法二一定要记得要执行flush privileges刷新权限。其次,mysql5.7以后,mysql.user表的password字段已被authentication_string代替,所以应将“password”改为“authentication_string”,密码一定要使用password函数加密。
3. Mysql用户删除:
1
|
drop user '用户名' @ '主机' ; |
4. Mysql用户权限授予:
刚创建的用户默认是没有权限的,需要使用grant指令进行权限的授予。
grant指令完整格式:
grant 权限列表 on 数据库名.数据表名 to '用户名'@'主机' identified by '密码' with grant option;
示例:
1
|
grant all privileges on *.* to 'wjt' @ 'localhost' identified by "wujuntian" with grant option ; |
可使用“*”表示所有数据库或所有数据表,“%”表示任何主机地址。
可以使用grant重复给用户添加权限,进行权限叠加。
with grant option:这个选项表示该用户可以将自己拥有的权限授权给别人。
记得授权后一定要刷新权限:
1
|
flush privileges ; |
5. Mysql用户权限回收:
revoke指令格式:
revoke 权限列表 on 数据库名.数据表名 from 用户名@主机;
示例:
1
|
revoke select on test. user from wjt@localhost; |
注意:
其实GRANT语句在执行的时候,如果权限表中不存在目标账号,则创建账号;如果已经存在,则执行权限的新增。
usage权限不能被回收,也就是说,REVOKE用户权限并不能删除用户。
6. 对账户重命名:
rename user '旧用户名'@'旧主机' to '新用户名'@'新主机';
示例:
1
|
rename user 'wujuntian' @ 'localhost' to 'ajun' @ 'localhost' ; |
7. Mysql用户密码修改:
方法一:使用set password命令。
set password for '用户名'@'主机' = password('新密码');
示例:
1
|
set password for 'root' @ 'localhost' = password ( '123456' ); |
方法二:修改mysql.user表中的password(或authentication_string)字段。
示例:
注意:
此方法一定要执行“flush privileges;”指令刷新权限,否则密码修改无法生效。Mysql5.7以后应将“password”改为“authentication_string”。
方法三:使用grant指令在授权时修改密码:
grant select on 数据库名.数据表名 to 用户名@主机 identified by '新密码' with grant option;
示例:
方法四:运行mysqladmin脚本文件。
该文件一般在mysql安装目录下的bin目录中。进入该目录,根据一下两种具体情况输入命令(只有root用户有这个权限)。
(1)用户尚无密码:
mysqladmin -u 用户名 password 新密码;
(2)用户已有密码:
mysqladmin -u 用户名 -p password 新密码;
(回车后会提示输入旧密码,输入之后即可修改成功。)
注意:
更改密码时候一定要使用PASSWORD函数(mysqladmin 和GRANT 两种方式不用写,会自动加上)。
8. 忘记密码登录mysql:
方法一:
先停止正在运行的Mysql服务,在命令行窗口进入mysql安装目录下的bin目录,在-skip-grant-tables参数下运行mysqld文件(Linux系统运行mysqld_safe文件更安全):
1
|
mysqld --skip-grant-tables |
这样可以跳过Mysql的访问控制,在控制台以管理员的身份进入mysql数据库。另外再开启一个命令行窗口,进入mysql安装目录下的bin目录,直接输入:mysql,回车,即可登录mysql,然后就可以重新设置密码了(注意:此时“Mysql用户密码修改”中的四种方法只有第二种方法能使用!)。设置成功后退出,重启Mysql服务。
方法二:修改mysql配置文件my.ini。
其实原理和方法一一样,都是利用Mysql提供的--skip-grant-tables参数来跳过Mysql的访问控制。打开mysql配置文件my.ini,在'[mysqld]'下加入“skip-grant-tables”,保存,重启Mysql服务,然后就可以不需密码登录mysql进行密码修改了。
Mysql中的“mysql”数据库存储着所有Mysql用户的权限信息数据表。当Mysql启动时,所有的权限表内容都被读进内存中,进行权限判断时直接使用内存中的内容进行判断。用grant、revoke或set password对权限表进行的修改会立即被服务器注意到,GRANT操作的本质就是修改权限表后进行权限的刷新。但是如果手工修改权限表,例如使用insert、update、delete等操作权限表的话,应该执行一个flush privileges命令,该命令会使服务器重新读取权限表内容到内存,从而使修改生效。如果不执行该命令,必须重启mysql服务才能生效。所以,最好使用grant、revoke或set password对权限表进操作,可以省去执行flush privileges命令的麻烦,而且如果忘了执行这个命令的话你会很抓狂。。。
不仅如此,删除用户、重命名用户最好也分别使用drop user、rename user命令进行操作,而不要使用delete、update命令进行操作。前者不但会对mysql.user数据表进行操作,同时也会更新其他权限表的记录,而后者只会对mysql.user表的数据进行操作,这样会出现很多问题,因为用户的权限信息不仅仅存在于mysql.user表中。比如你使用delete删除了mysql.user表中的一个用户,但是没有操作其他权限数据表的话,那么其他权限数据表例如tables_priv中关于该用户的权限记录还存在着,下次如果想使用create user命令创建相同名称的用户会失败,只能使用insert into指令向mysql.user表中插入记录,或者先把其他权限数据表中与该用户名相关的记录删除。使用update命令重命名用户也会出现很大问题,重命名后用户失去了很多的权限,而其他权限表中关于原用户名的记录则成了没用的记录,除非你对每一个权限表都进行相同的更新操作,但这很麻烦。所以,使用drop user、rename user吧,一个命令就可以让系统自动帮你完成所有事情,何乐而不为呢!
Mysql权限检查:
mysql 先检查对大范围是否有权限,如果没有再到小范围里去检查。比如:先检查对这个数据库是否有select权限,如果有,就允许执行。如果没有,再检查对表是否有select权限,一直到最细粒度,也没有权限,就拒绝执行。因此,粒度控制越细,权限校验的步骤越多,性能越差。