MySQL permission control details analysis

Today is Sunday. I was lazy for a while in the morning and got up a little late. I had nothing to do at noon, so I re-looked at the permission control module in MySQL. Looking back again, there are still many details that I gained a lot. I will record them here for your later review.
Regarding the content of permissions, we have written some of them in the previous article on March 11. Today, we will write about them one by one using detailed knowledge points (MySQL version used in this article is 5.7.16). Before writing these knowledge points, we first introduce the granularity of MySQL permission control, and then understand the verification work done by the server when the client initiates a request in MySQL. Let's first look at the granularity of permission control:

1. Global level

Global privileges apply to all databases in a given server. These privileges are stored in the mysql.user table and are granted or revoked using the grant all on *.* and revoke all on *.* methods.

2. Database level

Database permissions apply to all objects in a given database, including table objects and stored procedures. These permissions are stored in the mysql.db table. Database permissions can be granted and revoked using grant all on db_name.* or the corresponding revoke method.

3. Surface level

Table privileges apply to all columns in a given table. These privileges are stored in the mysql tables_priv table. Privileges are generally granted or revoked using grant all on db_name.tbl_name and the corresponding revoke statement.

4. Column-level permissions

Column-level permissions apply to specified columns in a given table. These permissions are stored in the mysql.columns_priv table. Because this permission is not commonly used, here is an example of how to grant it, as follows:

First, we create a user with select privileges for the yeyztest.test1 table:

mysql:mysql 19:35:38>>show grants for dba_yeyz@'192.168.18.%';
+------------------------------------------------------------------------------+
| Grants for [email protected].% |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%' |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Then we update a field id in test1, and the result is as follows:

mysql> select * from test1;
+---------------+
|id|
+---------------+
| 22 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)

mysql> update test1 set id=2 where id=22;
ERROR 1142 (42000): UPDATE command denied to user 'dba_yeyz'@'192.168.18.**' for table 'test1'

Of course, we cannot update it. At this time, we use the root account to give the dba_yeyz account a permission for the id column, and then look at its results:

mysql:mysql 19:38:38>>show grants for dba_yeyz@'192.168.18.%';
+------------------------------------------------------------------------------+
| Grants for [email protected].% |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%' |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
| GRANT SELECT, UPDATE (id) ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Here we need to pay attention to the statement that adds permissions to the field, that is:

grant update (id) on yeyztest.test1 to XXXXXX

That is, follow the name of the field after the permission.

At this time, we query the columns_priv table and see that the records are:

mysql:mysql 19:39:46>>select * from columns_priv;
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| 192.168.18.% | yeyztest | dba_yeyz | test1 | id | 0000-00-00 00:00:00 | Update |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)

Use dba_yeyz to perform the update operation again, and you can see the result:

mysql> update test1 set id=2 where id=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test1;
+---------------+
|id|
+---------------+
| 2 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)

The column with id=22 was successfully changed to id=2.

5. Subroutine level

The create routine, alter routine, execute, and grant privileges apply to stored routines. These privileges can be granted at the global level and the database level and can be stored in mysql.procs_priv.

When the client initiates a request, the flowchart of the MySQL server verifying the request is as follows:

I will list some of the details I saw today one by one, hoping it will be helpful to everyone:

1. In MySQL version 5.7.16, there is no host table in the mysql system library. There are only five tables related to permission control, namely user, db, table_priv, proc_priv, and column_priv.

2. The primary key of the mysql.user table is composed of user and host. Let's look at the table structure:

[email protected]:mysql 19:44:56>>show create table mysql.user\G
*************************** 1. row ***************************
    Table: user
