MySQL Bugs: #14203: SQL_LOG_OFF should not need priv

Bug #14203	SQL_LOG_OFF should not need priv_super
Submitted:	21 Oct 2005 9:16	Modified:	15 Oct 2010 9:03
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	4.1 and 5.0	OS:	Linux (Debian GNU/Linux)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Currently "SET SQL_LOG_OFF = 1" needs the super privilege.
While this is seems reasonable from the security point of view, I experience that in the
daily use this command is only used for daemons like e.g. our RADIUS server that
fills up a table of currently logged in DSL customers and generates several INSERT and DELETE queries every second, letting the myslq.log grow up to 2GB per day.

(I know that the query log is not performant but I want a log where SELECTs show up, too, and without those bulk queries the log stays reasonable small)

How to repeat:
-

Suggested fix:
For such applications, that even ran mostly on remote servers, granting the super privilege seems not so wise therefore I propose to create a separate privilege called priv_log.
(depending on for which kind of logfiles it is usable).

An alternative would be to be able to exclude individual tables from the query log.

bye,

-christian-

Thank you for the feature request. Other, less powerful privilege for this will be certainly useful.

Is this feature likely to be introduced?

I've recently been working on an app where one requirement was to remove SUPER from the main db user.  The app executes hundreds of queries, a handful of which must not be logged.  The thinking was to use a stored procedure defined by a SUPER user to set sql_log_off=1, and call this from the non-SUPER user.  This works as far as toggling the flag, but of course MySQL does not check this when executing a query from the less privileged user - which I found out the hard way.

An additional privilege level to alter the logging state would be the perfect solution to this situation.

@Andrew: you could make your stored procedure callable by non-root users by definiting it with "CREATE DEFINER='root' PROCEDURE ... SQL SECURITY DEFINER ..."

@Christian: Thanks for the response.

Your suggestion was my initial approach, as mentioned in my previous post - I apologise if this was unclear.  When a user without priv_super calls the stored procedure, the value of sql_log_off is indeed changed.  However, when the same user then executes a query, the sql_log_off flag is ignored and the query appears in the std log.

A glance at the source revealed that MySQL will only inspect the value of sql_log_off if the current user has priv_super, making it impossible for a user without priv_super to disable logging and then execute a query without it appearing in the std log.