Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the access control mechanism of mysql, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand what is the access control mechanism of mysql.

Authority control mechanism

Four tables: user db tables_priv columns_priv privilege privileges

Mysql flush privileges

Mode 2:

Mysqladmin flush-privileges-u root-p1

Five tables linkage (5.7canceled host table)

User table

Login authentication: can users enter mysql to view user

Authority authentication: if you can enter, look at the permissions of the user table. If not, look at the next db table. If the db table does not see tables_priv,tables_priv, go to columns_priv.

The host table db or host will only be used when the host field of the db table is empty. If any select is N, there is no permission.

Use command authorization: grant

You can also create a new account (although this feature will be removed in a later version, it is recommended to use create user)

Syntax format:

Grant permission list on library name. Table name to 'username' @ 'client host' [identified by 'password' with option parameter]

= = permission list all all permissions (excluding authorization permissions)

Select,update

Select (col1), insert (col1,col2) Column level

= = database. Table name *. * all tables under all libraries, all tables under Global level web.* web library, all tables under Database level web.stu_info web library, stu_ info table Table level== client host%. All hosts with host 192.168.2.% 192.168.2.0 network segment 192.168.2.168 designated host localhost specified host

With_option parameter

GRANT OPTION: authorization option

MAX_QUERIES_PER_HOUR: defines the number of queries allowed to be executed per hour

MAX_UPDATES_PER_HOUR: defines the number of updates allowed per hour

MAX_CONNECTIONS_PER_HOUR: defines the number of connections that can be established per hour

MAX_USER_CONNECTIONS: defines the number of connections that a single user can establish at the same time

Mysql > grant select (id), insert (id) on wing.t1 to 'xiaowu'@'172.16.70.%' identified by' 123'

Mysql > grant select,insert on wing.t1 to 'xiaowu'@'172.16.70.%' identified by' 123'

Mysql > grant all on wing.t1 to 'xiaowu'@'172.16.70.%' identified by' 123'

Mysql > grant all on. To 'xiaowu'@'172.16.70.%' identified by' 123'

Mysql > grant all on. To 'xiaowu'@'172.16.70.%'

Mysql > grant all on. To 'xiaowu'@'%'

View permissions

Look at your own permissions:

SHOW GRANTS\ G

Look at other people's permissions: SHOW GRANTS FOR admin1@'%'\ G

Revoke permission: revoke

Syntax:

REVOKE permission list ON database name FROM user name @ 'client host'

Mysql > revoke all on *. * from 'xiaowu'@'%';mysql > revoke delete on *. * from admin1@'%'; / / Recycle partial permissions REVOKE ALL PRIVILEGES ON *. * FROM admin2@'%'; / / Recycle all permissions

REVOKE ALL PRIVILEGES,GRANT OPTION ON *. * FROM 'admin2'@'%'

After reading the above about what is the access control mechanism of mysql, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

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.