MySQL User Rights Management

MySQL data in the actual production environment in a large number of use, that usually in a good server on the firewall outside the usual, the user rights under MySQL must also be strictly managed, MySQL user rights are placed in the Mysql.user this table, the usual user rights management is implicitly using this form, the first said permission:

Permissions	Significance
Select	Inquire
Insert	Insert
Update	Update
Delete	Delete records, typically used to empty a table or a record
Create	Create
Drop	Delete, delete from table space
Grant	Grant, generally used to authorize a user
References	Reference
Index	Index
Alter	Modify Table
Create temporary tables	Create a temporary table
Lock tables	Lock table
Execute	Execute stored procedure, this is less used
CREATE view	Create a View
Show view	View View
Create routine	Create a stored procedure
Alter routine	Modifying stored Procedures
Event	Transaction management, equivalent to cron tasks in UNIX systems, setting timed tasks through the Mysq.event table
Trigger	Trigger

If it is ordinary users are generally to some of the basic additions and deletions to check the rights to be enough, peacetime management generally with the following permissions:

Mysql> select distinct concat (' user:  ', User, ' @ ', host, '; ')  AS query FROM mysql.user; #查所有用户, which host permissions are granted, CONCAT will be clearer and more intuitive when viewed mysql> show  grants for  ' root ' @ ' 127.0.0.1 '; #用于查看用名对应主机下的权限mysql > grant all *.* to  ' Test ' @ ' 127.0.0.1 '  identified by  ' youpasswd '; Grant Test all permissions for all libraries under 127.0.0.1 login password youpasswd, this if the user is not created will be implicitly creat users, here to note that the normal user to this permission is definitely not, the permissions are too high, such as the current library has a daemon library, Give test basic permissions, such as create user  ' Test ' @ ' 127.0.0.1 '  IDENTIFIED BY  ' youpasswd '; grant select, insert, update, delete on  ' daemon ' .* to  ' Test ' @ ' 127.0.0.1 '; #在这里可以creat创建后再限定权限, of course, can also write grant select, insert, update, delete on  ' Daemon ' .* to  ' Test ' @ ' 127.0.0.1 '  IDENTIFIED BY  ' youpasswd ', #当然这里都是给固定主机的权限 can also give an address segment, such as 10.0.%, or any IP that is%, of course in the actual production environment such is not allowed revoke insert on  ' daemon ' .* from  ' Test ' @ ' 127.0.0.1 '; #移除权限drop user [email protected]; #删除用户 # The actions above will be implicitly flush privileges refresh the authorization table Update user set  password = password (' newpasswd ')  WHERE user =  ' test '; flush privileges; #利用update跟新test密码, need flush privileges refresh authorization Form select password (' newpasswd '); Generates a password that relies on MySQL hash encryption, which is used when using MySQL to give permissions inside a program

Basic, in the usual management of MySQL user permissions is probably the use of so much, but also to pay attention to the host when the permissions to pay attention to 2 points: You can use the intranet must be the intranet; To avoid using the domain name, you can use IP to use IP, in order to prevent the DNS back-up in MySQL, can be used at startup

/usr/local/mysql/bin/mysqld_safe--skip-name-resolve--user=mysql& Add--skip-name-resolve parameter, Of course, it's best to add skip-name-resolve parameters to my.cnf or My.ini.

This article from "Technical essay" blog, declined reprint!

MySQL User Rights Management