Description:
## Description:
When we modify a session variable, the changes should be reflected in the same session. The new privilege SESSION_VARIABLES_ADMIN controls changes of certain variables, including sql_log_off at session level. However, even after a user with the SESSION_VARIABLES_ADMIN privilege changes the session value of sql_log_off, the change is not applicable to the session unless the user also has the higher (SUPER) privileges.
## Actual Issue:
Upon verification, it appears that we are checking for the SUPER or CONNECTION_ADMIN privilege for the current session to turn off logging at the session level when log_off option bit is enabled.
https://github.com/mysql/mysql-server/blob/trunk/sql/log.cc#L1365C1-L1377C2
```
static bool log_command(THD *thd, enum_server_command command) {
if (what_to_log & (1L << (uint)command)) {
Security_context *sctx = thd->security_context();
if ((thd->variables.option_bits & OPTION_LOG_OFF) &&
(sctx->check_access(SUPER_ACL) ||
sctx->has_global_grant(STRING_WITH_LEN("CONNECTION_ADMIN")).first)) {
/* No logging */
return false;
}
return true;
}
return false;
}
```
The parameter require SESSION_VARIABLES_ADMIN or SUPER OR SYSTEM_VARIABLES_ADMIN privilege to change the session value of the same i.e., to run set session sql_log_off=value;
https://github.com/mysql/mysql-server/blob/trunk/sql/sys_vars.cc#L5227
```
static Sys_var_bit Sys_log_off("sql_log_off", "sql_log_off",
SESSION_VAR(option_bits), NO_CMD_LINE,
OPTION_LOG_OFF, DEFAULT(false), NO_MUTEX_GUARD,
NOT_IN_BINLOG, ON_CHECK(check_session_admin));
```
https://github.com/mysql/mysql-server/blob/trunk/sql/sys_vars.cc#L427
```
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/trunk/sql/sys_vars.cc#L368
```
static bool check_session_admin_privileges_only(sys_var *self [[maybe_unused]],
THD *thd, set_var *setv) {
// Privilege check for global variable must have already done before.
assert(self->scope() != sys_var::GLOBAL);
Security_context *sctx = thd->security_context();
if ((setv->type == OPT_SESSION || setv->type == OPT_DEFAULT) &&
!sctx->has_global_grant(STRING_WITH_LEN("SESSION_VARIABLES_ADMIN"))
.first &&
!sctx->has_global_grant(STRING_WITH_LEN("SYSTEM_VARIABLES_ADMIN"))
.first) {
return true;
}
return false;
}
```
This behavior seems conflicting with general session variable changes because it requires one privilege (SESSION_VARIABLES_ADMIN) to set the variable and another higher privilege (SUPER or CONNECTION_ADMIN) to take advantage of it. Usually when a DB user is able to change the session value of this parameter then it should be applicable to that particular section.
How to repeat:
The provided code snippet demonstrates the issue. It creates two users, samp and samp_conn, with different privileges. The samp user has the SESSION_VARIABLES_ADMIN privilege, while the samp_conn user has both SESSION_VARIABLES_ADMIN and CONNECTION_ADMIN privileges.
The reproduction steps show that setting sql_log_off at the session level does not work for the samp user, but it works for the samp_conn user, who has the additional CONNECTION_ADMIN privilege.
Session - 2:
[root@testbox data]# tail -f testbox.log
/root/dbdeployer/8.0.37/bin/mysqld, Version: 8.0.37 (MySQL Community Server - GPL). started with:
Tcp port: 8037 Unix socket: /tmp/mysql_sandbox8037.sock
Time Id Command Argument
2024-06-27T06:55:25.764969Z 9 Query set global log_output=1
2024-06-27T06:57:00.011538Z 9 Query CREATE USER 'samp'@'%' IDENTIFIED BY <secret>
2024-06-27T06:57:04.639386Z 9 Query show grants
2024-06-27T06:57:51.247956Z 9 Query GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, SESSION_VARIABLES_ADMIN ON *.* TO samp
2024-06-27T06:57:55.697551Z 9 Quit
2024-06-27T06:58:03.729319Z 10 Connect root@localhost on using Socket
2024-06-27T06:58:03.729610Z 10 Query select @@version_comment limit 1
2024-06-27T06:58:09.024803Z 10 Query GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, SESSION_VARIABLES_ADMIN ON *.* TO samp
2024-06-27T06:58:43.731865Z 10 Query CREATE USER 'samp_conn'@'%' IDENTIFIED BY <secret>
2024-06-27T06:59:02.899094Z 10 Query GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, SESSION_VARIABLES_ADMIN, CONNECTION_ADMIN ON *.* TO samp_conn
2024-06-27T06:59:35.046374Z 10 Quit
2024-06-27T06:59:42.131701Z 11 Connect samp@localhost on using Socket
2024-06-27T06:59:42.133068Z 11 Query select @@version_comment limit 1
2024-06-27T07:00:01.416999Z 11 Query select @@sql_log_off
2024-06-27T07:00:10.565741Z 11 Query set session sql_log_off=1
2024-06-27T07:00:25.334555Z 11 Query show processlist
2024-06-27T07:00:36.048695Z 11 Query show processlist
2024-06-27T07:00:36.820561Z 11 Query show processlist
2024-06-27T07:00:37.236225Z 11 Query show processlist
2024-06-27T07:00:41.821334Z 11 Quit
2024-06-27T07:00:50.396071Z 12 Connect samp_conn@localhost on using Socket
2024-06-27T07:00:50.397467Z 12 Query select @@version_comment limit 1
2024-06-27T07:01:06.088493Z 12 Query select 'test with conn admin priv user'
2024-06-27T07:01:14.318966Z 12 Query set session sql_log_off=1
^C
[root@testbox data]#
session - 1:
[root@testbox ~]#
[root@testbox ~]# mysql80 -u vidya -p**** -S /tmp/mysql_sandbox8037.sock
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 9
Server version: 8.0.37 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> set global log_output=1;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> set global log_output='FILE';
Query OK, 0 rows affected (0.00 sec)
mysql> create user samp identified by '****';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for vidya@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `vidya`@`localhost` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `vidya`@`localhost` |
| GRANT `R_DO_IT_ALL`@`%` TO `vidya`@`localhost` |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, SESSION_VARIABLES_ADMIN ON *.* TO samp;
ERROR 1045 (28000): Access denied for user 'vidya'@'localhost' (using password: YES)
mysql>
mysql> \q
Bye
[root@testbox ~]# mysql80 -u root -p**** -S /tmp/mysql_sandbox8037.sock
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 10
Server version: 8.0.37 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, SESSION_VARIABLES_ADMIN ON *.* TO samp;
Query OK, 0 rows affected (0.01 sec)
mysql> create user samp_conn identified by '****';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, SESSION_VARIABLES_ADMIN, CONNECTION_ADMIN ON *.* TO samp_conn;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> \q
Bye
[root@testbox ~]#
[root@testbox ~]#
[root@testbox ~]# mysql80 -u samp -p**** -S /tmp/mysql_sandbox8037.sock
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.37 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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>
mysql> select @@sql_log_off;
+---------------+
| @@sql_log_off |
+---------------+
| 0 |
+---------------+
1 row in set (0.01 sec)
mysql> set session sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 345 | Waiting on empty queue | NULL |
| 11 | samp | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)
.
.
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 357 | Waiting on empty queue | NULL |
| 11 | samp | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> \q
Bye
[root@testbox ~]# mysql80 -u samp_conn -p**** -S /tmp/mysql_sandbox8037.sock
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 12
Server version: 8.0.37 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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>
mysql> select 'test with conn admin priv user';
+--------------------------------+
| test with conn admin priv user |
+--------------------------------+
| test with conn admin priv user |
+--------------------------------+
1 row in set (0.00 sec)
mysql> set session sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
.
.
.
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> \q
Bye
[root@testbox ~]#
[root@testbox ~]#
Suggested fix:
The proposed fix is to allow users with the SESSION_VARIABLES_ADMIN or SYSTEM_VARIABLES_ADMIN privilege to turn off logging at the session level (in addition to users with the SUPER privilege) and ensure changes applicable to same session.
diff --git a/sql/log.cc b/sql/log.cc
index 600cf87..71fc26d 100644
--- a/sql/log.cc
+++ b/sql/log.cc
@@ -1361,7 +1361,8 @@ static bool log_command(THD *thd, enum_server_command command) {
Security_context *sctx = thd->security_context();
if ((thd->variables.option_bits & OPTION_LOG_OFF) &&
(sctx->check_access(SUPER_ACL) ||
- sctx->has_global_grant(STRING_WITH_LEN("CONNECTION_ADMIN")).first)) {
+ sctx->has_global_grant(STRING_WITH_LEN("SESSION_VARIABLES_ADMIN")).first ||
+ sctx->has_global_grant(STRING_WITH_LEN("SYSTEM_VARIABLES_ADMIN")).first)) {
/* No logging */
return false;
}
=====
After above fix:
mysql -u vidya -S /tmp/mysql.sock -e "select version(), user(), current_user; show grants; set session sql_log_off=1; select 1; select 'test - not logged'; show processlist;"
+-----------+-----------------+--------------+
| version() | user() | current_user |
+-----------+-----------------+--------------+
| 8.0.37 | vidya@localhost | vidya@% |
+-----------+-----------------+--------------+
+-----------------------------------------------------------------------+
| Grants for vidya@% |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `vidya`@`%` |
| GRANT SESSION_VARIABLES_ADMIN ON *.* TO `vidya`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------+
+---+
| 1 |
+---+
| 1 |
+---+
+-------------------+
| test - not logged |
+-------------------+
| test - not logged |
+-------------------+
+----+-------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+------+---------+------+-------+------------------+
| 10 | vidya | localhost | NULL | Query | 0 | init | show processlist |
+----+-------+-----------+------+---------+------+-------+------------------+
cat testbox.log
/home/mysql8037/bin/mysqld, Version: 8.0.37 (Source distribution). started with:
Tcp port: 3308 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2024-07-04T04:22:43.505645Z 9 Query select @@general_log_file
2024-07-04T04:22:43.505764Z 9 Quit
2024-07-04T04:22:51.209930Z 10 Connect vidya@localhost on using Socket
2024-07-04T04:22:51.210080Z 10 Query select @@version_comment limit 1
2024-07-04T04:22:51.210223Z 10 Query select version(), user(), current_user
2024-07-04T04:22:51.210354Z 10 Query show grants
2024-07-04T04:22:51.210484Z 10 Query set session sql_log_off=1
