MySQL user and Rights management allow remote connections

MYSQ, a powerful relational database, and its user management is of course especially important in the development process, then look at MySQL user management

1. Log in to the database

Mysql-uroot-p Enter

Enter password ... Enter

2. After successful login, switch database

Mysql>use MySQL;

3. View Current User

Mysql>select user,host from user;

Here are just a list of two fields, this table is a lot of fields, the general concern about these several:

Host: Access hosts

User: Access username

Plugin: Authentication method (password encryption method)

Authentication_string: A long string of characters after password encryption

4. New Users

Mysql>create USER ' username ' @ ' host ' identified by ' password ';

Host: ip,% "Any IP" localhost "native" 192.168.31.22 "Specify IP"

Identified by: The password is encrypted with the default encryption method is placed in the table, not directly stored can be plaintext

The default encryption method needs to check the user table plugin, if the client does not support, you can specify encryption method to encrypt

Mysql>create USER ' username ' @ ' host ' identified MySqlSHA1 by ' password ';

The new user by default is not any permission, that is, useage, can only login

5. View permissions for users in the MySQL databaseMysql>show grants for ' username ' @ ' host '; 6. User AuthorizationMysql>grant privileges on Dbname.tabname to "username" @ "host" Privileges: permissions, select "Query", update "updates", delete "delete" and so on, All "All" such authorized users have all the rights, but there is no way to manage other users, if you want this user can authorize other users, and then add with the grant Option.grant privileges on Dbname.tabname to "Username" @ "host" with GRANT option 7. Modify the userMysql>update user set host= "localhost" where user= "username" User,host,plugin can use the UPDATE statement, after modifying plugin, you need to change the password again, Otherwise invalid, you can also change the password when you specify the encryption method, you do not need to modify the plugin user password encryption method is generally the client does not support the server side of the encryption method, will be modified, if supported, generally can not modify the encryption method, mysql8.0 need to change the encryption method, otherwise the graphical tool can not be connected at all. 8. Delete User, delete according to user name, or according to hostMysql>delete user where user= "username"

Note: Allow remote is the user's access address from localhost to% or specify the IP, allow the remote, to set permissions, otherwise the user can only log in, and nothing else can do, of course, you want to tease him to play, also can, you happy good.

9. User action, last step, update permissions

Mysql>flush Privileges

All right! Just share this little bit, and wait till I get some more ink to go back.

MySQL user and Rights management allow remote connections