Bug #25822 Log Access Denied errors for bad passwords in the error log
Submitted: 24 Jan 2007 12:02 Modified: 12 Nov 2009 19:56
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Logging Severity:S4 (Feature request)
Version:5.1 / 5.2 OS:Any (All)
Assigned to: Mark Leith
Tags: bfsm_2007_02_01, bfsm_2007_08_16, Contribution, error log, errors
Triage: D5 (Feature request)

[24 Jan 2007 12:02] Mark Leith
Description:
For security reasons people request that we log access denied errors within the error log, for people trying to connect with bad passwords. 

This could be done as extra functionality to the log-warnings=2 setting, until we get a more unified "security auditing" log. 

The patch proposed enables this, some sample output based on 5.1bk:

070124 11:45:19 [Note] libexec/mysqld: ready for connections.
Version: '5.1.15-beta-debug'  socket: '/Users/markleith/mysql/mysql-5.1-passwords.sock'  port: 5115  Source distribution
070124 11:45:19 [Note] SCHEDULER: Loaded 0 events
070124 11:45:52 [Warning] Got signal 1 from thread 1
070124 11:47:39 [Warning] Access denied for user 'mark'@'localhost' (using password: NO)
070124 11:47:42 [Warning] Access denied for user 'mark'@'localhost' (using password: NO)
070124 11:49:18 [Warning] Access denied for user 'mark'@'localhost' (using password: NO)
070124 11:49:36 [Warning] Access denied for user 'mark'@'localhost' (using password: YES)

How to repeat:
Nothing to repeat

Suggested fix:
===== sql_parse.cc 1.619 vs edited =====
--- 1.619/sql/sql_parse.cc      2007-01-18 23:02:56 +00:00
+++ edited/sql_parse.cc 2007-01-24 11:53:10 +00:00
@@ -480,6 +480,13 @@
                     thd->main_security_ctx.user,
                     thd->main_security_ctx.host_or_ip,
                     passwd_len ? ER(ER_YES) : ER(ER_NO));
+  if (global_system_variables.log_warnings > 1)
+  {
+    sql_print_warning(ER(ER_ACCESS_DENIED_ERROR),
+                      thd->main_security_ctx.user,
+                      thd->main_security_ctx.host_or_ip,
+                      passwd_len ? ER(ER_YES) : ER(ER_NO));      
+  }
   DBUG_RETURN(-1);
 #endif /* NO_EMBEDDED_ACCESS_CHECKS */
 }
[3 Sep 2007 22:19] 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/33602

ChangeSet@1.2598, 2007-09-03 23:18:50+01:00, markleith@medusa.mysql.com +1 -0
  Bug#25822 / WL#3953 - Log Access Denied errors for bad passwords in the error log
  
  Added logging of Access Denied messages on failed new connections to the 
  error log when log-warnings = 2.
  
  This allows a user to log these messages without having to incur the overhead
  of using the general query log on production systems.
[4 Sep 2007 13:12] Mark Leith
Pushed in to 5.2.6-alpha.
[23 Oct 2007 18:57] Paul Dubois
Noted in 5.2.6 changelog.

If the value of the --log-warnings option is greater than 1, the
server now writes access-denied errors to the error log (for example,
if a client username or password is incorrect).
[29 Oct 2007 17:36] Paul Dubois
5.2.6 changes will appear in 6.0.3 instead.
[21 Oct 2009 14:01] 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/87654

2907 Alexander Nozdrin	2009-10-21
      Backporting WL#3953 from 6.0. Original revision:
      ------------------------------------------------------------
      revno: 2476.714.9
      committer: markleith@medusa.mysql.com
      timestamp: Mon 2007-09-03 23:18:50 +0100
      message:
        Bug#25822 / WL#3953 - Log Access Denied errors for bad passwords in the error log
        
        Added logging of Access Denied messages on failed new connections to the 
        error log when log-warnings = 2.
        
        This allows a user to log these messages without having to incur the overhead
        of using the general query log on production systems.
      ------------------------------------------------------------
[31 Oct 2009 8:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:alik@sun.com-20091022131929-avpr7n06pbnmz9o2) (merge vers: 6.0.14-alpha) (pib:13)
[31 Oct 2009 17:26] Paul Dubois
Already fixed in 6.0.3.

Setting report to NDI pending push to 5.5.x
[12 Nov 2009 8:16] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 19:56] Paul Dubois
Noted in 5.5.0 changelog.