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

[4 Jan 2006 11:16] Anders Karlsson
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>
[4 Jan 2006 11:47] MySQL Verification Team
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)
[28 Jun 2006 0:10] Bugs System
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
[18 Jul 2006 22:32] Timothy Smith
OK to push.
[19 Jul 2006 19:18] Iggy Galarza
Available in 4.1.21.
[20 Jul 2006 17:56] Paul DuBois
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.
[10 Aug 2006 14:55] Chad MILLER
Added to 5.0.25 .
[16 Oct 2006 2:50] Yoshinori Matsunobu
Hi,

http://lists.mysql.com/commits/8362
This patch is not applied to 4.1.21.
This bug still happens on 4.1.21.
[16 Oct 2006 16:44] Iggy Galarza
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.
[20 Oct 2006 12:43] Paul DuBois
I'll move the 4.1.21 note to 4.1.22.