Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you information about how to obtain permissions in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Recently, when I was doing a MySQL data migration, I suddenly found that I had missed a place, that is, the permission information was not exported. If we use mysqldump-- all-databases without adding-- flush-privileges, the exported data will not include mysql database.

But I am actually quite lazy, do not want to export again because of this, then I have several ways to choose.

If I were in the version 5.6 of MySQL 5.5, I could export mysql.user data directly.

If you use scripting, it is basically in this form, which is actually the combined form of show grants for 'xxx', constantly splicing and parsing.

Mysql-e "SELECT DISTINCT CONCAT ('showgrants for',', user,'''@''',host,'''',';') AS query FROM mysql.user where username filled root'" | grep-v query > / tmp/showgrants.sql & & mysql select user,password,authentication_string from mysql.user |

| | user | password | authentication_string | |

| | app_live_im | * E96DB97255EF3ED52454A10EDA1AE7BABC8D3700 |

| | mysqlmon | * 0571D080430BC7B60A3F4D41A8D71501E6B8FDAA | | in MySQL 5.7, the result is different |

+-+

| | user | authentication_string |

+-+

| | gym | * 0CD6502815166F2C7E17B630C3248B900065FCEA |

| | actv_test | * 82A4DC7B3F5E73E822529E9EF4DE8C042253445A | one important difference lies in the field value plugin of the mysql.user table |

Max_connections: 0

Max_user_connections: 0

Plugin: mysql_native_password

Authentication_string:

Password_expired: N

Password_last_changed: 2016-11-09 11:38:39

Password_lifetime: 0

Based on this security policy, there are many things that can be done. 5.7 this mode is the default.

It seems that the information obtained by the previous show grants is very limited, so let's take a look at the effect of the pt tool and run it directly. / pt-show-grants

-- Grants for 'webadmin'@'10.127.8.207'

CREATE USER IF NOT EXISTS 'webadmin'@'10.127.8.207'

ALTER USER 'webadmin'@'10.127.8.207' IDENTIFIED WITH' mysql_native_password' AS'* DA43F144DD67A3F00F086B0DA1288C1D5DA7251F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

GRANT ALL PRIVILEGES ON *. * TO 'webadmin'@'10.127.xx.xx'

Such statements are relatively complete, and the results of using show grants are much less, containing only basic permission information.

> show grants for 'webadmin'@'10.12.20.133'

| GRANT ALL PRIVILEGES ON *. * TO 'webadmin'@'10.12.xx.xxx' | Why you can get more by using the pt tool, not because of how amazing the tool is, but because it takes full advantage of the new features.

This is what it says in pt-show-grants, the way MySQL 5.7is handled.

# If MySQL 5.7.6 + then we need to use SHOW CREATE USER

My @ create_user

If (VersionCompare::cmp ($version, '5.7.6') > = 0) {

Eval {

Create_user = @ {$dbh- > selectcol_arrayref ("SHOW CREATE USER $user_host")}

}

If ($EVAL_ERROR) {

PTDEBUG & & d ($EVAL_ERROR)

$exit_status = 1

}

PTDEBUG & & _ d ('CreateUser:', Dumper (\ @ create_user))

# make this replication safe converting the CREATE USER into

# CREATE USER IF NOT EXISTS and then doing an ALTER USER

My $create = $create_user [0]

My $alter = $create

$create = ~ s {CREATE USER} {CREATE USER IF NOT EXISTS}

$create = ~ s {IDENTIFIED. *} {}

$alter = ~ s {CREATE USER} {ALTER USER}

@ create_user = ($create, $alter)

PTDEBUG & & _ d ('AdjustedCreateUser:', Dumper (\ @ create_user))

}

To simplify is to use show create user as a way to add something on this basis to make this statement more robust.

Using show create user 'webadmin'@'10.12.20.133', we get the following results:

| | CREATE USER 'webadmin'@'10.12.20.133' IDENTIFIED WITH' mysql_native_password' AS'* DA43F144DD67A3F00F086B0DA1288C1D5DA7251F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |

The statement looks plump, but it still seems to be missing some permission information.

This is because the complete information in 5.7is done through show create user and show grants for 'xx', while in 5.6you only need to use show grants for' xxx'.

The above is how to get permission information in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.