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

This article shares with you the content of a sample analysis of MariaDB/MySQL users and rights management. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The user in MariaDB/MySQL consists of a user name and a host name, such as "root@localhost". The same user name but different host names are different for MySQL/MariaDB, which means that "root@localhost" and "root@127.0.0.1" are different users, even though they are both native root.

After the MariaDB/MySQL server starts, the permission table will be loaded into memory. When users want to connect to the server, they will read the permission table to verify and assign permissions, that is, to read and write permissions in memory.

The permission system in MariaDB/MySQL has been verified in two steps:

1. Validity verification: verify whether the user is legal. Legitimate users are allowed to connect to the server, otherwise the connection is denied.

two。 Permission verification and allocation: assign the operation rights to the objects in the database to the users who have passed the validity verification.

1.1 permissions table

The permission tables in MariaDB/MySQL are stored in the mysql database. Before MySQL5.6, the tables related to permissions were the user table, the db table, the host table, the tables_ privtable, the columns_ privtable, and the procs_ privtable (permissions related to stored procedures and functions). Since MySQL5.6, the host table is gone. Although there is a host table in MariaDB, it is not used.

User is the most frequently used table in these tables. The user table is mainly divided into several parts: user column, permission column, security column, resource control column, and miscellaneous column. The most important columns are the user column and permission column. The permission column is divided into general permissions (red font in the above table) and administrative permissions columns, such as ordinary permissions for select class and administrative permissions for super permissions. As you can see, the permissions in the db table are all ordinary permissions. In addition to the normal permissions in the db table, there are show_db_pirv and create_tablespace_priv, and there are several administrator privileges in the user table. That is, permissions that are not available in db cannot be granted to the specified database. For example, you cannot grant super permissions to an test database.

In addition, the usage permission is not listed in the above table, because this permission is the right of all users, and it is only used to indicate whether you can log in to the database. One of its special features is that when grant only specifies this permission, it will not affect the existing permissions, that is, you can use grant to change the password without affecting the existing permissions.

It is important to note that from the user table to the db table to the tables_ private table and finally to the columns_ private table, their permissions are refined layer by layer. The normal permissions in the user table are for all databases. For example, if the select_priv in the user table is Y, then you have select permissions for all databases. The db table is for all tables in a specific database. If there is only select permission in the test database, then there is a record in the db table that the select permission of the test database is Y, so you have select permission for all tables in the test database, and the select permission in the user table is N (because all databases have permissions when it is Y). Similarly, the tables_priv table has permissions for all columns in a specific table. Columns_priv is the permission for a specific column.

Therefore, the mechanism for reading and assigning permissions to users who have passed identity legitimacy verification is as follows:

1. Read the uesr table to see if the user table has a permission column corresponding to Y, and assign it if so.

two。 Read the db table to see if any database in the db table has the corresponding permissions assigned.

3. Read the tables_priv table to see which tables have corresponding permissions.

4. Read the columns_priv table to see which specific columns have what permissions.

For example, give a user select permission for the test database. You can see that the select_priv in the user table is N and the select in the db table is Y.

GRANT SELECT ON test.* TO 'long'@'192.168.100.1' IDENTIFIED BY' 123456 * select host,user,select_priv FROM mysql.user;SELECT * FROM mysql.db

1.2 illustrate the two stages of authentication and permission allocation

1.3 the time when the authority takes effect

The permission table is read into memory when the server starts, and the permission table takes effect from then on.

After that, using grant, revoke, set password and other commands will also implicitly refresh the permission table to memory.

In addition, using the explicit command flush privileges or mysqladmin flush-privileges or mysqladmin reolad will also re-brush the above permission tables into memory for subsequent authentication and permission verification and allocation.

User management is divided into several aspects, creating users, authorizing users, modifying and deleting users.

2.1 create a user

There are several ways to create an account.

1. Use grant to directly authorize the account. If the account does not exist, it will be created.

two。 Insert a record into the mysql.user table

3. Use the create user command.

The users created by the latter two methods do not have any permissions initially (only the permission for usage to log in to the database), and after modifying the permissions, use FLUSH PRIVILEGES statements or execute mysqladmin flush-privileges or mysqladmin reload commands to refresh the permissions table to memory, while the first method is much easier, and the permissions table will be refreshed automatically after the user is created.

