13.4 mysql用户管理

2018-01-15 14:21:54来源:oschina作者:运维小白人点击

分享
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的用户名,但不知道密码,也可以这样去授权

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台