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

This article mainly introduces the method of authority management in MySQL, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

The permission table of MySQL is loaded into memory when the database is started, and when the user is authenticated, the corresponding permissions are accessed in memory, so that the user can do all kinds of operations within the scope of authority in the database.

I. access to the right list

In the two processes of permission access, the system will use the three most important permission tables, user, host and db, in the "mysql" database (created when installing MySQL and the database name is "mysql").

Of the three tables, the most important table represents the user table, followed by the db table, and the host table is not used in most cases.

The columns in user are mainly divided into four parts: user column, permission column, security column, and resource control column.

User columns and permissions columns are commonly used, in which permissions columns are divided into general permissions and administrative permissions. Ordinary permissions are used for database operations, such as select_priv, super_priv, and so on.

When a user connects, the access process of the permission table has the following two processes:

First, from the host, user and password fields in the user table, determine whether the connected IP, user name, and password exist in the table, and if so, pass authentication, otherwise reject the connection.

If authenticated, the database permissions are obtained in the following order: user-> db-> tables_priv-> columns_priv.

In these permission tables, the scope of permissions decreases in turn, and the global permissions cover the local permissions. The first stage above is easy to understand. Let's explain the second stage in detail with an example.

To facilitate testing, you need to modify the variable sql_mode

/ / NO_AUTO_CREATE_USER (prevents GRANT from automatically creating new users unless a password is also specified) NO_AUTO_CREATE_USER is included in the sql_mode default value, SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';1. Create user zj@localhost and grant select permissions to all tables on all databases MySQL [mysql] > grant select on *. * to zj@localhost;Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql] > select * from user where user= "zj" and host='localhost'\ G * * 1. Row * Host: localhost User: zj Select_priv: y Insert_priv: n Update_priv: n Delete_priv: n Create_priv: N Drop_priv: N Reload_priv: N...2. View the db table MySQL [mysql] > select * from db where user='zj'\ G; Empty set (0.00 sec)

You can find that the select_priv column of the user table is "Y", while there is no record in the db table, that is, users who have the same permissions for all databases do not need to log to the db table, but only need to change the select_priv in the user table to "Y". In other words, each permission in the user table represents permissions for all databases.

3. Change the permissions on zj@localhost to select permissions only for all tables on the T2 database. MySQL [mysql] > revoke select on *. * from zj@localhost;Query OK, 0 rows affected, 1 warning (0.02 sec) MySQL [mysql] > grant select on t2.* to zj@localhost;Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL [mysql] > select * from user where user='zj'\ G * * 1. Row * * Host: localhost User: zj Select_priv: n Insert_priv: n Update_priv: n Delete_priv: n Create_priv: n Drop_priv: n Reload_priv: N...MySQL [mysql] > select * from db where user='zj'\ G * * 1. Row * * Host: localhost Db: T2 User: zj Select_priv: y Insert_priv: n Update_priv: n Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N

At this point, it is found that the select_priv in the user table becomes "N", while a record with db T2 is added to the db table. That is, when only part of the database is granted certain permissions, the corresponding permission column in the user table remains "N" and the specific database permissions are written to the db table. The permission mechanisms of table and column are similar to db.

As can be seen from the above example, when a user passes permission authentication and assigns permissions, the permissions will be assigned in the order of user-> db-> tables_priv-> columns_priv, that is, check the global permissions table user first. If the corresponding permission in user is "Y", then the user's permissions to all databases are "Y", and db, tables_priv and columns_priv will no longer be checked. If it is "N", check the specific database corresponding to this user in the db table, and get the permission of "Y" in db; if the corresponding permission in db is "N", then check the permissions in tables_priv and columns_priv in turn, and if all are "N", it is judged that there is no permission.

II. Account management

It mainly includes the creation of account, the change of authority and the deletion of account.

1. Create an account

Created using grant syntax, for example:

(1) create a user zj with permissions that can execute all permissions on all databases and can only connect locally. MySQL [mysql] > grant all privileges on *. * to zj@localhost;Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql] > select * from user where user= "zj" and host= "localhost"\ G * * 1. Row * * Host: localhost User: zj Select_priv: y Insert_priv: y Update_priv: y Delete_priv: y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y

You can find that all permissions except grant_priv permissions are "Y" in the user table.

