Detailed Explanation of MySQL User Authority Verification and Management Method

This paper describes the verification and management method of MySQL user rights. Share it for your reference, as follows:

1. Mysql permissions are validated in two stages

1. The server checks whether the connection is allowed: username, password and host address.

2. Check whether every request has permission to execute.

2. Mysql Permission List

权限	权限级别	权限说明
create	数据库、表或索引	创建数据库、表或索引权限
drop	数据库或表	删除数据库或表权限
grant option	数据库、表或保存的程序	赋予权限选项
references	数据库或表	外键权限
alter	表	更改表，比如添加字段、索引、修改字段等
delete	表	删除数据权限
index	表	索引权限
insert	表	插入权限
select	表	查询权限
update	表	更新权限
create view	视图	创建视图权限
show view	视图	查看视图权限
alter routine	存储过程	更改存储过程权限
create routine	存储过程	创建存储过程权限
execute	存储过程	执行存储过程权限
file	服务器主机上的文件访问	文件访问权限
create temporary tables	服务器管理	创建临时表权限
lock tables	服务器管理	锁表权限
create user	服务器管理	创建用户权限
proccess	服务器管理	查看进程权限
reload	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
replication client	服务器管理	复制权限
replication slave	服务器管理	复制权限
show databases	服务器管理	查看数据库权限
shutdown	服务器管理	关闭数据库权限
super	服务器管理	执行kill线程权限

3. Mysql User Rights Management Actions

1. Permission query:

(1) View all users of mysql and their permissions:

select * from mysql.user\G;

(Formatted display)

(2) View the current mysql user rights:

show grants;

(3) View the permissions of a user:

show grants for  User name @ Host ;

Example:

show grants for root@localhost;

2. Mysql user creates:

Method 1: Use the create user command to create.

create user ' User name '@' Host ' identified by ' Password ';

Example:

create user 'wjt'@'localhost' identified by 'wujuntian';

Method 2: Insert a user record directly into the data table mysql. user.

Example:

insert into mysql.user set user='wujuntian',host='localhost',password=password('123123');

Note:

Using Method 21, remember to perform flush privileges refresh permissions. Secondly, after mysql5.7, the password field of mysql. user table has been replaced by authentication_string, so "password" should be changed to "authentication_string", and password 1 must be encrypted by password function.

3. Mysql user deletion:

drop user ' User name '@' Host ';

4. Mysql user rights granted:

The user just created has no permission by default, and needs to use grant instruction to grant permission.

grant instruction full format:

grant Permission List on Database Name. Table Name to 'User Name' @ 'Host' identified by 'Password' with grant option;

Example:

grant all privileges on *.* to 'wjt'@'localhost' identified by "wujuntian" with grant option;

You can use "*" for all databases or all data tables, and "%" for any host address.

You can use grant to add permissions to users repeatedly and overlay permissions.

with grant option: This option indicates that the user can delegate his or her own rights to others.

Remember that 1 must refresh the permissions after authorization:

flush privileges;

5. Mysql user rights recovery:

revoke instruction format:

revoke Permission List on Database Name. Table Name from User Name @ Host;

Example:

revoke select on test.user from wjt@localhost;

Note:

In fact, when the GRANT statement is executed, if there is no target account in the permission table, an account is created; If it already exists, the permissions are added.

usage permissions cannot be reclaimed, that is, REVOKE user permissions do not delete users.

6. Rename the account:

rename user 'Old User Name' @ 'Old Host' to 'New User Name' @ 'New Host';

Example:

show grants;

0

7. Mysql user password modification:

Method 1: Use the set password command.

set password for 'Username' @ 'Host' = password ('New Password');

Example:

show grants;

1

Method 2: Modify the password (or authentication_string) field in the mysql. user table.

Example:

update mysql.user set password=password('123123') where user='root' and host='localhost';

Note:

