Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to the detailed steps of managing MySQL users. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the detailed steps of managing MySQL users can bring you some practical help.

Some restrictions and precautions for creating users

User name must be no more than 16 characters long

The user name is case sensitive

"create a user

Syntax:

(root@localhost) [(none)] > help create userName: '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 occursif you try to create an account that already exists.

According to the syntax, the easiest way to create a user is:

C (root@localhost) [mysql] > create user test1;Query OK, 0 rows affected (0.00 sec) (root@localhost) [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

At this time, the password is actually empty, you can log in with an empty password.

[mysql@mysql01] $mysql-S / data/mysqldata/3306/mysql.sock-utest1Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 3Server version: 5.6.31-log Source distributionCopyright (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. (test1@localhost) [(none)] >

But do not have any authority: (USAGE this authority, is the meaning of waste! Well, that's it.)

(test1@localhost) [(none)] > show grants +-- + | Grants for test1@% | +-+ | GRANT USAGE ON *. * TO 'test1'@'%' | +- -+ 1 row in set (0.00 sec)

"set the password for the user:

Command

(root@localhost) [mysql] > help set passwordName: 'SET PASSWORD'Description:Syntax:SET PASSWORD [FOR user] = password_optionpassword_option: {PASSWORD (' auth_string') | OLD_PASSWORD ('auth_string') |' hash_string'}

Set a password for test1:

(root@localhost) [mysql] > set password for test1=password ('passwordtest'); Query OK, 0 rows affected (0.00 sec) (root@localhost) [mysql] > select user,host,password from user where user='test1' +-- + | user | host | password | +-- -+ | test1 |% | * A76A397AE758994B641D5C456139B88F40610926 | +-+ 1 row in set (0.00 sec)

As for the OLD_PASSWORD () function, it exists to be compatible with the old version of the password, the old mysql4.

However, set password for = password ('string'); this modification has been set to be deprecated, so the standard password modification method is required:

(root@localhost) [mysql] > alter user test1 identified by 'password4test1';ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near' identified by 'password4test1'' at line 1 (root@localhost) [mysql] >

From the above, we can see that the report was wrong. The reason is that 5.6 does not support this password modification method:

(root@localhost) [mysql] > help alter user;Name: 'ALTER USER'Description:Syntax:ALTER USER user_specification [, user_specification]... user_specification: user PASSWORD EXPIRE

There is only one clause, which is to set the password to expire.

The password of your account expires:

(root@localhost) [mysql] > alter user test1 password expire;Query OK, 0 rows affected (0.00 sec) (root@localhost) [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)

You can see that the account password has expired.

But you can still log in after it expires, but you can't do anything. You will be prompted to change your password immediately:

[mysql@mysql01] $mysql-S / data/mysqldata/3306/mysql.sock-utest1-p'passwordtest'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 4Server version: 5.6.31-logCopyright (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. (test1@localhost) [(none)] > select 1->; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement (test1@localhost) [(none)] > # modify the password of the current account: (test1@localhost) [(none)] > set password = password ('password4test1') Query OK, 0 rows affected (0.00 sec) (test1@localhost) [(none)] > # try to log in again and do a query test [mysql@mysql01] $mysql-S / data/mysqldata/3306/mysql.sock-utest1-p'password4test1'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 5Server version: 5.6.31-log Source distributionCopyright (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. (test1@localhost) [(none)] > select 1 | 1 | +-+ | 1 | +-- + 1 row in set (0.00 sec) # query indicates that the password has been changed successfully. Query the use table with the administrative account to check the account status: (root@localhost) [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)

Remote login

In the user table, the host column value of test1 is%, which means that you can log in to mysql from any location

[mysql@mysql01] $mysql-utest1-paired password4test1'-h 192.168.199.101-P 3306Warning: 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 11Server version: 5.6.31-log Source distributionCopyright (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. (test1@192.168.199.101) [(none)] >

Create users in a more complete way

(root@localhost) [mysql] > create user test2@'%' identified by 'password4test2';Query OK, 0 rows affected (0.00 sec) (root@localhost) [mysql] > create user test2@'192.168.199.101' identified by' test2local';Query OK, 0 rows affected (0.00 sec) (root@localhost) [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) (root@localhost) [mysql] >

Two test2 are established, and the two test2 are different. In fact, it should be said that user test2@'192.168.199.101' and user test2@'%' are two different users.

[mysql@mysql01] $mysql-utest2-paired test 2local`-h 192.168.199.101-P 3306Warning: 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 14Server version: 5.6.31-log Source distributionCopyright (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. (test2@192.168.199.101) [(none)] > [mysql@mysql01 ~] $mysql-utest2-S / data/mysqldata/3306/mysql.sock-p'password4test2'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 21Server version: 5.6.31-log Source distributionCopyright (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. (test2@localhost) [(none)] >

"change the password:

1Dim set password method:

(root@localhost) [mysql] > set password for test1=password ('password4test1'); Query OK, 0 rows affected (0.00 sec)

2. Directly update the system table user, which requires refreshing the permission list.

(root@localhost) [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 (root@localhost) [mysql] > flush privileges;Query OK, 0 rows affected (0.00 sec)

3Grant mode

(root@localhost) [mysql] > grant usage on *. * to test1 identified by 'password4test1'

Query OK, 0 rows affected (0.01 sec)

The detailed steps of managing MySQL users will stop here. If you want to know about other related issues, you can continue to follow our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.