第30课 用户和数据安全

创建用户(用户名区分大小写)

方法一(命令):
语法:

CREATE USER '用户名'@'主机ip地址' IDENTIFIED BY '密码';

实例: 添加dog用户, 密码123456

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';

方法二(改表, 需要刷新权限):
语法:

insert into mysql.user(host,user,password) values("主机ip地址","用户名",password("密码"));
flush privileges; 

实例: 添加cat用户, 密码123456

insert into mysql.user(host,user,password) values("localhost","cat",password("123456"));
flush privileges; 

查看用户

SELECT HOST, USER, PASSWORD FROM mysql.USER;

image.png

测试一下登录

image.png

删除用户

方式一(改表, 需要刷新权限):

Delete FROM mysql.user Where User='用户名' and Host='主机ip地址';
flush privileges;

删除用户cat

Delete FROM mysql.user Where User='cat' and Host='localhost';
flush privileges;

方式二(命令):

DROP USER '用户名'@'主机ip地址';

删除用户dog

DROP USER 'dog'@'localhost';

修改用户名

创建一个用户叫Trident(海皇戟)

insert into mysql.user(host,user,password) values("localhost","Trident",password("123456"));
flush privileges; 

方式一(改表, 需要刷新权限):

update mysql.user set user = '新名字' where user = '原来的名字';
flush privileges; 

改名Aegis(宙斯盾)

update mysql.user set user = 'Aegis' where user = 'Trident';
flush privileges; 

方式二(命令):

rename user '原来的用户名'@'主机ip地址' to '新名字'@'主机ip地址';

再改回Trident(海皇戟)

rename user 'Aegis'@'localhost' to 'Trident'@'localhost';

修改密码(知道密码)

方式一(命令):

set password for '用户名'@'主机ip地址' = password('新密码');

把''海皇戟''用户的密码改成"root"

set password for 'Trident'@'localhost' = password('root');

方式二(改表, 需要刷新权限):

UPDATE `mysql`.`user` SET `Password` = password('新密码') where user = '用户名' and host = 'localhost';
FLUSH PRIVILEGES;

把'海皇戟'用户的密码再次改成"123456"

UPDATE `mysql`.`user` SET `Password` = password('123456') where user = 'Trident' and host = 'localhost';
FLUSH PRIVILEGES;