(2) on the basis of (1), increase the grant permission to zj MySQL [(none)] > grant all privileges on *. * to zj@localhost with grant option;Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql] > select * from user where user= "zj" and host='localhost'\ G * * 1. Row * * Host: localhost User: zj Select_priv: y Insert_priv: y Update_priv: y Delete_priv: y Create_priv: y Drop_priv: y Reload_priv: y Shutdown_priv: y Process_priv: y File_priv: y Grant_priv: y... (3) based on (2) Set the password to "123" MySQL [mysql] > grant all privileges on *. * to zj@localhost identified by '123' with grant option Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql] > select * from user where user= "zj" and host= "localhost"\ G * * 1. Row * * Host: localhost User: zj Select_priv: y Insert_priv: y Update_priv: y Delete_priv: y Create_priv: Y Drop_priv: Y Reload_priv: Y. Authentication_string: * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: n password_last_changed: 2017-09-25 20:29:42 password_lifetime: NULL

As you can see, the password becomes a bunch of encrypted strings.

(4) create a new user zj2, which can be connected from any IP. The permission is to perform select, update, insert and delete operations on all tables in the T2 database. The initial password is "123" MySQL [mysql] > grant select, insert, update,delete on t 2.* to 'zj2'@'%' identified by' 123 queries OK, 0 rows affected, 1 warning (0.00 sec) MySQL [mysql] > select * from user where user='zj2' and host= "%"\ G * * 1. Row * * Host:% User: zj2 Select_priv: n Insert_priv: n Update_priv: n Delete_priv : N Create_priv: N Drop_priv: N. Authentication_string: * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: n password_last_changed: 2017-09-25 20:37:49 password_lifetime: NULLMySQL [mysql] > select * from db where user= "zj2" and host='%'\ G * * 1. Row * * Host:% Db: T2 User: zj2 Select_priv: y Insert_priv: y Update_priv: y Delete_priv: Y Create_priv: N Drop_priv: N.

The permissions in the user table are all "N", and the record permissions added in the db table are all "Y". In general, only appropriate permissions are granted to the user, not too many permissions.

The IP in this example is limited to all IP can be connected, so it is set to "*". The mysql database is controlled by the host field of the user table, and host can be the following type of assignment.

The Host value can be the hostname or IP number, or "localhost" indicates the local host.

You can use the wildcard characters "%" and "_" in Host column values

The Host value "%" matches any hostname, and empty Host values are equivalent to "%" and have the same meaning as the pattern matching operation of the like operator.

Note: the value of host in the user table of the mysql database is "*" or empty, indicating that all external IP can be connected, but does not include the local server localhost, so if you want to include the local server, you must grant permissions to localhost separately.

(5) Grant super, process and file permissions to users zj3@%MySQL [mysql] > grant super,process,file on *. * to 'zj3'@'%';Query OK, 0 rows affected, 1 warning (0.00 sec)

Because these permissions belong to administrative permissions, you cannot specify a database. On must be followed by ".". The following syntax will indicate an error

MySQL [mysql] > grant super,process,file on t2.* to 'zj3'@'%';ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES (6) only grants login permissions to zj4@localhostMySQL [mysql] > grant usage on *. * to' zj4'@'localhost';Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql] > exitByezj@bogon:~$ mysql-uzj4-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 78Server version: 5.7.18-log Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MySQL [(none)] > show databases;+-+ | Database | +-+ | information_schema | +-+ 1 row in set (0.02 sec)

Usage permissions can only be used for database login and cannot perform any operations.

two。 View account permissions

After the account is created, you can view the permissions by using the following command:

Show grants for user@host

Example:

MySQL [(none)] > show grants for zj@localhost +-+ | Grants for zj@localhost | + -+ | GRANT ALL PRIVILEGES ON *. * TO 'zj'@'localhost' WITH GRANT OPTION | +-- -+ 1 row in set (0.01 sec) 3. Change account permissions

Permissions can be added and reclaimed. Just like creating an account, there are two ways to change permissions: use grant and revoke statements, or change the permission table.

