MySQL Bugs: #16180: Setting SQL_LOG_OFF without SUPER privilege is silently ignored

Bug #16180	Setting SQL_LOG_OFF without SUPER privilege is silently ignored
Submitted:	4 Jan 2006 11:16	Modified:	20 Oct 2006 12:43
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18/4.1 BK	OS:	Windows (Windows/Linux)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
Setting SQL_LOG_OFF requires the SUPER privilege. If a user does not have this, then setting this anyway is silently ignored. This is inconsistent compared to how for example the similar SQL_LOG_BIN behaves. If the latter is set by a user without SUPER privilege, an error is reurned.

How to repeat:
C:\>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create user 'test'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> grant select, insert, update on test.* to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\>mysql -u test test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set sql_log_off = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin = 0;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
mysql> show variables like 'log%';
+---------------------------------+--------------+
| Variable_name                   | Value        |
+---------------------------------+--------------+
| log                             | ON           |
| log_bin                         | ON           |
| log_bin_trust_function_creators | OFF          |
| log_error                       | .\WIGGUM.err |
| log_slave_updates               | OFF          |
| log_slow_queries                | OFF          |
| log_warnings                    | 1            |
+---------------------------------+--------------+
7 rows in set (0.00 sec)

mysql>

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create user 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select, insert, update on test.* to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/mysql -utest test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set sql_log_off = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin = 0;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
mysql> show variables like 'log%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log                             | ON    |
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| log_error                       |       |
| log_slave_updates               | OFF   |
| log_slow_queries                | OFF   |
| log_warnings                    | 1     |
+---------------------------------+-------+
7 rows in set (0.01 sec)

miguel@hegel:~/dbs/4.1> bin/mysql -utest test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.17-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set sql_log_off = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin = 0;
ERROR 1227 (HY000): Access denied; you need the SUPER privilege for this operation
mysql> show variables like 'log%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log               | ON    |
| log_bin           | ON    |
| log_error         |       |
| log_slave_updates | OFF   |
| log_slow_queries  | OFF   |
| log_update        | OFF   |
| log_warnings      | 1     |
+-------------------+-------+
7 rows in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8362

OK to push.

Available in 4.1.21.

Noted in 4.1.21 changelog.

A warning now is issued if the client attempts to set the SQL_LOG_OFF
variable without the SUPER privilege.

Added to 5.0.25 .

Hi,

http://lists.mysql.com/commits/8362
This patch is not applied to 4.1.21.
This bug still happens on 4.1.21.

You are correct, this did not make it into the 4.1.21 version.  My initial comment was premature. This fix will be available in 4.1.22.

I'll move the 4.1.21 note to 4.1.22.