MySQL User rights settings

About MySQL user management, notes

1. Create a new user

Created after logging in by the root user

>> Grant all privileges on * * to testuser@localhost identified by "123456" ; Create a new user, the user name is TestUser, the password is 123456;

>> Grant all privileges on * * to testuser@localhost identified by "123456" ; Set up user testuser to access MySQL locally

>> Grant all privileges on * * to testuser@ '% ' identified by ' 123456 ' ; Set user testuser to remotely access MySQL

>> flush Privileges ; MySQL new setup user or change password after use flush privileges refresh the MySQL system permission related table, otherwise there will be denied access, another way is to restart the MySQL server, to make the new settings take effect

　　

2. Set user access to database permissions

>> Grant all privileges the test_db.* to [e-mail protected] identified by "123456"; Set user TestUser, can only access the database test_db, other databases can not access;

>> grant all privileges on * * to [e-mail protected] identified by "123456"; Set user testuser to access all databases on MySQL;

>> Grant all privileges the test_db.user_infor to [e-mail protected] identified by "123456"; Set user TestUser, can only access the database test_db table User_infor, the other tables in the database can not be accessed;

　　

3. Set User operation permissions

>> Grant all privileges on * * to [e-mail protected] identified by ' 123456 ' with Grant OPTION; Set the user testuser, have all the operation rights, namely the Administrator;

>> Grant Select on *. * to [e-mail protected] identified by ' 123456 ' with Grant OPTION; Set user testuser, only have "query" operation permission;

>> Grant Select,insert on * * to [e-mail protected] identified by "123456"; Set user testuser, only have "query \ Insert" Operation permission;

>> Grant select,insert,update,delete on * * to [e-mail protected] identified by "123456"; Set user testuser, only have "query \ Insert" Operation permission;

>> REVOKE Select,insert on how from testuser //Cancel user testuser "query \ Insert" Operation permission;

　　

4. Set User remote access permissions

>> Grant all privileges on * * to [e-mail protected]"192.168.1.100" identified by "123456"; Set user TestUser, only on the client IP is 192.168.1.100 to access MySQL remotely;

　　

5, about the root user's access settings

Set all users can remotely access MySQL, 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 to access MySQL remotely

>> select Host,user from user; Querying all user rights in MySQL

Turn off root user remote access permission

>> Delete from user where user= "root" and host= "%"; Disable root user access to MySQL on remote machine

>> flush Privileges; After you modify the permissions, refresh the MySQL system permissions related table before it takes effect　　

　　　　

　　

　　

MySQL User rights settings