mysql-用户权限 & 修改用户密码

研究mysql用户权限是有原因的，惨痛的教训。
代码放到git上面的时候，不小心把服务器IP、数据库用户名、密码都泄露了，当初懒，只设了两个拥有各种权限的用户，结果就是数据库被清空了，还留了QQ，加QQ付钱给还原数据。倔强如我，数据库卸了重装！庆幸里面没放很重要的数据，而且还有备份。
所以有了前面安装mysql的博文。讲正事儿，mysql的用户权限。
废话不多说，上命令：

root@ip-x-x-x-:~# mysql -u username -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 147
Server version: 5.5.53-0ubuntu0.14.04.1 (Ubuntu)

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 databases;  # 看一下有哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_xxx             |
| db_xxx             |
| db_xxx             |
| db_xxx             |
| mysql              |
| performance_schema |
+--------------------+
n rows in set (0.01 sec)

mysql> use mysql;    # 切换到mysql数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;    # 看一下有哪些数据表，我们要用的是user表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)


mysql> select user, host, password from user;
+---------------+-----------+------------------+
| user          | host      | password         |
+---------------+-----------+------------------+
| usename1      | ip1       | password         |
| usename2      | ip2       | password         |
+---------------+-----------+------------------+
n rows in set (0.00 sec)
# 这里说明一下，host字段为“%”的表示允许所有ip使用此用户访问

# 创建一个所有ip可用的用户
mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY '123';  
Query OK, 0 rows affected (0.02 sec)

mysql> select user, host, password from user;
+--------------+----------------+---------------+
| user         | host           | password      |
+--------------+----------------+---------------+
| newuser      | %              | *23AE809DDAC  |
+--------------+----------------+---------------+
12 rows in set (0.00 sec)

# 创建一个仅能在本地使用的用户
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY '123';  
Query OK, 0 rows affected (0.00 sec)
mysql> select user, host, password from user;
+--------------+----------------+---------------+
| user         | host           | password      |
+--------------+----------------+---------------+
| newuser      | %              | *23AE809DDAC  |
| newuser      | localhost      | *23AE809DDAC  |
+--------------+----------------+---------------+
12 rows in set (0.00 sec)

# 添加完之后刷新一下
mysql> flush privileges;

# 接下来给用户分配权限
# 语句格式如下：grant authority on database.table to username@host identified by password;
mysql> grant select, update, insert on db_name.* to 'username'@'host' identified by 'password';
mysql> flush privileges;
mysql> show grants for 'username'@'host';
+----------------------------------------------+
| Grants for mysql_app@%                       |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'host' IDENTIFIED BY PASSWORD '*passwd' |
| GRANT SELECT, INSERT, UPDATE ON `database`.* TO 'username'@'host'  |
+-------------------------------------+
2 rows in set (0.00 sec)
# 完毕

再来看一下精简的命令。

# 登录数据库
root@ip-x-x-x-:~# mysql -u username -p
Enter password: 

# 切换数据库
mysql> use mysql;

# 查看存在的用户
mysql> select user, host, password from user;

# 创建新用户  "host"部分可以是ip，%表示任意ip
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'passwd';
# 刷新一下
mysql> flush privileges;

# 给新用户赋权限
mysql> grant select, update, insert on db_name.* to 'username'@'host' identified by 'password';
# 刷新一下
mysql> flush privileges;

# 完毕

写在最后：删表等操作赋权限要慎重，建议多用户分邦（数据库）而治。

2017-12-01补充：
好久没有这么深层的用过mysql了，都快忘记怎么修改用户密码了。借着用到新的mysql，重温修改密码的命令

use mysql;
select * from user;
update user set password=password('123') where user='username';
flush privileges;