Enforce/Revoke db permission restrictions against MySQL when migrating models to another app

db_permissions.rake

namespace :app do
  # List of models that are in the new app, that should not be revoked yet from app-transition
  # The goal should be to keep this VERY SHORT. Users is being added here because its a god
  # model.
  DONT_REVOKE = %w(users)
  ACCOUNT_NAME = 'app-transition'
  SCHEMA_NAMES = ['app_development', 'app_test']

  namespace :db do
    desc "Disregard transitionary permissions on old MySQL Account"
    # This is intended to allow migrations from the old app MySQL account until new app has taken over migrations
    task :disregard => :environment do
      sql = ActiveRecord::Base.connection
      statements = []
      statements += user_statements

      SCHEMA_NAMES.each do |schema_name|
        statements << "GRANT ALL PRIVILEGES ON #{schema_name}.* TO '#{ACCOUNT_NAME}'@'localhost'"
      end
      statements << "FLUSH PRIVILEGES"

      statements.each{|stmt| sql.execute(stmt)}
      puts "Permissions reset for migrations."
    end

    desc "Enforce transitionary permissions on old MySQL Account"
    task :enforce => :environment do |t, args|
      # we should revoke write access on anything moved to new app, unless explicitly NOT revoking.
      Rails.application.eager_load!
      to_revoke = ActiveRecord::Base.descendants.map{|t| t.table_name} - DONT_REVOKE

      sql = ActiveRecord::Base.connection
      statements = []
      statements += user_statements

      SCHEMA_NAMES.each do |schema_name|
        statements << "GRANT SELECT ON #{schema_name}.* TO '#{ACCOUNT_NAME}'@'localhost'"
        # get all of the table names in the schema
        results = sql.execute("select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='#{schema_name}'")
        tables = results.reduce([]){|tables, result| tables += result; tables}

        tables.each do |table|
          statements << "GRANT INSERT, UPDATE, DELETE ON #{schema_name}.#{table} TO '#{ACCOUNT_NAME}'@'localhost'"
        end
        statements << "FLUSH PRIVILEGES"

        to_revoke.each do |table|
          statements << "REVOKE INSERT, UPDATE, DELETE ON #{schema_name}.#{table} FROM '#{ACCOUNT_NAME}'@'localhost';"
        end
        statements << "FLUSH PRIVILEGES"
      end

      puts "Dry running setup..." if ENV['DRY_RUN']
      statements.each do |statement|
        puts "#{statement}"
        sql.execute(statement) unless ENV['DRY_RUN']
      end

      puts "The following write permissions were revoked: #{to_revoke.join(', ')}"
    end

    def user_statements
      [ # 1st statement makes sure DROP USER doesnt fail since MySQL doesnt support IF EXISTS... Yay, MySQL!
        "GRANT USAGE ON *.* TO '#{ACCOUNT_NAME}'@'localhost' IDENTIFIED BY '#{ACCOUNT_NAME}'",
        "DROP USER '#{ACCOUNT_NAME}'@'localhost'",
        "FLUSH PRIVILEGES",
        "CREATE USER '#{ACCOUNT_NAME}'@'localhost' IDENTIFIED BY '#{ACCOUNT_NAME}'",
        "FLUSH PRIVILEGES"
      ]
    end
  end
end