MySQL user rights settings, remote access settings, set fixed IP remote access, set root user cannot remotely access MySQL database

Source: Internet
Author: User
Tags access database

  About MySQL user management, note  1, creating a new user after logging in by the root user creates >> grant all privileges on *. * to [email protected] Identified by  "123456"  ;//create a new user, the user name is TestUser, the password is 123456;  >> grant all privileges on *. * to& nbsp [email protected] Identified by  "123456"  ;//set user testuser, can be accessed locally mysql >> grant all Privileges on * * to [email protected] "%" identified by  "123456"  ;//set user testuser, can access MySQL remotely  >> flush privileges ;//mysql New user or change password after the need to flush privileges refresh the MySQL system permissions related tables, otherwise there will be access denied, there is a way to is to restart the MySQL server for the new settings to take effect 2, set the user Access database permissions >> grant all privileges on test_db.* to [email  Protected] identified by "123456";//Set user testuser, only Access database test_db, other databases cannot access  ; >> grant all Privileges on *.* to [email protected] identified by "123456";//Set user testuser to access all databases on MySQL;  >> grant All Privileges On test_db.user_infor to [email protected] identified by "123456";//Set user testuser, only Access database test_db table user_infor, other tables in the database cannot access   ; 3, set user operation rights >> grant all privileges on * * to [email protected] identified by "123456" With GRANT option ;//set user testuser, with all operational privileges, i.e. Administrator; >> grant select on * * to [email  protected] identified by "123456" with GRANT option ;//set user testuser, with only "query" operation permission;  >> grant   select,insert on * * to [email protected] identified by "123456"  ; Set user testuser, only have "query \ Insert" Operation permission;  >> grant select,insert,update,delete on *. * to [ Email protected] identified by "123456";//Set user testuser, only have "query \ Insert" Operation permission;  >> revoke select , insert on what from testuser//Cancel user testuser "query \ Insert" operation right; 4, set user remote access (fixed IP address remote access) >> grant all Privileges on * * to [email protected] "192.168.1.100"  identified by  "123456" ;//set user TestUser, only on the client IP is 192.168.1.100 to remote access to MySQL; 5. Access settings settings for root users All users can access MySQL remotely, Modify the MY.CNF configuration file, bind-address = 127.0.0.1 front Plus "#" comment out, so that you can allow other machines to remotely access the native MySQL;  >> grant all privileges on   * * to [email protected] "%" identified by  "123456"  ;   Set user root, can be accessed remotely mysql >> select host,user from user; Query MySQL for all user rights   turn off root user remote access (increase privacy) >> delete from user where user= "root" and host= "%";//  Prevents root users from accessing Mysql >> flush on remote machines privileges ; After modifying permissions, refresh the MySQL system permission related table to be effective  

MySQL user rights settings, remote access settings, set fixed IP remote access, set root user cannot remotely access MySQL database

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.