Example: (1) zj4@localhost only has login permission MySQL [(none)] > show grants for zj4@localhost. +-- + | Grants for zj4@localhost | +-+ | GRANT USAGE ON *. * TO 'zj4'@'localhost' | +- -- + 1 row in set (0.00 sec) (2) give zj4@localhost select permissions for all tables on all databases MySQL [(none)] > grant select on *. * to 'zj4'@'localhost' Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)] > show grants for zj4@localhost +-- + | Grants for zj4@localhost | +-- + | GRANT SELECT ON *. * TO 'zj4'@'localhost' | + -- + 1 row in set (0.00 sec) (3) continue to grant select and insert permissions to zj4@localhost Merge MySQL [(none)] > show grants for 'zj4'@'localhost' with existing select permissions +-+ | Grants for zj4@localhost | +-+ | GRANT SELECT INSERT ON *. * TO 'zj4'@'localhost' | +-+ 1 row in set (0.00 sec)

The revoke statement can reclaim the permissions that have been granted. For the above example, it is decided to revoke the insert and select permissions on the zj4@localhost:

MySQL [(none)] > revoke select,insert on *. * from zj4@localhost;Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)] > show grants for zj4@localhost +-- + | Grants for zj4@localhost | +-+ | GRANT USAGE ON *. * TO 'zj4'@'localhost' | +- -- + 1 row in set (0.00 sec)

Usage permissions cannot be reclaimed, that is, revoke users cannot delete users.

4. Change the account password (1) you can use the mysqladmin command to specify the password on the command line. Shell > mysqladmin-u user_name-h host_name password "123456" (2) executes the set password statement. Mysql > set password for 'username'@'%' = password (' pwd')

If you are changing your password, you can omit the for statement

Mysql > set password=password ('pwd'); (3) you can use grant usage statements at the global level (in ".") To specify an account password without affecting the current permissions of the account. Mysql > grant usage on *. * to 'username'@'%' identified by' pwd';5. Delete an account

To completely delete an account, you can use drop user:

Drop user zj@localhost;6. Account resource limit

When creating a MySQL account, there is another type of option called account resource limit, which limits the actual resource limit of each account. The "resources" here mainly include:

Max_queries_per_hour count: the number of queries executed per hour by a single account

Max_upodates_per_hour count: the number of updates per hour performed by a single account

Max_connections_per_hour count: the number of times a single account connects to the server per hour

Max_user_connections count: the number of times a single account connects to the server concurrently

Column

No regrets about losing chess.

Article details

Waterandair

955 released in falling chess with no regrets.

Released on 2017-10-30

MySQL rights management

Authority control

Mysql

It takes 38 minutes to read 1.5k times.

Undefined

four

The permission table of MySQL is loaded into memory when the database is started, and when the user is authenticated, the corresponding permissions are accessed in memory, so that the user can do all kinds of operations within the scope of authority in the database.

I. access to the right list

In the two processes of permission access, the system will use the three most important permission tables, user, host and db, in the "mysql" database (created when installing MySQL and the database name is "mysql").

Of the three tables, the most important table represents the user table, followed by the db table, and the host table is not used in most cases.

The columns in user are mainly divided into four parts: user column, permission column, security column, and resource control column.

User columns and permissions columns are commonly used, in which permissions columns are divided into general permissions and administrative permissions. Ordinary permissions are used for database operations, such as select_priv, super_priv, and so on.

When a user connects, the access process of the permission table has the following two processes:

First, from the host, user and password fields in the user table, determine whether the connected IP, user name, and password exist in the table, and if so, pass authentication, otherwise reject the connection.

If authenticated, the database permissions are obtained in the following order: user-> db-> tables_priv-> columns_priv.

In these permission tables, the scope of permissions decreases in turn, and the global permissions cover the local permissions. The first stage above is easy to understand. Let's explain the second stage in detail with an example.

To facilitate testing, you need to modify the variable sql_mode

/ / NO_AUTO_CREATE_USER (prevents GRANT from automatically creating new users unless a password is also specified) NO_AUTO_CREATE_USER is included in the sql_mode default value, SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';1. Create user zj@localhost and grant select permissions to all tables on all databases MySQL [mysql] > grant select on *. * to zj@localhost;Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql] > select * from user where user= "zj" and host='localhost'\ G * * 1. Row * Host: localhost User: zj Select_priv: y Insert_priv: n Update_priv: n Delete_priv: n Create_priv: N Drop_priv: N Reload_priv: N...2. View the db table MySQL [mysql] > select * from db where user='zj'\ G; Empty set (0.00 sec)

You can find that the select_priv column of the user table is "Y", while there is no record in the db table, that is, users who have the same permissions for all databases do not need to log to the db table, but only need to change the select_priv in the user table to "Y". In other words, each permission in the user table represents permissions for all databases.

