User management for MySQL

# Create some limitations and points of attention for users
User name must be no more than 16 characters long
User name is case-sensitive

Create user
Grammar:

```

([email protected]) [(None)]> help create User
Name: ' CREATE USER '
Description:
Syntax:
CREATE USER user_specification [, User_specification] ...

User_specification:
User [identified_option]

Auth_option: {
Identified by ' auth_string '
| Identified by PASSWORD ' hash_string '
| Identified with Auth_plugin
| Identified with Auth_plugin as ' hash_string '
}

The CREATE USER statement creates new MySQL accounts. An error occurs
If you try to create a account for that already exists.

按照语法，最简答的创建用户的方法：

c ([email protected]) [mysql]> create user test1;
Query OK, 0 rows Affected (0.00 sec)

([email protected]) [mysql]> select User,host,password from user;
+-------+-----------+-------------------------------------------+
| user | Host | password |
+-------+-----------+-------------------------------------------+
| Root | localhost | *a0f874bc7f54ee086fce60a37ce7887d8b31086b |
| Test1 | % | |
+-------+-----------+-------------------------------------------+
2 rows in Set (0.00 sec)
2 rows in Set (0.00 sec) reate user test1;

这个时候其实密码是空的，可以空密码登录的。

[Email protected] ~]$ mysql-s/data/mysqldata/3306/mysql.sock-utest1
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 3
Server Version:5.6.31-log Source Distribution

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

([email protected]) [(None)]>

但是没有任何权限：（USAGE这个权限，是代表废物的意思！嗯，就是这样）

([email protected]) [(none)]> show grants;
+-----------------------------------+
| Grants for [email protected]% |
+-----------------------------------+
| GRANT USAGE on . To ' test1 ' @ '% ' |
+-----------------------------------+
1 row in Set (0.00 sec)

3# 给用户设置密码：命令

([email protected]) [mysql]> Help Set Password
Name: ' SET PASSWORD '
Description:
Syntax:
SET PASSWORD [for user] = Password_option

Password_option: {
PASSWORD (' auth_string ')
| Old_password (' auth_string ')
| ' Hash_string '
}

给test1设置一个密码：

([email protected]) [mysql]> Set password for Test1=password (' passwordtest ');
Query OK, 0 rows Affected (0.00 sec)

([email protected]) [mysql]> Select User,host,password from user where user= ' test1 ';
+-------+------+-------------------------------------------+
| user | Host | password |
+-------+------+-------------------------------------------+
| Test1 | % | *a76a397ae758994b641d5c456139b88f40610926 |
+-------+------+-------------------------------------------+
1 row in Set (0.00 sec)

至于OLD_PASSWORD()函数，是为了兼容老版本的密码而存在，古老的mysql4。然而，set password for <user>=password(‘string‘); 这种修改方式已经被设置为要弃用，所以需要使用标准的修改密码方式：

([email protected]) [mysql]> alter user test1 identified by ' Password4test1 ';
Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' identified by ' password4t Est1 ' at line 1
([email protected]) [mysql]>

以上可见报错了。原因是5.6还不支持这种密码修改方式：

([email protected]) [mysql]> help alter User;
Name: ' ALTER USER '
Description:
Syntax:
ALTER USER user_specification [, User_specification] ...

User_specification:
User PASSWORD EXPIRE

这里只有一个子句，就是设置密码过期3# 账号的密码过期：

([email protected]) [mysql]> alter user test1 password expire;
Query OK, 0 rows Affected (0.00 sec)

([email protected]) [mysql]> select user,host,password,password_expired from user;
+-------+-----------+-------------------------------------------+------------------+
| user | Host | password | password_expired |
+-------+-----------+-------------------------------------------+------------------+
| Root | localhost | a0f874bc7f54ee086fce60a37ce7887d8b31086b | N |
| Test1 | % | a76a397ae758994b641d5c456139b88f40610926 | Y |
+-------+-----------+-------------------------------------------+------------------+
2 rows in Set (0.00 sec)

可以看到账号密码已经过期。但是过期以后还是可以登录，但是什么都干不了，会提示马上更改密码：

[Email protected] ~]$ mysql-s/data/mysqldata/3306/mysql.sock-utest1-p ' passwordtest '
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 4
Server Version:5.6.31-log

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

([email protected]) [(None)]> Select 1
;
ERROR 1820 (HY000): Must SET PASSWORD before executing this statement
([email protected]) [(None)]>

#修改当前账户的密码:
([email protected]) [(None)]> Set password = password (' password4test1 ');
Query OK, 0 rows Affected (0.00 sec)

([email protected]) [(None)]>

#再次尝试登录, and do the query test

[Email protected] ~]$ mysql-s/data/mysqldata/3306/mysql.sock-utest1-p ' Password4test1 '
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 5
Server Version:5.6.31-log Source Distribution

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

([email protected]) [(None)]> Select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in Set (0.00 sec)

#查询成功 to indicate that the password change was successful. Use the Admin account to check the status of your account:

([email protected]) [mysql]> select user,host,password,password_expired from user;
+-------+-----------+-------------------------------------------+------------------+
| user | Host | password | password_expired |
+-------+-----------+-------------------------------------------+------------------+
| Root | localhost | a0f874bc7f54ee086fce60a37ce7887d8b31086b | N |
| Test1 | % | cfa887c680e792c2dcf622d56fb809e3f8be63cc | N |
+-------+-----------+-------------------------------------------+------------------+
2 rows in Set (0.00 sec)

4# 远程登录在user表中，test1的host列值为%，代表可以从任意位置登录mysql

[[email protected] ~]$ mysql-utest1-p ' password4test1 '-H 192.168.199.101-p 3306
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 11
Server Version:5.6.31-log Source Distribution

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

([email protected]) [(None)]>

5# 比较完整方式创建用户

([email protected]) [mysql]> create user [email protected] '% ' identified by ' password4test2 ';
Query OK, 0 rows Affected (0.00 sec)

([email protected]) [mysql]> create user [email protected] ' 192.168.199.101 ' identified by ' test2local ';
Query OK, 0 rows Affected (0.00 sec)

([email protected]) [mysql]> Select User,host,password from user where user= ' test2 ';
+-------+-----------------+-------------------------------------------+
| user | Host | password |
+-------+-----------------+-------------------------------------------+
| Test2 | 192.168.199.101 | 74f386e8f5eec7648babdd0fcba4524b97344856 |
| Test2 | % | 5ab2e18ad9ee76f76e1c02e4dbf97bc7c3b4588b |
+-------+-----------------+-------------------------------------------+
2 rows in Set (0.00 sec)

([email protected]) [mysql]>

建立了两个test2，这两个test2是不同的，实际上应该说，用户[email protected]‘192.168.199.101‘ 和用户[email protected]‘%‘ 是两个不同的用户。

[[email protected] ~]$ mysql-utest2-p ' test2local '-H 192.168.199.101-p 3306
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 14
Server Version:5.6.31-log Source Distribution

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

([email protected]) [(None)]>

[Email protected] ~]$ mysql-utest2-s/data/mysqldata/3306/mysql.sock-p ' Password4test2 '
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 21
Server Version:5.6.31-log Source Distribution

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

([email protected]) [(None)]>

5# 修改密码：1，set password 方式：

    ([email protected])[mysql]>  set password for test1=password(‘password4test1‘);    Query OK, 0 rows affected (0.00 sec)

 2，直接update系统表user，这种方式需要刷新权限列表

([email protected]) [mysql]> Update user Set Password=password (' Password4test1 ') where user= ' test1 ';
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0

([email protected]) [mysql]> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

3，grant 方式

([email protected]) [mysql]> grant usage on . to Test1 identified by ' Password4test1 ';
Query OK, 0 rows affected (0.01 sec)

User management for MySQL