public
Last active

Managing MySQL user accounts with Chef

  • Download Gist
users-mysql.rb
Ruby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
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|
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
 
execute "grant-perms-#{u[:id]}" do
command "/usr/bin/mysql -u root -p#{mysql_password} -D mysql -r -B -N -e \"GRANT SELECT, FILE 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'"`.to_i == 0 }
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).

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.