3. Change the permissions on zj@localhost to select permissions only for all tables on the T2 database. MySQL [mysql] > revoke select on *. * from zj@localhost;Query OK, 0 rows affected, 1 warning (0.02 sec) MySQL [mysql] > grant select on t2.* to zj@localhost;Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL [mysql] > select * from user where user='zj'\ G * * 1. Row * * Host: localhost User: zj Select_priv: n Insert_priv: n Update_priv: n Delete_priv: n Create_priv: n Drop_priv: n Reload_priv: N...MySQL [mysql] > select * from db where user='zj'\ G * * 1. Row * * Host: localhost Db: T2 User: zj Select_priv: y Insert_priv: n Update_priv: n Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N

At this point, it is found that the select_priv in the user table becomes "N", while a record with db T2 is added to the db table. That is, when only part of the database is granted certain permissions, the corresponding permission column in the user table remains "N" and the specific database permissions are written to the db table. The permission mechanisms of table and column are similar to db.

As can be seen from the above example, when a user passes permission authentication and assigns permissions, the permissions will be assigned in the order of user-> db-> tables_priv-> columns_priv, that is, check the global permissions table user first. If the corresponding permission in user is "Y", then the user's permissions to all databases are "Y", and db, tables_priv and columns_priv will no longer be checked. If it is "N", check the specific database corresponding to this user in the db table, and get the permission of "Y" in db; if the corresponding permission in db is "N", then check the permissions in tables_priv and columns_priv in turn, and if all are "N", it is judged that there is no permission.

II. Account management

It mainly includes the creation of account, the change of authority and the deletion of account.

1. Create an account

Created using grant syntax, for example:

(1) create a user zj with permissions that can execute all permissions on all databases and can only connect locally. MySQL [mysql] > grant all privileges on *. * to zj@localhost;Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql] > select * from user where user= "zj" and host= "localhost"\ G * * 1. Row * * Host: localhost User: zj Select_priv: y Insert_priv: y Update_priv: y Delete_priv: y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y

You can find that all permissions except grant_priv permissions are "Y" in the user table.

(2) on the basis of (1), increase the grant permission to zj MySQL [(none)] > grant all privileges on *. * to zj@localhost with grant option;Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql] > select * from user where user= "zj" and host='localhost'\ G * * 1. Row * * Host: localhost User: zj Select_priv: y Insert_priv: y Update_priv: y Delete_priv: y Create_priv: y Drop_priv: y Reload_priv: y Shutdown_priv: y Process_priv: y File_priv: y Grant_priv: y... (3) based on (2) Set the password to "123" MySQL [mysql] > grant all privileges on *. * to zj@localhost identified by '123' with grant option Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql] > select * from user where user= "zj" and host= "localhost"\ G * * 1. Row * * Host: localhost User: zj Select_priv: y Insert_priv: y Update_priv: y Delete_priv: y Create_priv: Y Drop_priv: Y Reload_priv: Y. Authentication_string: * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: n password_last_changed: 2017-09-25 20:29:42 password_lifetime: NULL

As you can see, the password becomes a bunch of encrypted strings.

(4) create a new user zj2, which can be connected from any IP. The permission is to perform select, update, insert and delete operations on all tables in the T2 database. The initial password is "123" MySQL [mysql] > grant select, insert, update,delete on t 2.* to 'zj2'@'%' identified by' 123 queries OK, 0 rows affected, 1 warning (0.00 sec) MySQL [mysql] > select * from user where user='zj2' and host= "%"\ G * * 1. Row * * Host:% User: zj2 Select_priv: n Insert_priv: n Update_priv: n Delete_priv : N Create_priv: N Drop_priv: N. Authentication_string: * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: n password_last_changed: 2017-09-25 20:37:49 password_lifetime: NULLMySQL [mysql] > select * from db where user= "zj2" and host='%'\ G * * 1. Row * * Host:% Db: T2 User: zj2 Select_priv: y Insert_priv: y Update_priv: y Delete_priv: Y Create_priv: N Drop_priv: N.

The permissions in the user table are all "N", and the record permissions added in the db table are all "Y". In general, only appropriate permissions are granted to the user, not too many permissions.

The IP in this example is limited to all IP can be connected, so it is set to "*". The mysql database is controlled by the host field of the user table, and host can be the following type of assignment.