This method 1 must execute "flush privileges;" Command to refresh permissions, otherwise password changes will not take effect. After Mysql5.7, "password" should be replaced with "authentication_string".

Method 3: Use the grant directive to change the password at authorization time:

grant select on database name. Table name to User name @ Host identified by 'New Password' with grant option;

Example:

grant select on test.user to ajun@localhost identified by '111111' with grant option;

Method 4: Run the mysqladmin script file.

This file 1 is generally in the bin directory under the mysql installation directory. Enter this directory and enter the command according to the two specific situations under 1 (only root users have this permission).

(1) The user does not have a password:

mysqladmin-u Username password New Password;

(2) The user already has a password:

mysqladmin-u User Name-p password New Password;

(Enter and you will be prompted to enter the old password, which can be modified successfully.)

Note:

When changing password, 1 must use PASSWORD function (mysqladmin and GRANT do not need to be written, but will be added automatically).

8. Forgot your password to log in to mysql:

Method 1:

Stop the running Mysql service first, enter the bin directory under the mysql installation directory in the command line window, and run the mysqld file under the parameters of-skip-grant-tables (it is safer for the Linux system to run the mysqld_safe file):

show grants;

2

This allows you to skip Mysql access control and enter the mysql database as an administrator at the console. In addition, open a command line window, enter the bin directory under the mysql installation directory, directly enter: mysql, enter, you can log in to mysql, and then you can reset the password (Note: At this time, only the second method of the four methods in "Mysql User Password Modification" can be used!) . After successful setup, exit and restart Mysql service.

Method 2: Modify the mysql configuration file my. ini.

In fact, there are 11 principles and methods, all of which use the parameters provided by Mysql-skip-grant-tables to skip the access control of Mysql. Open mysql configuration file my. ini, add "skip-grant-tables" under '[mysqld]', save and restart Mysql service, and then you can log in to mysql without password for password modification.

The "mysql" database in Mysql stores the permission information datasheet for all Mysql users. When Mysql is started, all the contents of the permission table are read into the memory, and the contents in the memory are directly used to judge the permission. The modification of permission table with grant, revoke or set password will be noticed by the server immediately. The essence of GRANT operation is to refresh the permission after modifying the permission table. However, if you modify the permission table manually, such as using insert, update, delete, etc., you should execute an flush privileges command, which will cause the server to reread the contents of the permission table into memory, thus making the modification effective. If this command is not executed, the mysql service must be restarted for it to take effect. Therefore, it is best to use grant, revoke or set password to enter the permission table, which can save the trouble of executing flush privileges command, and you will be mad if you forget to execute this command. . .

Moreover, deleting users and renaming users should also use drop user and rename user commands instead of delete and update commands. The former will not only operate on the mysql. user data table, but also update the records of other permission tables, while the latter will only operate on the data of the mysql. user table, which will cause many problems, because the user's permission information does not only exist in the mysql. user table. For example, if you use delete to delete a user in the mysql. user table, However, if other permission data tables are not operated, the permission records of the user in other permission data tables such as tables_priv still exist. If you want to use create user command to create a user with the same name next time, you will fail. You can only use insert into command to insert records into mysql. user table, or delete records related to the user name in other permission data tables first. Renaming a user with update command will also cause great problems. After renaming, the user loses a lot of permissions, while the records about the original user name in other permission tables become useless records, unless you update every permission table in the same way, but this is very troublesome. So, use drop user, rename user, one command can let the system automatically help you complete everything, why not!

Mysql Permission Check:

mysql first check whether you have permission to a large scope, and then check it in a small scope if you don't. For example, first check whether you have select permissions on this database, and if so, allow execution. If not, check whether you have select permissions on the table, 1 until the finest granularity, and no permissions, refuse to execute. Therefore, the finer the granularity control, the more steps of permission verification, and the worse the performance.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Encyclopedia", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Encyclopedia" and "MySQL Common Functions Encyclopedia"

I hope this article is helpful to everyone's MySQL database.