Create Table: CREATE TABLE `user` (
 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
---------------Authorization fields (29)--------------
 `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 ......Omitted here---------------Security fields (4)---------------
 `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
 `ssl_cipher` blob NOT NULL,
 `x509_issuer` blob NOT NULL,
 `x509_subject` blob NOT NULL,
---------------Resource control fields (4)--------------
 `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
 `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
 `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
 `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
--------------Plugin field (1 item)---------------
 `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
--------------Password fields (5)--------------
 `authentication_string` text COLLATE utf8_bin,
 `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `password_last_changed` timestamp NULL DEFAULT NULL,
 `password_lifetime` smallint(5) unsigned DEFAULT NULL,
 `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 PRIMARY KEY (`Host`,`User`) -------------Joint primary key, host first-----------
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

3. The tables_priv table only has the following permissions, that is, permissions on the table:

select, insert, update, delete, drop, create, alter, grant, references, index, create view, show view, trigger;

The permissions in the columns_priv table are only the following four: select, insert, update, references

4. There are generally several ways to modify a user's password:

set password for user@host = password('newpassword');
update mysql.user set authentication_string=password('pwd') where user='username' and host='hostname';
alter user user@host identified by 'newpassword';
mysqladmin -u username -h hostname -p password "new password";

The best way is to use the alter user method. In fact, in the new version 8.0, the set password method is no longer available, so it is recommended to use the alter user method to set a new password.

In addition, there is another method, which is not commonly used, which is to use the grant method to overwrite the previous password. Here we simply experiment and see the effect:

mysql:mysql 20:01:05>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' identified by '111111';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql:mysql 20:01:29>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user | host | concat(user,'@','\'',host,'\'') | authentication_string |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz | 192.168.18.% | dba_yeyz@'192.168.18.%' | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+------------------+----------------+-----------------------------------+-------------------------------------------+
11 rows in set (0.00 sec)

mysql:mysql 20:01:31>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql:mysql 20:01:57>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user | host | concat(user,'@','\'',host,'\'') | authentication_string |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz | 192.168.18.% | dba_yeyz@'192.168.18.%' | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+----------------+-----------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

As can be seen from the above test, when we use the grant method to reset the password for a specified user, the previous password will be overwritten, so this operation should be used with caution online. Check whether there is an existing account each time you grant, and then grant after confirming that there is no existing account.

5. If we accidentally forget the root password of MySQL, we can restart the MySQL service and add the parameter --skip-grant-tables to start the MySQL service. This will directly avoid the matching work in the permission table and log in directly to the MySQL service to modify the password of the root account.

6. If you use update or insert to create an account or change the password in the mysql.user table, you must use the flush privileges operation to refresh the privilege table after executing the statement, otherwise the operation will not take effect.

7. There are several permissions that affect the execution of the mysqladmin tool, namely

reload permission: affects flush operation

Shutdown permission: affects shutdown operations

Process permissions: affect processlist operations

super permission: affects the kill operation

8. The resource control fields in the mysql.user table mentioned earlier are

max_questions: the maximum number of requests per hour, max_updates: the maximum number of updates per hour, max_connections: the maximum number of connections per hour, and max_user_connections: the maximum number of connections that a single user can establish simultaneously.

If we want to set this parameter for a user, we can use the following SQL to set it:

mysql:mysql 20:01:58>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' with max_queries_per_hour 1000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql:mysql 20:13:13>>select user,host,max_questions from mysql.user where user='dba_yeyz';  
+----------+--------------+---------------+
| user | host | max_questions |
+----------+--------------+---------------+
| dba_yeyz | 192.168.18.% | 1000 |
+----------+--------------+---------------+
1 row in set (0.00 sec)

Note that the with option is used in the grant statement here. There are five options that can be followed by with, namely:

Grant option: The authorized user can grant these permissions to other users

max_queries_per_hour count: count queries can be executed every hour;

max_updates_per_hour count: count updates can be performed every hour;

max_connections_per_hour count: count connections can be established every hour;

max_user_connections count: Sets the number of connections a single user can establish at the same time

9. Set global variables:

SET GLOBAL default_password_lifetime = 180;

SET GLOBAL default_password_lifetime = 0;

The password lifecycle can be set to 6 months, after which it will become invalid. If it is set to 0, it will remain valid forever.

Of course, you can also specify the password modification cycle or disable the password modification cycle when creating a user:

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

10. Sometimes we seem to have deleted the account password, but we can still access it with the account password. At this time, we need to check a setting to see if there are empty records in the user table:

select user,host from mysql.user where user='';

It is very likely that you have set the user record to be empty, which means that all users can log in directly. If there is, it is best to get rid of it directly because it violates the purpose of security.

The above is the detailed analysis of MySQL permission control details. For more information about MySQL permission control, please pay attention to other related articles on 123WORDPRESS.COM!