13.4 mysql用户管理

运维小白

发布于 2018-02-06 16:07:21

7840

发布于 2018-02-06 16:07:21

文章被收录于专栏：运维小白

mysql用户管理目录概要

grant all on . to 'user1' identified by 'passwd';
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
grant all on db1.* to 'user3'@'%' identified by 'passwd';
show grants;
show grants for user2@192.168.133.1;

mysql用户管理

场景，为了安全，新建的站点，创建新的用户，或者给予使用已有账户，给予权限
grant all on . to 'user1' identified by 'passwd';
- grant 表示授权
- all 表示所有权限，查看，创建，删除等等
- on . to 'user1' identified by 'passwd';
若是登录到mysql中后，输错了字符，并按了回车键，直接输入分号 ; 就会推出，回到mysql的命令行
退出mysql除了使用 quit 命令，还可以使用 exit 命令，还可以ctrl+d快捷键退出

登录到mysql

[root@hf-01 ~]# mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

创建普通用户user1，命令

grant all on . to 'user1'@'127.0.0.1' identified by '123456a';——>在输入命令的时候，千万要注意符号，一旦漏失了符号 ' '，那么后面就无法登录到user1的mysql
- 'user1'@'127.0.0.1' 指定用户@指定来源IP （指定用户可以写 % 就是通配，表示所有的IP）如果指定了来源IP，那么只能通过来源IP登录
- 符号*.* 表示所有库，所有表
  - 第一个 * 表示库名，可以写成mysql.* 那就表示对mysql所有的表
- identified by 'passwd' 指定user1的mysql密码
grant语句，是不会记录到命令历史中的因为不安全

mysql>  grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a';
Query OK, 0 rows affected (0.02 sec)

mysql>

退出数据库，并尝试user1是否可以登录

[root@hf-01 ~]# mysql -uuser1 -p'123456a'
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
[root@hf-01 ~]#

会看到登录失败，因为它默认的是sock，需要指定 -h 指定IP，会看到成功登录到user1的数据库

[root@hf-01 ~]# mysql -uuser1 -p123456a -h127.0.0.1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

授权localhost，授权本地，用sock去连接
重新登录root，并输入localhost，创建成功后，并退出

grant all on . to 'user1'@'localhost' identified by '123456a';

[root@hf-01 ~]#  mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to 'user1'@'localhost' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@hf-01 ~]#

这时不加-h 也可以登录到user1了，因为现在授权就是针对localhost，localhost就是针对的sock

[root@hf-01 ~]# mysql -uuser1 -p123456a
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

退出数据库除了用 quit 命令，还可以用 exit 命令，还可以ctrl+d快捷键退出

针对具体的权限去授权

grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
- 针对SELECT,UPDATE,INSERT，针对 db1这个库所有的表给用户user2来源IP，并设定密码
grant all on db1.* to 'user3'@'%' identified by 'passwd';
- 针对所有的IP去授权
show grants; 查看所有的授权
- 在登录到某一用户下，show grants;会查看到当前用户的权限的
- 登录user1用户的mysql，去查看授权

[root@hf-01 ~]# mysql -uuser1 -p123456a
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

show grants for user1@127.0.0.1; 指定用户去查看授权
- 登录root用户的mysql，然后查看user1用户的mysql的授权

[root@hf-01 ~]#  mysql -uroot -p'hanfeng'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants for user1@'127.0.0.1';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*B012E8731FF1DF44F3D8B26837708985278C3CED' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

show grants;需求

show grants；看的是root

创建一个用户user2，并做一个授权

grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';

mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
Query OK, 0 rows affected (0.01 sec)

mysql>

查看user2的授权

show grants for user2@'192.168.133.1';

mysql> show grants for user2@'192.168.133.1';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.1                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

有一种情况会用到它，比如说，给192.168.133.1做了授权了，但发现一个IP不够，还有一个192.168.133.2，也就是说user2用户不仅需要在192.168.133.1上登录，还需要在192.168.133.2上登录，这时候就需要把授权的命令全部在执行一遍
这时候就可以直接把GRANT USAGE ON . TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' 复制一遍，将其中192.168.133.1改为192.168.133.2 并在语句结尾加上分号 ;

mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWOORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0';
Query OK, 0 rows affected (0.00 sec)

mysql>

然后再将第二行复制GRANT SELECT, INSERT, UPDATE ON db1.* TO 'user2'@'192.168.133.1' 把IP改为192.168.133.2，并加上分号 ;

mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2';
Query OK, 0 rows affected (0.01 sec)

mysql>

这时候在来查看show grants查看192.168.133.2

mysql> show grants for user2@'192.168.133.2';
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.2                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.2' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.2'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

show grants;会看到同样的密码，同样的用户，唯一改变的就是IP
在知道mysql的用户名，但不知道密码，也可以这样去授权

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

linux

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

linux

登录后参与评论

0 条评论

热度

13.4 mysql用户管理

13.4 mysql用户管理

mysql用户管理目录概要

mysql用户管理

针对具体的权限去授权

show grants;需求

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