MySQL user and Rights management allow remote connections

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.