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

Have you seen the opscode database recipe that does most of this across three or four platforms? https://github.com/opscode-cookbooks/database

OpsCode's cookbook requires to install ruby and mysql gem. Why I need them on my DB server?

This seems to only offer granularity at the database level, nothing at the table level. So I could not have a users with SELECT on db1.table1 and SELECT, UPDATE on db1.table2.

I do like the way you run DROP USER before (if you did not do that old users would stay around forever).

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.