Managing MySQL user accounts with Chef

dba-user.json

	{
		"id":"dbauser",
		"uid":506,
		"comment":"DBA User",
		"shell":"/bin/bash",
		"groups": [
			"sysadm",
			"dba"
		],
		"ssh_key": "XXXXXXXXXX"
	}

limited-user.json

	{
		"id":"buser",
		"uid":502,
		"comment":"Bob User",
		"shell":"/bin/bash",
		"groups": [
			"devel"
		],
		"ssh_key": "XXXXXXXXXXXXXXXXXXX",
		"mysql_permissions":{
			"db3":{
				"mydb1":["SELECT","INSERT","UPDATE"]
			},
			"db2":{
				"mydb1":["ALL"],
				"mydb2":["SELECT","INSERT","UPDATE"],
				"mydb3":["SELECT","INSERT","UPDATE"]
			}
		}
	}

users-mysql.rb

include_recipe "databag_decrypt::default"
password = search(:passwords, "id:mysql_admin_password").first
mysql_password = item_decrypt(password[:data])

users = search(:users)
deleted_users = search(:deleted_users)

deleted_users.each do |deleted_user|
  users.delete_if {|user| user['id'] == deleted_user['id']}
end

if node[:mysql][:manage_users] == true
  users.each do |u|
    # Add database user accounts
    execute "add-mysql-user-#{u[:id]}" do
      command "/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"CREATE USER '#{u[:id]}'@'localhost'\""
      action :run
      only_if { `/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"SELECT COUNT(*) FROM user where User='#{u[:id]}' and Host = 'localhost'"`.to_i == 0 }
    end
    # Grant dba perms
    if u[:groups].member?('dba') then
      execute "grant-dba-perms-#{u[:id]}" do
        command "/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"GRANT ALL on *.* to '#{u[:id]}'@'localhost'\""
        action :run
        not_if { `/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"SELECT COUNT(*) FROM user where User='#{u[:id]}' and Host = 'localhost' and Super_priv='Y'"`.to_i == 1 }
      end
    end 
    # Grant generic user perms
    execute "grant-perms-#{u[:id]}" do
      command "/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"GRANT SELECT, FILE, CREATE TEMPORARY TABLES on *.* to '#{u[:id]}'@'localhost'\""
      action :run
      not_if { `/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"SELECT COUNT(*) FROM user where User='#{u[:id]}' and Host = 'localhost' and Select_priv='Y' and File_priv='Y' and Create_tmp_table_priv='Y'"`.to_i == 1 }
    end
    # Grant user-specific perms
    if u.has_key?("mysql_permissions") and u[:mysql_permissions].has_key?(node.name) then
      u[:mysql_permissions]["#{node.name}"].each do |database, grants| 
        Chef::Log.info("Found permission on #{node.name} for #{u[:id]}: DB: #{database}, Grants: #{grants.join(',')}")
        execute "grant-#{u[:id]}-#{database}-perms" do
          command "/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"GRANT #{grants.join(',')} ON #{database}.* to '#{u[:id]}'@'localhost'\""
          action :run
          not_if { `/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"SELECT COUNT(*) FROM user where User='#{u[:id]}' and Host = 'localhost'"`.to_i == 0 }
        end
      end 
    end
  end

  deleted_users.each do |du|
    # Delete deleted users
    execute "delete-user-#{du[:id]}" do
      command "/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"DROP USER '#{du[:id]}'@'localhost'\""
      action :run
      only_if { `/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"SELECT COUNT(*) FROM user where User='#{du[:id]}' and Host = 'localhost'"`.to_i == 1 }
    end
  end
end

I've found it's important to also flush the privileges when they change

# flush mysql privs if any are changed
execute "mysql-flush-privs" do
  command /usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"FLUSH PRIVILEGES;\""
  action :nothing
end

Then add to the grant execute blocks

notifies :run, "execute[mysql-flush-privs]", :delayed

So that a flush is run at the end of the chef run if any user privs were changed.