每日MySQL之009：MySQL账户管理

MySQL 将账户信息存放在mysql系统数据库的user表里，账户是由MySQL独立管理的，和操作系统的账户没有关系。 

1. 创建账户
有两种办法，第一种是CREATE USER命令，是推荐的做法；第二种是直接修改user表。

示例：
mysql> CREATE USER 'user1'@'db2b' IDENTIFIED BY 'pass1';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'db2b' WITH GRANT OPTION;

mysql> CREATE USER 'user2'@'localhost' IDENTIFIED BY 'pass2';
mysql> GRANT RELOAD, PROCESS ON *.* TO 'user2'@'localhost';

mysql> CREATE USER 'user3'@'localhost' IDENTIFIED BY 'pass3';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON test1.* TO 'user3'@'localhost';
mysql> GRANT CREATE,DROP ON tes1.* TO 'user3'@'localhost';

mysql> SHOW GRANTS FOR 'user3'@'localhost';
+--------------------------------------------------------------------------+
| Grants for user3@localhost                                               |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user3'@'localhost'                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test1`.* TO 'user3'@'localhost' |
| GRANT CREATE, DROP ON `tes1`.* TO 'user3'@'localhost'                    |
+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select User, Host from user where User like 'user%';
+-------+-----------+
| User  | Host      |
+-------+-----------+
| user1 | db2b      |
| user2 | localhost |
| user3 | localhost |
+-------+-----------+
3 rows in set (0.00 sec)

2. 删除账户
使用DROP USER来删除账户：
mysql> DROP USER 'user2'@'localhost';

3. 设置账户资源限制

3.1 max_user_connections系统变量

可以使用全局系统变量max_user_connections来设置MySQL账户的资源限制，这个变量的意思是任一给定用户同时最大连接数:
mysql> SET GLOBAL max_user_connections = 2;
或者
mysql> SET @@global.max_user_connections = 2;

设置完成之后，如果某个用户已经有两个连接了，那么再有第三个连接时，就会报错：
root@db2a:~# mysql -pqingsong
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1203 (42000): User root already has more than 'max_user_connections' active connections

3.2 单个用户属性
仅仅使用max_user_connections还够，第一是因为一旦连接上，就无法控制某个用户所做的事了。第二是因为它针对所有用户，无法给单个用户定制。为了解决这个问题，MySQL可以为单个用户设置以下资源限制：

MAX_QUERIES_PER_HOUR 每小时查询数
MAX_UPDATES_PER_HOUR 每小时更新数
MAX_CONNECTIONS_PER_HOUR 每小时连接数
MAX_USER_CONNECTIONS 同时最大连接数

四个限制分别对应user表的 max_questions、max_updates、max_connections 和 max_user_connections列.可以在创建账户的时候指定，也可以修改账户

示例1：
mysql> CREATE USER 'user2'@'localhost' IDENTIFIED BY 'user2' WITH
    -> MAX_QUERIES_PER_HOUR 5
    -> MAX_UPDATES_PER_HOUR 3
    -> MAX_CONNECTIONS_PER_HOUR 10
    -> MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql
Database changed
mysql> select  max_questions, max_updates, max_connections, max_user_connections from user where User='user2';
+---------------+-------------+-----------------+----------------------+
| max_questions | max_updates | max_connections | max_user_connections |
+---------------+-------------+-----------------+----------------------+
|             5 |           3 |              10 |                    2 |
+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)

新开一session
root@db2a:~# mysql -u user2 -puser2

mysql> select user(),now();
+-----------------+---------------------+
| user()          | now()               |
+-----------------+---------------------+
| user2@localhost | 2017-08-02 05:44:34 |
+-----------------+---------------------+
1 row in set (0.00 sec)

mysql> select user(),now();
+-----------------+---------------------+
| user()          | now()               |
+-----------------+---------------------+
| user2@localhost | 2017-08-02 05:44:35 |
+-----------------+---------------------+
1 row in set (0.00 sec)

mysql> select user(),now();
+-----------------+---------------------+
| user()          | now()               |
+-----------------+---------------------+
| user2@localhost | 2017-08-02 05:44:35 |
+-----------------+---------------------+
1 row in set (0.00 sec)

mysql> select user(),now();
+-----------------+---------------------+
| user()          | now()               |
+-----------------+---------------------+
| user2@localhost | 2017-08-02 05:44:36 |
+-----------------+---------------------+
1 row in set (0.00 sec)

mysql> select user(),now();
ERROR 1226 (42000): User 'user2' has exceeded the 'max_questions' resource (current value: 5)

修改为MAX_QUERIES_PER_HOUR为7
mysql> ALTER USER 'user2'@'localhost' WITH MAX_QUERIES_PER_HOUR 7;
mysql> flush privileges;

取消MAX_QUERIES_PER_HOUR限制
mysql> ALTER USER 'user2'@'localhost' WITH MAX_QUERIES_PER_HOUR 0;

4. 设置账户密码
修改'user1'@'db2b'密码为newpas1
mysql> ALTER USER 'user1'@'db2b' IDENTIFIED BY 'newpas1';
或者
mysql> SET PASSWORD FOR 'user1'@'db2b' = PASSWORD('newpas1');

当前用户修改自己密码为selfpw1
mysql> ALTER USER USER()IDENTIFIED BY 'selfpw1';
或者
mysql> SET PASSWORD = PASSWORD('selfpw1');

也可以命令行里：
shell> mysqladmin -u user_name -h host_name password "new_password"

5. 密码过期策略
5.1 使单个用户密码立刻过期
设置用户'user2'@'localhost'密码立刻过期：
mysql> ALTER USER 'user2'@'localhost' PASSWORD EXPIRE;

则用户'user2'@'localhost'连接以后(注意，连接是没有问题的）进行操作的时候会遇到报错：
mysql> select 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

default_password_lifetime 系统变量可以设置密码过期时间，默认为0：
mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
1 row in set (0.05 sec)

5.2 通过变量设置密码过期时间
mysql> SET @@global.default_password_lifetime = 100;
Query OK, 0 rows affected (0.06 sec)

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 100   |
+---------------------------+-------+
1 row in set (0.00 sec)

5.3 设置单个用户密码过期时间
mysql> ALTER USER 'user2'@'localhost' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'user2'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

5.4 通过option file指定密码过期时间
样例如下：
[mysqld]
default_password_lifetime=180

6. 账户锁定
6.1 锁定账户
锁定命令如下
mysql > ALTER USER 'user2'@'localhost' ACCOUNT LOCK;

mysql> select account_locked from user where User='user2';
+----------------+
| account_locked |
+----------------+
| Y              |
+----------------+
1 row in set (0.00 sec)

锁定之后，再使用user2连接的时候就会报错：
qingsong@db2a:~$ mysql -u user2 -pselfpw1
ERROR 3118 (HY000): Access denied for user 'user2'@'localhost'. Account is locked.

6.2 解锁账户
解锁账户命令如下：
mysql> ALTER USER 'user2'@'localhost' ACCOUNT UNLOCK;

参考资料：
https://dev.mysql.com/doc/refman/5.7/en/account-management-sql.html