MySQL Bugs: #73656: "SET SESSION" needs SUPER privilege

Bug #73656	"SET SESSION" needs SUPER privilege
Submitted:	20 Aug 2014 13:23	Modified:	11 Sep 2014 15:52
Reporter:	Yoshiaki Yamasaki	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6, 5.6.21	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
"SET SESSION" needs SUPER privilege. This behavior does not match description of manual.

 13.7.4 SET Syntax
 http://dev.mysql.com/doc/refman/5.6/en/set-statement.html
  => "Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client."

How to repeat:
mysql> create user 'test'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\work_dir\sql>mysql -u test -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 SESSION sql_log_bin=1;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> exit
Bye

C:\work_dir\sql>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 SUPER on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\work_dir\sql>mysql -u test -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 SESSION sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

Suggested fix:
As described manual, "Setting a session variable requires no special privilege."

 13.7.4 SET Syntax
 http://dev.mysql.com/doc/refman/5.6/en/set-statement.html
  => "Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client."

I think "privilege separation" is important between "SET SESSION" and "SET GLOBAL". Therefore, this is not a document bug.

Hi Yoshiaki,

Thank you for your bug report.  This particular session variable is somewhat unique.  It controls whether statements executed are written to the binary log, which has further implications for slave consistency.  As such, it is restricted in ways that other session variables are not, as described in the documentation for this variable:

"This variable controls whether logging to the binary log is done. The default value is 1 (do logging). To change logging for the current session, change the session value of this variable. The session user must have the SUPER privilege to set this variable. "

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_log_bin

Please note also that there has been recent discussion about the appropriateness of providing a global-scoped sql_log_bin variable.  Much of that discussion focuses on the same problematic resulting behavior which drives the need to restrict access.  You can read more about this particular variable and the impact here:

http://www.tocker.ca/2014/08/14/set-global-sql_log_bin-we-need-your-feedback.html

I've changed this bug to be a documentation bug - I think it would be helpful to note that the ability to set session variables is *generally* not restricted, but that exceptions exist, and documentation for individual variables should be consulted.

Thank you for the reply. I understand.

I voted the following discussion. Of course, I want to make an error.
http://www.tocker.ca/2014/08/14/set-global-sql_log_bin-we-need-your-feedback.html

I'm adopting this one.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.