以管理员身份删除mysql_Mysql创建、删除、授权管理员

Mysql权限工作原理

mysql权限验证阶段

阶段1：连接数据库，此时mysql会根据你的用户名及你的来源(ip或者主机名称)判断是否有权限连接

阶段2：对mysql服务器发起请求操作，如create table、select、delete、update、create index等操作，此时mysql会判断你是否有权限操作这些指令

查看mysql中所有用户

-- 进入mysql库

USE mysql;

-- 查看所有用户

SELECT user,host FROM user;

/*

mysql> select user,host from user;

+---------------+-----------+

| user | host |

+---------------+-----------+

| default | % |

| root | % |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+---------------+-----------+

5 rows in set (0.00 sec)

*/

创建用户

语法 create user 用户名[@主机名] [identified by '密码'];

主机名默认值为%，表示这个用户可以从任何主机连接mysql服务器

密码可以省略，表示无密码登录

-- 进入mysql库

USE mysql;

-- 查看所有用户

SELECT user,host FROM user;

/*

+---------------+-----------+

| user | host |

+---------------+-----------+

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+---------------+-----------+

*/

-- 不指定主机名时，表示这个用户可以从任何主机连接mysql服务器

create user test1;

-- 查看所有用户

SELECT user,host FROM user;

/*

+---------------+-----------+

| user | host |

+---------------+-----------+

| test1 | % |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+---------------+-----------+

*/

-- 从任何主机连接mysql服务器

CREATE user test1;

-- test2的主机为localhost表示本机，此用户只能登陆本机的mysql

CREATE user 'test2'@'localhost' identified by '123456';

-- test3可以从任何机器连接到mysql服务器

CREATE user 'test3'@% identified '123456';

-- test4可以从192.168.11段的机器连接mysql

CREATE user 'test4'@'192.168.11.%' identified by '123456';

修改密码

通过管理员修改密码

SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码');

创建账号时设置密码

create user 用户名[@主机名] [identified by '密码'];

通过修改mysql.user表修改密码

use mysql;

-- 修改mysql.user表信息

update user set authentication_string = password('321') where user ='test1' and host = '%';

-- 刷新

flush privileges;

-- 通过表的方式修改之后，需要执 flush privileges; 才能对用户生效

-- 5.7中user表中的authentication_string字段表示密码，老的一些版本中密码字段是password

给用户授权

语法 grant privileges ON database.table TO 'username'[@'host'] [with grant option]

privileges (权限列表)，可以是 all ，表示所有权限，也可以是 select、update 等权限，多个权限之间用逗号分开

ON 用来指定权限针对哪些库和表，格式为 数据库.表名 ，点号前面用来指定数据库名，点号后面用来指定表名， . 表示所有数据库所有表

TO 表示将权限赋予某个用户, 格式为 username@host ，@前面为用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人

-- 给test1授权可以操作所有库所有权限，相当于dba

GRANT all ON *.* TO 'test1'@'%';

-- test1可以对seata库中所有的表执⾏select

GRANT select ON seata.* TO 'test1'@'%';

-- test1可以对seata库中所有的表执⾏select、update

GRANT select,update ON seata.* TO 'test1'@'%';

-- test1用户只能查询mysql.user表的user,host字段

GRANT select(user,host) ON mysql.user TO 'test1'@'localhost';

查看用户有哪些权限

语法 show grants for '用户名'[@'主机']

主机可以省略，默认值为%

-- 主机可以省略，默认值为%

SHOW GRANTS FOR 'test1'@'localhost';

-- 查看当前用户的权限

SHOW GRANTS;

-- 当前是root账户

/*

mysql> show grants;

+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON "@" TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

2 rows in set (0.00 sec)

*/

撤销用户的权限

语法 revoke privileges ON database.table FORM '用户名'[@'主机'];

-- 可以先通过 show grants 命令查询一下用户对于的权限，然后使 revoke 命令撤销用户对应的权限

show grants for 'test1'@'localhost';

/*

+--------------------------------------------------------------------+

| Grants for test1@localhost |

+--------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'test1'@'localhost' |

| GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |

+--------------------------------------------------------------------+

*/

revoke select(host) on mysql.user from test1@localhost;

/*

Query OK, 0 rows affected (0.00 sec)

*/

show grants for 'test1'@'localhost';

/*

+--------------------------------------------------------------+

| Grants for test1@localhost |

+--------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'test1'@'localhost' |

| GRANT SELECT (user) ON `mysql`.`user` TO 'test1'@'localhost' |

+--------------------------------------------------------------+

*/

删除用户

drop user '用户名'[@‘主机’]

-- 删除 test1

drop user test1@localhost;

通过删除mysql.user表数据的方式删除

delete from user where user='test1' and host='localhost';

flush privileges;

授权原则说明

只授予能满足需要的最小权限，比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限

创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段

初始化数据库的时候删除没有密码的用户，安装完数据库的时候会自动创建一些用户，这些用户默认没有密码

为每个用户设置满足密码复杂度的密码

定期清理不需要的用户，回收权限或者删除用户

总结

通过命令的方式操作用户和权限不需要刷新，下次登录自动生效

通过操作mysql库中表的方式修改、用户信息，需要调用 flush privileges; 刷新一

下，下次登录自动生效

mysql识别用户身份的方式是：用户名+主机本

本中讲到的一些指令中带主机的，主机都可以省略，默认值为%，表示所有机器

mysql中用户和权限的信息在库名为mysql的库中

参考资料来源 微信公众号 "大侠学JAVA" mysql笔记