Skip to content

Instantly share code, notes, and snippets.

@rubberbird
Forked from lusis/dba-user.json
Created December 30, 2015 18:18
Show Gist options
  • Save rubberbird/e9bed178806cfa459640 to your computer and use it in GitHub Desktop.
Save rubberbird/e9bed178806cfa459640 to your computer and use it in GitHub Desktop.
Managing MySQL user accounts with Chef
{
"id":"dbauser",
"uid":506,
"comment":"DBA User",
"shell":"/bin/bash",
"groups": [
"sysadm",
"dba"
],
"ssh_key": "XXXXXXXXXX"
}
{
"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"]
}
}
}
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment