MySQL 5.7 Learning

Mysql-uroot-proot

MySQL5.7 mysql.user Table no password field changed authentication_string;

One. Create a User:

Command: CREATE USER ' username ' @ ' host ' identified by ' password ';

Example: CREATE USER ' dog ' @ ' localhost ' identified by ' 123456 ';

CREATE USER ' dog2 ' @ ' localhost ' identified by ';

Ps:username-The user name that you will create,

Host-Specifies on which host the user can log on, where "localhost" means that the user can only log on locally, not on another machine telnet, if you want to telnet, the "localhost" changed to "%", indicating that on any computer can be logged on; You can also specify that a machine can log in remotely;

Password-The user's login password, the password can be empty, if it is empty, the user can not require a password to log on to the server.

Two. Authorization:

Command:GRANT privileges on databasename.tablename to ' username ' @ ' host '

Ps:privileges-User's operation permissions, such as SELECT, INSERT, UPDATE, etc. (see the last side of the article for a detailed list). Use all if you want to grant the permission. DatabaseName-database name, tablename-table name, if you want to grant the user the appropriate operation permissions on all databases and tables, the * representation, such as *. *.

Example: GRANT SELECT, INSERT on mq.* to ' dog ' @ ' localhost ';

three. Create user authorization at the same time

Mysql> Grant all privileges the mq.* to [e-mail protected] identified by ' 1234 ';
Query OK, 0 rows affected, 1 Warning (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows affected (0.01 sec)

PS: flush Privileges must be performed;

Otherwise, you are prompted at logon: ERROR 1045 (28000): Access denied for user ' user ' @ ' localhost ' (using Password:yes)

Four. Setting and changing user passwords

Command: SET PASSWORD for ' username ' @ ' host ' = PASSWORD (' NewPassword ');

Example: SET PASSWORD for ' dog2 ' @ ' localhost ' = PASSWORD ("dog");

Five. Revoke user Rights

Command: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Description: Privilege, DatabaseName, TableName-With the authorization section.

Example: REVOKE SELECT on mq.* from ' dog2 ' @ ' localhost ';

PS: If you are authorizing the user ' dog ' @ ' localhost ' (or similar): Grant SELECT on Test.user to ' dog ' @ ' localhost ', then use revoke select On *. * FRO M ' dog ' @ ' localhost '; the command does not revoke the user's SELECT operation on the Users table in the test database. Conversely, if authorization is using Grant SELECT on *. * to ' dog ' @ ' localhost '; revoke Select on Test.user from ' dog ' @ ' localhost '; the command cannot revoke the user's SELECT permission to the Users table in the test database.

Specific information can be used with the command show GRANTS for ' dog ' @ ' localhost '; View.

Six. Delete a user

Command: DROP USER ' username ' @ ' host ';

Seven. View the user's authorization

Mysql> Show grants for [email protected];
+---------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------+
| GRANT USAGE on *. * to ' dog ' @ ' localhost ' |
| GRANT INSERT on ' MQ '. * to ' dog ' @ ' localhost ' |
+---------------------------------------------+
2 rows in Set (0.00 sec)

Ps:grant usage:mysql USAGE permissions are NULL permissions, default create user permissions, only library, nothing can do

PS before collecting a lot of MySQL commands, the results today in MySQL 5.7 is invalid, so in order to tidy up

Mysql> CREATE USER ' root ' @ ' 192.168.1.17 ' identified by ' root ';
Query OK, 0 rows Affected (0.00 sec)

Create user

Mysql> CREATE USER ' root ' @ '% ' identified by ' root ';
Query OK, 0 rows Affected (0.00 sec)

The root user can log on to any machine and have any permissions on any of the tables

Mysql> Grant all privileges on * * to [e-mail protected] '% ' identified by ' root ';
Query OK, 0 rows affected, 1 Warning (0.00 sec)

-----------------------------------------======================================================================

2. Mysql> set global validate_password_policy=0;
4. Query OK, 0 rows affected (0.05 sec)
8. Mysql>
10. Mysql>
12. Mysql> set global validate_password_mixed_case_count=0;
14. Query OK, 0 rows Affected (0.00 sec)
18. Mysql> set global validate_password_number_count=3;
20. Query OK, 0 rows Affected (0.00 sec)
24. Mysql> set global validate_password_special_char_count=0;
26. Query OK, 0 rows Affected (0.00 sec)
30. Mysql> set global validate_password_length=3;
32. Query OK, 0 rows Affected (0.00 sec)
36. Mysql> SHOW VARIABLES like ' validate_password% ';
38. +--------------------------------------+-------+  
40. | variable_name | Value |
42. +--------------------------------------+-------+  
44. |       Validate_password_dictionary_file | |
46. | Validate_password_length | 3 |
48. | Validate_password_mixed_case_count | 0 |
50. | Validate_password_number_count | 3 |
52. | Validate_password_policy | Low |
54. | Validate_password_special_char_count | 0 |
56. +--------------------------------------+-------+  
58. 6 rows in Set (0.00 sec)

FLUSH privileges;

4) Modify the simple password:

[SQL]View PlainCopy

1. mysql> SET PASSWORD for ' root ' @' localhost ' = PASSWORD (' 123 ');
2. Query OK, 0 rows affected, 1 Warning (0.00 sec)

MySQL 5.7 Learning