"Reprint" MySQL5.7 add user, delete user and authorization

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 giving users' dog ' @ 'localhost''The time of authorization is this (or the like): Grant SELECT on Test.user to' dog ' @ 'localhost', use revoke SELECT on the *.' 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 the *.' dog ' @ 'localhost'; The 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

"Original Address" https://www.cnblogs.com/xujishou/p/6306765.html

"Reprint" MySQL5.7 add user, delete user and authorization