mysql-grant-table-drop

   1 #!/bin/bash
   2
   3 #
   4 # This script finds all tables in all databases and grants DROP permission
   5 # on DBNAME.TBLNAME to corresponding DB owners.
   6 # We have to do this because granting DROP on DBNAME.* also gives permission
   7 # to drop DBNAME itself, which we can't allow.
   8 #
   9 # docelic@hcoop.net, Mon Sep 24 16:25:34 EDT 2007
  10 #
  11
  12 # Exit for now; I don't know how to handle this without messing up
  13 # user's password.
  14
  15 MYSQL_DBS=`sudo -H mysqlshow | tail -n +4 | head -n -1 | cut -d' ' -f 2 | xargs`
  16 #echo "Mysql Databases are: $MYSQL_DBS"
  17
  18 for DB in $MYSQL_DBS; do
  19         if (test "$DB" != "information_schema") && (test "$DB" != "mysql") && (test "$DB" != "performance_schema"); then
  20         USER=${DB%%_*}
  21
  22         MYSQLDB_TABLES=`sudo -H mysqlshow $DB | tail -n +5 | head -n -1 | cut -d' ' -f 2 | xargs`
  23         #echo Tables in $DB are $MYSQLDB_TABLES
  24         for TBL in $MYSQLDB_TABLES; do
  25
  26                 #echo "OWNER FOR $DB.$TBL is $USER"
  27                 sudo -H mysql -e "GRANT ALL PRIVILEGES ON \`$DB\`.\`$TBL\` TO $USER@'%.hcoop.net'";
  28
  29         done
  30
  31         fi
  32 done