mysql-用户权限

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

root@ip-x-x-x-:~# mysql -u username -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 147Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 -pEnter password: # 切换数据库mysql> use mysql;# 查看存在的用户mysql> select user, host, password from user;# 创建新用户  "host"部分可以是ip，%表示任意ipmysql> 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;# 完毕

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