Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Managing MySQL user accounts with Chef
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
@fjfish

This comment has been minimized.

Show comment
Hide comment
@fjfish

fjfish Jun 30, 2013

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

fjfish commented Jun 30, 2013

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

@DenisBY

This comment has been minimized.

Show comment
Hide comment
@DenisBY

DenisBY Oct 23, 2013

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

DenisBY commented Oct 23, 2013

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

@frankflynn

This comment has been minimized.

Show comment
Hide comment
@frankflynn

frankflynn Jan 29, 2014

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).

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).

@slashterix

This comment has been minimized.

Show comment
Hide comment
@slashterix

slashterix Apr 29, 2016

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.

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