Created
January 7, 2011 11:24
-
-
Save lusis/769365 to your computer and use it in GitHub Desktop.
Managing MySQL user accounts with Chef
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
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.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Have you seen the opscode database recipe that does most of this across three or four platforms? https://github.com/opscode-cookbooks/database