Mysql权限控制语句

1.创建用户

create user 'ky32'@'localhost' IDENTIFIED by '123456'

create user：创建用户开头

ky32：用户名

localhost 新建的用户可以在哪些主机上登录 即可以使用ip地址，网段主机名

'ky32'@'localhost'

'ky32'@'192.168.233.22'

'ky32'@'192.168.233.0/24'

'ky32'@'%'表示所有

2.密码加密

create user 'ky33'@'localhost' IDENTIFIED by '123456'

select PASSWORD('adc123')

将复制到

create user 'ky32'@'localhost' IDENTIFIED by '*A128850A1070B874A09A551583D9D900E3C85B13'

3.赋权

grant all privileges on *.* to 'ky32'@'localhost' identified by '123456';

grant 赋权的开头语句

all privileges 赋予所有权限

on *.* 对所有库都有操作权限

on kgc.*只能对指定的库进行操作

to 'ky32'@'localhost' 赋权给哪个用户

identified by '123456';使用的哪个密码进行登录

4.删除用户的权限

revoke all privileges on *.* from 'test1'@'192.168.66.15';

5.如何对权限进行控制

grant select on kgc.* to 'test1'@'192.168.66.15' identified by '123456'; 单独赋权

6.如何给用户重命名

rename user 'test1'@'192.168.66.15' to 'guoqi'@'192.168.66.15';

7.删除用户

drop user 'guoqi'@'192.168.66.15';

8.如何给用户修改密码

当前的登录用户

set password=password('abc123') ##root

flush privileges

set password for 'guoqi'@'192.168.66.15' =password('abc123'); #给其他用户修改密码

9.忘记了root密码怎么办

vim /etc/my.cnf

systemctl restart mysqld

mysql -u root -p

use mysql;

select * from user;

select User,authentication_string,Host from user;

update mysql.user set authentication_string=password('123456') where User='root' and Host='localhost';

flush privileges;

vim /etc/my.cnf

删除免密登录