user-tools/mysql-fix-orphan-privileges.php

   1 #!/usr/bin/env php
   2 <?php
   3
   4 /*
   5
   6 =pod
   7
   8 =head1 NAME
   9
  10 mysql-fix-orphan-privileges.php - Suggest SQL commands to clean up unused records in system tables which hold permission data
  11
  12 =cut
  13
  14 */
  15
  16
  17 error_reporting(E_ALL & ~E_DEPRECATED);
  18 ini_set('display_errors', true);
  19
  20 if(!isset($argv[3]))
  21 {
  22     echo "Usage: {$argv[0]} host[:port] user password
  23
  24 This tool outputs sql commands to delete orphan records in privilege-related
  25 tables, which belong to nonexiststent mysql users. This tool does not modify
  26 any privileges, just suggests commands.
  27
  28 Connecting user must have SELECT privilege for `user`, `db`, `tables_priv`
  29 and `columns_priv` tables in mysql database. You need DELETE privilege for
  30 those last three tables in order to apply changes.
  31 ";
  32     exit(1);
  33 }
  34
  35 if(!mysql_connect($argv[1], $argv[2], $argv[3])) die();
  36 mysql_select_db("mysql");
  37
  38
  39 function table($query)
  40 {
  41         $return = array();
  42         $result = mysql_query($query);
  43         if(!$result) return NULL;
  44         while($row = mysql_fetch_assoc($result))
  45         {
  46                 $return[] = $row;
  47         }
  48         mysql_free_result($result);
  49         return $return;
  50 }
  51
  52 foreach(array("db", "tables_priv", "columns_priv") as $table)
  53 {
  54         $grants[$table] = table("SELECT user, host FROM $table");
  55 }
  56
  57 // print_r($grants);
  58
  59 foreach($grants as $table => $users)
  60 {
  61     foreach($users as $user_data)
  62     {
  63         $user = $user_data["user"];
  64         $host = $user_data["host"];
  65         $user_safe = mysql_escape_string($user);
  66         $host_safe = mysql_escape_string($host);
  67
  68         $result = mysql_query("SELECT 1 FROM user WHERE user='$user_safe' AND host='$host_safe' LIMIT 1");
  69         if($result and mysql_num_rows($result) == 0)
  70         {
  71             echo "DELETE FROM $table WHERE user='$user_safe' AND host='$host_safe';\n";
  72         }
  73         mysql_free_result($result);
  74     }
  75 }
  76
  77 echo "FLUSH PRIVILEGES;\n";