The Host value can be the hostname or IP number, or "localhost" indicates the local host.

You can use the wildcard characters "%" and "_" in Host column values

The Host value "%" matches any hostname, and empty Host values are equivalent to "%" and have the same meaning as the pattern matching operation of the like operator.

Note: the value of host in the user table of the mysql database is "*" or empty, indicating that all external IP can be connected, but does not include the local server localhost, so if you want to include the local server, you must grant permissions to localhost separately.

(5) Grant super, process and file permissions to users zj3@%MySQL [mysql] > grant super,process,file on *. * to 'zj3'@'%';Query OK, 0 rows affected, 1 warning (0.00 sec)

Because these permissions belong to administrative permissions, you cannot specify a database. On must be followed by ".". The following syntax will indicate an error

MySQL [mysql] > grant super,process,file on t2.* to 'zj3'@'%';ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES (6) only grants login permissions to zj4@localhostMySQL [mysql] > grant usage on *. * to' zj4'@'localhost';Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql] > exitByezj@bogon:~$ mysql-uzj4-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 78Server version: 5.7.18-log Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MySQL [(none)] > show databases;+-+ | Database | +-+ | information_schema | +-+ 1 row in set (0.02 sec)

Usage permissions can only be used for database login and cannot perform any operations.

two。 View account permissions

After the account is created, you can view the permissions by using the following command:

Show grants for user@host

Example:

MySQL [(none)] > show grants for zj@localhost +-+ | Grants for zj@localhost | + -+ | GRANT ALL PRIVILEGES ON *. * TO 'zj'@'localhost' WITH GRANT OPTION | +-- -+ 1 row in set (0.01 sec) 3. Change account permissions

Permissions can be added and reclaimed. Just like creating an account, there are two ways to change permissions: use grant and revoke statements, or change the permission table.

Example: (1) zj4@localhost only has login permission MySQL [(none)] > show grants for zj4@localhost. +-- + | Grants for zj4@localhost | +-+ | GRANT USAGE ON *. * TO 'zj4'@'localhost' | +- -- + 1 row in set (0.00 sec) (2) give zj4@localhost select permissions for all tables on all databases MySQL [(none)] > grant select on *. * to 'zj4'@'localhost' Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)] > show grants for zj4@localhost +-- + | Grants for zj4@localhost | +-- + | GRANT SELECT ON *. * TO 'zj4'@'localhost' | + -- + 1 row in set (0.00 sec) (3) continue to grant select and insert permissions to zj4@localhost Merge MySQL [(none)] > show grants for 'zj4'@'localhost' with existing select permissions +-+ | Grants for zj4@localhost | +-+ | GRANT SELECT INSERT ON *. * TO 'zj4'@'localhost' | +-+ 1 row in set (0.00 sec)

The revoke statement can reclaim the permissions that have been granted. For the above example, it is decided to revoke the insert and select permissions on the zj4@localhost:

MySQL [(none)] > revoke select,insert on *. * from zj4@localhost;Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)] > show grants for zj4@localhost +-- + | Grants for zj4@localhost | +-+ | GRANT USAGE ON *. * TO 'zj4'@'localhost' | +- -- + 1 row in set (0.00 sec)

Usage permissions cannot be reclaimed, that is, revoke users cannot delete users.

4. Change the account password (1) you can use the mysqladmin command to specify the password on the command line. Shell > mysqladmin-u user_name-h host_name password "123456" (2) executes the set password statement. Mysql > set password for 'username'@'%' = password (' pwd')

If you are changing your password, you can omit the for statement

Mysql > set password=password ('pwd'); (3) you can use grant usage statements at the global level (in ".") To specify an account password without affecting the current permissions of the account. Mysql > grant usage on *. * to 'username'@'%' identified by' pwd';5. Delete an account

To completely delete an account, you can use drop user:

Drop user zj@localhost;6. Account resource limit

When creating a MySQL account, there is another type of option called account resource limit, which limits the actual resource limit of each account. The "resources" here mainly include:

Max_queries_per_hour count: the number of queries executed per hour by a single account

Max_upodates_per_hour count: the number of updates per hour performed by a single account

Max_connections_per_hour count: the number of times a single account connects to the server per hour

Max_user_connections count: the number of times a single account connects to the server concurrently

Thank you for reading this article carefully. I hope it will be helpful for everyone to share the methods and contents of MySQL rights management. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.