Sample mysql_grant provider for Chef

README

Prototype mysql_grant provider for Chef
---------------------------------------

Drop the two files into your mysql cookbook, and experiment with the following examples:

mysql_grant "kenneth" do
  action :grant
  username "root" # <- User with admin privs
  password node[:mysql][:server_root_password]
  # Actual grant details
  grant_user "kenneth"
  grant_pass "secretdb"
  grant_host "%"
  grants "ALL PRIVILEGES"
end

mysql_grant "foo password" do
  action :changed_password
  username "root"
  password node[:mysql][:server_root_password]
  grant_user "kenneth"
  grant_pass "s3cr3tdb"
  grant_host "%"
end

Caveats
-------

Parsing for matching grants is not without fail, specifying "ALL" as a grant will cause the 
provider to issue a grant statement on every run, since MySQL reports the grant as 
"ALL PRIVILEGES":

mysql> show GRANTS FOR kenneth@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for kenneth@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kenneth'@'localhost' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'kenneth'@'localhost'                                                      |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

DISCLAIMER
----------

This is an experiment in learning and understanding Chef's LWRP, and so far it seems 
to be working for my own use cases. I value feedback, however, you are on your own!

providers_grant.rb

# Drop into mysql cookbook's provider/ directory, rename to grant.rb

include Opscode::Mysql::Database

action :grant do
  unless @grant.grant_exists
    Chef::Log.info "mysql_grant: Adding grants #{new_resource.grants} on #{new_resource.database} for #{new_resource.grant_user}@#{new_resource.grant_host}"
    query = "GRANT #{new_resource.grants} ON `#{new_resource.database}`.* TO `#{new_resource.grant_user}`@`#{new_resource.grant_host}`"
    query.gsub!("`*`", "*") # Clean up possible wildcard issues resulting in `*` for db name
    Chef::Log.debug "mysql_grant: SQL: #{query}"
    db.query query
    new_resource.updated_by_last_action(true)
  else
    Chef::Log.debug "mysql_grant: Grant exists"
  end

  # Set the password
  action_change_password
end

action :revoke do
  if @grant.exists
    Chef::Log.info "mysql_grant: Revoking grants from #{new_resource.database} for #{new_resource.grant_user}"
    db.query "REVOKE ALL ON `#{new_resource.database}`.* FROM `#{new_resource.grant_user}`@`#{new_resource.grant_host}`"
    new_resource.updated_by_last_action(true)
  else
    Chef::Log.debug "mysql_grant: Nothing to revoke"
  end
end

action :change_password do
  if !@grant.pass_match
    Chef::Log.info "mysql_grant: Changing password for #{new_resource.grant_user}@#{new_resource.grant_host}"
    query = "SET PASSWORD FOR `#{new_resource.grant_user}`@`#{new_resource.grant_host}` = PASSWORD('#{new_resource.grant_pass}')"
    Chef::Log.debug "mysql_grant: SQL: #{query}"
    db.query query
    new_resource.updated_by_last_action(true)
  else
    Chef::Log.debug "mysql_grant: Password matched, no changes made"
  end
end

def load_current_resource
  Chef::Log.debug "mysql_grant: Loading new resource"
  @grant = Chef::Resource::MysqlGrant.new(new_resource.name)
  @grant.database(new_resource.database)
  db.select_db('mysql')
  exists = false
  current_password = nil

  # Determine if the grant exists, and extract the current password
  begin
    res = db.query("SHOW GRANTS FOR `#{new_resource.grant_user}`@`#{new_resource.grant_host}`")
    grant_pattern = /^GRANT #{new_resource.grants} ON `?#{new_resource.database.gsub('*', '\*')}/
    pass_pattern = /IDENTIFIED BY PASSWORD '([0-9A-Z\*]+)'/
    res.each do |row|
      if row[0] =~ pass_pattern
        current_password = $1
      end
      if row[0] =~ grant_pattern
        exists = true
        break
      end
    end
    res.free
  rescue Mysql::Error
    # No grant yet
    Chef::Log.info "mysql_grant: user #{new_resource.grant_user}@#{new_resource.grant_host} doesn't exist"
  end
  @grant.grant_exists(exists)

  # Match the passwords
  if current_password
    begin
      res = db.query "SELECT PASSWORD('#{new_resource.grant_pass}')"
      if current_password == res.fetch_row[0]
        @grant.pass_match(true)
      end
    rescue => e
      Chef::Log.error "mysql_grant: Exception while matching password: #{e.message}"
      # Cannot match passwords
    end
  end
end

resources_grant.rb

# Drop into mysql cookbook's resources/ directory, rename to grant.rb

actions :grant, :revoke, :change_password

# MySQL authentication
attribute :host, :kind_of => String, :default => "localhost"
attribute :username, :kind_of => String
attribute :password, :kind_of => String

# User we're giving grants to
attribute :grant_host, :kind_of => String, :default => "localhost"
attribute :grant_user, :kind_of => String
attribute :grant_pass, :kind_of => String
attribute :database, :kind_of => String

# Grants or SQL statement
attribute :grants, :default => String, :default => "USAGE"
attribute :statement, :kind_of => String

# Meta
attribute :grant_exists, :default => false
attribute :pass_match, :default => false