Bug #57220 SET SESSION @sql_log_off should not require SUPER privilege
Submitted: 4 Oct 2010 15:00 Modified: 5 Oct 2010 9:20
Reporter: Willem Bogaerts Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S2 (Serious)
Version:5.1.48 OS:Any
Assigned to: CPU Architecture:Any
Tags: privilege, query log

[4 Oct 2010 15:00] Willem Bogaerts
Description:
Setting logging temporarily off should be possible for a non-privileged user. Setting @sql_log_off on a session basis is the way to prevent logging sensitive data. For global logging options, it should remain restricted.

How to repeat:
Example script:

SET @LogSave=@@session.sql_log_off;
SET SESSION sql_log_off=1; -- For security, do not allow these statements to be logged.

-- sensitive statements here (for instance INSERTS into some account table)

SET SESSION sql_log_off=@LogSave; -- Switch the log back on.

Execute the above for a non-privileged user and it will result in an error message:
Error Code: 1227
Access denied; you need the SUPER privilege for this operation

Suggested fix:
split the SET SESSION and the SET GLOBAL restrictions for this setting.
[4 Oct 2010 15:08] Valeriy Kravchuk
Manual is clear about this. Read http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_sql_log_off. 

If session prevented binary logging, then correct point in time recovery is prevented, and this affects not only current session, but entire system. Only DBA (user with SUPER privilege) can make such global decisions.
[5 Oct 2010 7:24] Willem Bogaerts
I do not mean the binary log. I mean that a script should be able to suppress the sensitive query to show up in the general query log. This is not necessarily for the administrator to decide: the developer of the script knows better which statements should not be logged in the query log. We sometimes switch on query logging on live machines and I usually use the root account to run the repeatable database definition script. It usually contains standard accounts for developers which have salted hashes. The salt is different for each server and is included in the script.

For third-party hosting servers, this utterly fails because I do not have SUPER privileges. I know that the query log is owned by the database server system-user and not world-readable on *nix systems, but I think it should be possible in a script to suppress security-sensitive things like passwords to show up at all in the query log. Especially in third-party hosting servers.
[5 Oct 2010 7:38] Valeriy Kravchuk
Sorry, my last explanation had nothing to do with the question...

Anyway, current behavior is intended and documented. If DBA decided that queries must be logged (for audit purposes or for some other reason), then only DBA can decide what is NOT logged. Average user should not have a way to workaround this decision. It is reasonable, IMHO.

Maybe separate privilege for general query log control can help in cases like yours. So, let's consider this a new feature request.
[5 Oct 2010 9:20] Willem Bogaerts
I see your point. A separate privilege could indeed help to make somebody a "lesser DBA", which the issuer of a database definition script is on a shared server. Thanks for your help.
[29 Oct 2012 20:10] MySQL Verification Team
i think the intention was to be able to audit all queries to the server.
if any user can disable general query log for his session, what worth is the audit  if you try to find out how/who your database got dropped by a non-super malicious user...
[19 Dec 2012 11:07] Willem Bogaerts
Command line password hash generator

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysqlpasswordhash.7z (application/octet-stream, text), 7.21 KiB.

[19 Dec 2012 11:17] Willem Bogaerts
I uploaded a password hash generator. It is a command-line utility written in Python, with man page. It can just generate a hash, or some statements that use it (CREATE USER and GRANT statements). Also, it can take an SQL script and replace plaintext passwords with hashed equivalents.
[7 Mar 2013 12:01] Ståle Deraas
Hi Willem, In MySQL 5.6 you will see that we have a builtin function for doing this, so we do not plan to take in your contribution.

Best regards,
Staale