Grant and revoke syntax:

GRANT priv_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] priv_level TO user [IDENTIFIED [BY [PASSWORD] 'password'] [WITH with_option [with_option] object_type: TABLE | FUNCTION | PROCEDUREpriv_level: * | *. * | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_namewith_option: GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | | MAX_USER_CONNECTIONS count | MAX_STATEMENT_TIME time |

Grant can authorize on libraries, tables, functions, stored procedures, and specific columns, and can grant permissions to multiple objects to multiple users at one time. With grant option indicates that users who have this permission can grant their own permissions to other users.

Revoke means to withdraw permissions. Note that revoke cannot revoke usage permissions.

The user is expressed as' user name'@ 'hostname', and the hostname part can be a hostname, an IP address, a localhost, or a hostname composed of wildcards (empty host values also represent all host, which is equivalent to 'user_name'@'%'). The following is an example:

For network segment addresses, you can specify a mask, such as 192.168.100.1Universe 255.255.255.0. You cannot use the mask recording method in cidr format, nor can you specify masks other than 8, 16, 24, and 32 bits. For example, 192.168.100.1 Universe 255.255.255.240 is not allowed.

If the user in the user table has a cross section, such as root, which can log in either from localhost or 127.0.0.1, from native IP192.168.100.61, or from the network segment address 192.168.100.00, which login will it be from?

When reading the permission table user into memory, it will first sort according to the concreteness of the host column, then sort concretely according to the user column (that is, understood as order by host,user), and then scan from top to bottom, and log in with the record that matches the first scan. Concreteness means that the higher the specific user priority, the lower the user priority for the wider range of wildcards. For example, the concreteness of root@localhost is higher than that of root@'%', and the concreteness of the latter is higher than that of'% @'.

2.2 create user and alter user

Do not use these two commands to create and modify users before MySQL 5.6.7, as they set an empty string in the password column of the mysql.user table. To mysql5.6.7 to solve this problem. MariaDB can be used at will.

Syntax:

CREATE [OR REPLACE] USER [IF NOT EXISTS] user_specification [, user_specification]... [WITH resource_option [resource_option]...] user_specification: username [authentication_option] authentication_option: IDENTIFIED BY 'authentication_string' resource_option: MAX_QUERIES_PER_HOUR count | MAX_UPDATE_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count

For example:

Create user 'longshuai'@'127.0.0.1' identified by' 123456'

The syntax of alter user and create user are basically the same, but there is a feature to expire passwords in MySQL, which is not supported in MariaDB.

ALTER USER user_specification [, user_specification]... user_specification: user PASSWORD EXPIRE

For example, expire the user you just created.

Alter user 'longshuai'@'127.0.0.1' password expire;2.3 records the time the user was created

The metadata information of user in MariaDB/MySQL is stored in the mysql.user table, but there are few categories of information in this table, only user class columns and permission class columns are commonly used, and there is no user creation time.

You can record the user's creation time by adding a new column.

Alter table mysql.user add column create_time timestamp default current_timestamp

In this way, the new user will record the creation time later. But obviously, there is no time record for existing users, and their values are all '0000-00-0000: 00: 00'.

MariaDB [mysql] > select host,user,create_time from mysql.user +-+ | host | user | create_time | +- -+ | localhost | root | 2018-04-21 05:58:19 | | 127.0.0.1 | root | 2018-04-21 05:58:19 | |:: 1 | root | 2018-04-21 05:58:19 | | localhost | | 2018-04-21 05:58:19 | 192.168.100 .% | root | 2018-04-21 05:58:19 | | 192.168.100.1 | long | 2018-04-21 05:58:19 | | 127.0.0.1 | longshuai | 2018-04-21 05:58:19 | | 192.168.100.1 | longshuai | 0000-00-0000: 00:00 | +-- -+-+ 2.4 View user permissions

You can use the show grants statement to view permission information for a user.

For example:

MariaDB [mysql] > show grants for 'root'@'localhost' Grants for root@localhost -- GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION GRANT PROXY ON'@'TO 'root'@'localhost' WITH GRANT OPTIONMariaDB [mysql] > SHOW GRANTS FOR' long'@'192.168.100.1'

Grants for long@192.168.100.1-GRANT USAGE ON *. * TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' GRANT SELECT ON `test`s strictness of the TO 'long'@'192.168.100.1'2.5 revoke command

The revoke command must explicitly specify the reclaimed database object and user name when reclaiming permissions, in which the usage permission cannot be reclaimed. In particular, revoke all may not be reclaimed at all when you think it will reclaim all permissions. In other words, the writing of the revoke command is very strict.

User 'long'@'192.168.100.1' has usage permissions on *. * and select permissions on test.*.

MariaDB [mysql] > SHOW GRANTS FOR 'long'@'192.168.100.1' Grants for long@192.168.100.1-GRANT USAGE ON *. * TO 'long'@'192.168.100.1' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' GRANT SELECT ON `test`. * TO 'long'@'192.168.100.1'

Revoke all the user on *. *, check the permissions again, and find that the permissions have not changed at all. Because the usage permission cannot be reclaimed, and the select permission is on test.* rather than on *. *.

REVOKE ALL ON *. * FROM 'long'@'192.168.100.1'

To reclaim select permissions on test.*, you must specify test.*, in revoke instead of *. *. Both of the following statements can be recycled.

Revoke select on test.* from 'long'@'192.168.100.1';revoke all on test.* from' long'@'192.168.100.1';2.6 delete user

Use the drop user command directly or delete the corresponding record from the mysql.user table.

Drop user user_name1,username2...

Note that when deleting a user record in a table, the permissions on the table will not be reclaimed from the existing users, and the permissions granted to the user will automatically cause an outflow of permissions when the table with the same name is created next time.

Therefore, it is recommended that you use the drop user statement to delete the user.

Set password (1) grant all on *. * to 'root'@'localhost' identified by' 123456 'with grant option; (2) grant usage on *. * to' root'@'localhost' identified by '123456' with grant option

Using usage permissions means to use grant to change passwords without affecting existing permissions.

(3) set password [for 'root'@'localhost'] = password (' 123456')

The password function must be in quotation marks. When user is not written, it is modified for the current user.

(4) alter user root@localhost identified by '123456; (5) mysqladmin-uroot-h localhost-paired password' password' new_password'; (6) update mysql.user set password=password ('123456') where user='root' and host='localhost'

The grant and set password statements can refresh the permission table directly, and the other statements need to use flush privileges or other refresh statements.

3.2 recover root password

You can use the mysqld_safe service program when starting the mysql service and specify the "--skip-grant-tables" option to skip the authorization table, so that you do not need any permissions to log in to the MySQL server, including password authentication, but it is also restricted that you cannot operate any permission-related content, such as modifying permissions, refreshing authorization tables, and so on. This is usually the option used when the mysql administrator's password is forgotten. Because skipping the authorization table makes the mysql server extremely insecure, any user can log in directly to the server, so it is usually used with the "--skip-networking" option to disable server connection requests from the network, so you can only log in using localhost or 127.0.0.1 as host.

In addition, stop existing MySQL instances before using mysqld_safe to start services without authorization tables. Because you cannot manipulate permission-related content by skipping the authorization table, changing the password field of the administrator account in the mysql.user table is the only way to modify it. Remember to restart the MySQL service after changing the password.

The steps are as follows:

[root@xuexi mysql] # service mysqld stop [root@xuexi mysql] # mysqld_safe-- skip-grant-tables-- skip-networking & [root@xuexi mysql] # mysqlmysql > update mysql.user set password=password ("123456") where user='root' and host='localhost';mysql > flush privileges;mysql > select user,host,password from mysql.user where user='root' and host='localhost' +-+ | user | host | password | +- -- + | root | localhost | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-- + 1 row in setmysql >\ Q [root@xuexi mysql] # service mysqld stop [root@xuexi Mysql] # service mysqld start [root@xuexi mysql] # mysql-uroot-p123456mysql >\ Q

If you want to retrieve the password for multiple instances, use-- defaults-file in the mysqld_safe command to specify the corresponding configuration file.

Thank you for reading! This is the end of this article on "sample Analysis of MariaDB/MySQL users and Rights Management". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.