Description:
In the documentation, it is mentioned that users need super or system_variables_admin privilege to modify connection_memory_limit and connection_memory_chunk_size. This is true only for global value of the variables. Could you please update the documentation and explain privileges required for global and session values accordingly which helps to create DB users with least possible privileges.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_connection_mem...
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_connection_mem...
From the code we can see that ON_CHECK(check_session_admin) used for verification of privileges for these variables.
https://github.com/mysql/mysql-server/blob/8.0/sql/sys_vars.cc#L385
static bool check_session_admin(sys_var *self, THD *thd, set_var *setv) {
Security_context *sctx = thd->security_context();
if (check_session_admin_privileges_only(self, thd, setv) &&
!sctx->check_access(SUPER_ACL)) {
my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0),
"SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN");
return true;
}
return false;
}
https://github.com/mysql/mysql-server/blob/8.0/sql/sys_vars.cc#L3505
BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_session_admin)
How to repeat:
From root user
mysql> create user test identified by '********';
Query OK, 0 rows affected (0.01 sec)
From test user
[root@testbox msb_8_0_36]# /root/dbdeployer/8.0.36/bin/mysql -u test -S /tmp/mysql_sandbox8036.sock -p********
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'connection%';
+------------------------------+----------------------+
| Variable_name | Value |
+------------------------------+----------------------+
| connection_memory_chunk_size | 8192 |
| connection_memory_limit | 18446744073709551615 |
+------------------------------+----------------------+
2 rows in set (0.01 sec)
mysql> set connection_memory_limit=1*1024*1024;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
mysql>
mysql> set connection_memory_chunk_size=1*1024;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
mysql>
mysql> \q
Bye
[root@testbox msb_8_0_36]#
From root user
mysql> grant SESSION_VARIABLES_ADMIN on *.* to test;
Query OK, 0 rows affected (0.00 sec)
From test user
[root@testbox msb_8_0_36]# /root/dbdeployer/8.0.36/bin/mysql -u test -S /tmp/mysql_sandbox8036.sock -p********
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants;
+----------------------------------------------------+
| Grants for test@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT SESSION_VARIABLES_ADMIN ON *.* TO `test`@`%` |
+----------------------------------------------------+
2 rows in set (0.01 sec)
mysql> show variables like 'connection%';
+------------------------------+----------------------+
| Variable_name | Value |
+------------------------------+----------------------+
| connection_memory_chunk_size | 8192 |
| connection_memory_limit | 18446744073709551615 |
+------------------------------+----------------------+
2 rows in set (0.00 sec)
mysql> set connection_memory_chunk_size=16*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> set connection_memory_chunk_size=1*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> set connection_memory_limit=1*1024*1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect connection_memory_limit value: '1048576' |
+---------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set connection_memory_limit=10485760;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'connection%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| connection_memory_chunk_size | 1024 |
| connection_memory_limit | 10485760 |
+------------------------------+----------+
2 rows in set (0.00 sec)
mysql> \q
Bye
[root@testbox msb_8_0_36]#
Suggested fix:
Describing the permissions clearly in documentation helpful to customer while creating and granting users. In earlier versions, there is no confusion between session and global changes. However in 8.0 due to implementation SESSION_VARIABLES_ADMIN and other permission, providing clear description about permission for every variable (wherever applicable) will definitely help!
Example: we can update the description of the variables like below:
"To change global value of this variable - You must have the SYSTEM_VARIABLES_ADMIN or SUPER privilege to set this variable.
To change session value of this variable - You must have the SYSTEM_VARIABLES_ADMIN or SUPER or SESSION_VARIABLES_ADMIN privilege to set this variable."
