diff options
Diffstat (limited to 'lib/modules/mysql_user')
| -rw-r--r-- | lib/modules/mysql_user | 491 |
1 files changed, 491 insertions, 0 deletions
diff --git a/lib/modules/mysql_user b/lib/modules/mysql_user new file mode 100644 index 0000000..64e1f3d --- /dev/null +++ b/lib/modules/mysql_user @@ -0,0 +1,491 @@ +#!/usr/bin/python + +# (c) 2012, Mark Theunissen <mark.theunissen@gmail.com> +# Sponsored by Four Kitchens http://fourkitchens.com. +# +# This file is part of Ansible +# +# Ansible is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# Ansible is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Ansible. If not, see <http://www.gnu.org/licenses/>. + +DOCUMENTATION = ''' +--- +module: mysql_user +short_description: Adds or removes a user from a MySQL database. +description: + - Adds or removes a user from a MySQL database. +version_added: "0.6" +options: + name: + description: + - name of the user (role) to add or remove + required: true + default: null + password: + description: + - set the user's password + required: false + default: null + host: + description: + - the 'host' part of the MySQL username + required: false + default: localhost + login_user: + description: + - The username used to authenticate with + required: false + default: null + login_password: + description: + - The password used to authenticate with + required: false + default: null + login_host: + description: + - Host running the database + required: false + default: localhost + login_port: + description: + - Port of the MySQL server + required: false + default: 3306 + version_added: '1.4' + login_unix_socket: + description: + - The path to a Unix domain socket for local connections + required: false + default: null + priv: + description: + - "MySQL privileges string in the format: C(db.table:priv1,priv2)" + required: false + default: null + append_privs: + description: + - Append the privileges defined by priv to the existing ones for this + user instead of overwriting existing ones. + required: false + choices: [ "yes", "no" ] + default: "no" + version_added: "1.4" + state: + description: + - Whether the user should exist. When C(absent), removes + the user. + required: false + default: present + choices: [ "present", "absent" ] + check_implicit_admin: + description: + - Check if mysql allows login as root/nopassword before trying supplied credentials. + required: false + default: false + version_added: "1.3" +notes: + - Requires the MySQLdb Python package on the remote host. For Ubuntu, this + is as easy as apt-get install python-mysqldb. + - Both C(login_password) and C(login_username) are required when you are + passing credentials. If none are present, the module will attempt to read + the credentials from C(~/.my.cnf), and finally fall back to using the MySQL + default login of 'root' with no password. + - "MySQL server installs with default login_user of 'root' and no password. To secure this user + as part of an idempotent playbook, you must create at least two tasks: the first must change the root user's password, + without providing any login_user/login_password details. The second must drop a ~/.my.cnf file containing + the new root credentials. Subsequent runs of the playbook will then succeed by reading the new credentials from + the file." + +requirements: [ "ConfigParser", "MySQLdb" ] +author: Mark Theunissen +''' + +EXAMPLES = """ +# Create database user with name 'bob' and password '12345' with all database privileges +- mysql_user: name=bob password=12345 priv=*.*:ALL state=present + +# Ensure no user named 'sally' exists, also passing in the auth credentials. +- mysql_user: login_user=root login_password=123456 name=sally state=absent + +# Example privileges string format +mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanotherdb.*:ALL + +# Example using login_unix_socket to connect to server +- mysql_user: name=root password=abc123 login_unix_socket=/var/run/mysqld/mysqld.sock + +# Example .my.cnf file for setting the root password +# Note: don't use quotes around the password, because the mysql_user module +# will include them in the password but the mysql client will not + +[client] +user=root +password=n<_665{vS43y +""" + +import ConfigParser +import getpass +import tempfile +try: + import MySQLdb +except ImportError: + mysqldb_found = False +else: + mysqldb_found = True + +# =========================================== +# MySQL module specific support methods. +# + +def user_exists(cursor, user, host): + cursor.execute("SELECT count(*) FROM user WHERE user = %s AND host = %s", (user,host)) + count = cursor.fetchone() + return count[0] > 0 + +def load_plugin(cursor, plugin): + cursor.execute("SELECT count(*) FROM plugin WHERE name = %s", plugin) + count = cursor.fetchone() + if count[0] == 0: + so = "%s.so" % plugin + cursor.execute("INSTALL PLUGIN %s SONAME %s", (plugin, so)) + +def user_add(cursor, user, host, password, new_priv, auth_plugin): + if password is None: + # Automatically loaded on first first use. + load_plugin(cursor, auth_plugin) + cursor.execute("CREATE USER %s@%s IDENTIFIED WITH %s", (user,host,auth_plugin)) + else: + cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user,host,password)) + if new_priv is not None: + for db_table, priv in new_priv.iteritems(): + privileges_grant(cursor, user,host,db_table,priv) + return True + +def user_mod(cursor, user, host, password, new_priv, append_privs, auth_plugin): + changed = False + grant_option = False + + # Handle plugin. + if auth_plugin is not None: + cursor.execute("SELECT plugin FROM user WHERE user = %s AND host = %s", (user,host)) + if cursor.fetchone()[0] != auth_plugin: + # Sadly there is no proper way to updade the authentication plugin: + # http://bugs.mysql.com/bug.php?id=67449 + cursor.execute( "UPDATE user SET plugin = %s, password = '' WHERE user = %s AND host = %s" + , (auth_plugin,user,host)) + cursor.execute("FLUSH PRIVILEGES") + changed = True + + # Handle passwords. + if password is not None: + cursor.execute("SELECT password FROM user WHERE user = %s AND host = %s", (user,host)) + current_pass_hash = cursor.fetchone() + cursor.execute("SELECT PASSWORD(%s)", (password,)) + new_pass_hash = cursor.fetchone() + if current_pass_hash[0] != new_pass_hash[0]: + cursor.execute("SET PASSWORD FOR %s@%s = PASSWORD(%s)", (user,host,password)) + changed = True + + # Handle privileges. + if new_priv is not None: + curr_priv = privileges_get(cursor, user,host) + + # If the user has privileges on a db.table that doesn't appear at all in + # the new specification, then revoke all privileges on it. + for db_table, priv in curr_priv.iteritems(): + # If the user has the GRANT OPTION on a db.table, revoke it first. + if "GRANT" in priv: + grant_option = True + if db_table not in new_priv: + if user != "root" and "PROXY" not in priv and not append_privs: + privileges_revoke(cursor, user,host,db_table,grant_option) + changed = True + + # If the user doesn't currently have any privileges on a db.table, then + # we can perform a straight grant operation. + for db_table, priv in new_priv.iteritems(): + if db_table not in curr_priv: + privileges_grant(cursor, user,host,db_table,priv) + changed = True + + # If the db.table specification exists in both the user's current privileges + # and in the new privileges, then we need to see if there's a difference. + db_table_intersect = set(new_priv.keys()) & set(curr_priv.keys()) + for db_table in db_table_intersect: + priv_diff = set(new_priv[db_table]) ^ set(curr_priv[db_table]) + if (len(priv_diff) > 0): + privileges_revoke(cursor, user,host,db_table,grant_option) + privileges_grant(cursor, user,host,db_table,new_priv[db_table]) + changed = True + + return changed + +def user_delete(cursor, user, host): + cursor.execute("DROP USER %s@%s", (user,host)) + return True + +def privileges_get(cursor, user,host): + """ MySQL doesn't have a better method of getting privileges aside from the + SHOW GRANTS query syntax, which requires us to then parse the returned string. + Here's an example of the string that is returned from MySQL: + + GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY 'pass'; + + This function makes the query and returns a dictionary containing the results. + The dictionary format is the same as that returned by privileges_unpack() below. + """ + output = {} + cursor.execute("SHOW GRANTS FOR %s@%s", (user,host)) + grants = cursor.fetchall() + + def pick(x): + if x == 'ALL PRIVILEGES': + return 'ALL' + else: + return x + + for grant in grants: + res = re.match("GRANT (.+) ON (.+) TO '.+'@'.+'( IDENTIFIED BY PASSWORD '.+')? ?(.*)", grant[0]) + if res is None: + module.fail_json(msg="unable to parse the MySQL grant string") + privileges = res.group(1).split(", ") + privileges = [ pick(x) for x in privileges] + if "WITH GRANT OPTION" in res.group(4): + privileges.append('GRANT') + db = res.group(2) + output[db] = privileges + return output + +def privileges_unpack(priv): + """ Take a privileges string, typically passed as a parameter, and unserialize + it into a dictionary, the same format as privileges_get() above. We have this + custom format to avoid using YAML/JSON strings inside YAML playbooks. Example + of a privileges string: + + mydb.*:INSERT,UPDATE/anotherdb.*:SELECT/yetanother.*:ALL + + The privilege USAGE stands for no privileges, so we add that in on *.* if it's + not specified in the string, as MySQL will always provide this by default. + """ + output = {} + for item in priv.split('/'): + pieces = item.split(':') + if pieces[0].find('.') != -1: + pieces[0] = pieces[0].split('.') + for idx, piece in enumerate(pieces): + if pieces[0][idx] != "*": + pieces[0][idx] = "`" + pieces[0][idx] + "`" + pieces[0] = '.'.join(pieces[0]) + + output[pieces[0]] = [ g.strip() for g in pieces[1].upper().split(',') ] + + if '*.*' not in output: + output['*.*'] = ['USAGE'] + + return output + +def privileges_revoke(cursor, user,host,db_table,grant_option): + if grant_option: + query = "REVOKE GRANT OPTION ON %s FROM '%s'@'%s'" % (db_table,user,host) + cursor.execute(query) + query = "REVOKE ALL PRIVILEGES ON %s FROM '%s'@'%s'" % (db_table,user,host) + cursor.execute(query) + +def privileges_grant(cursor, user,host,db_table,priv): + + priv_string = ",".join(filter(lambda x: x != 'GRANT', priv)) + query = "GRANT %s ON %s TO '%s'@'%s'" % (priv_string,db_table,user,host) + if 'GRANT' in priv: + query = query + " WITH GRANT OPTION" + cursor.execute(query) + + +def strip_quotes(s): + """ Remove surrounding single or double quotes + + >>> print strip_quotes('hello') + hello + >>> print strip_quotes('"hello"') + hello + >>> print strip_quotes("'hello'") + hello + >>> print strip_quotes("'hello") + 'hello + + """ + single_quote = "'" + double_quote = '"' + + if s.startswith(single_quote) and s.endswith(single_quote): + s = s.strip(single_quote) + elif s.startswith(double_quote) and s.endswith(double_quote): + s = s.strip(double_quote) + return s + + +def config_get(config, section, option): + """ Calls ConfigParser.get and strips quotes + + See: http://dev.mysql.com/doc/refman/5.0/en/option-files.html + """ + return strip_quotes(config.get(section, option)) + + +def _safe_cnf_load(config, path): + + data = {'user':'', 'password':''} + + # read in user/pass + f = open(path, 'r') + for line in f.readlines(): + line = line.strip() + if line.startswith('user='): + data['user'] = line.split('=', 1)[1].strip() + if line.startswith('password=') or line.startswith('pass='): + data['password'] = line.split('=', 1)[1].strip() + f.close() + + # write out a new cnf file with only user/pass + fh, newpath = tempfile.mkstemp(prefix=path + '.') + f = open(newpath, 'wb') + f.write('[client]\n') + f.write('user=%s\n' % data['user']) + f.write('password=%s\n' % data['password']) + f.close() + + config.readfp(open(newpath)) + os.remove(newpath) + return config + +def load_mycnf(): + config = ConfigParser.RawConfigParser() + mycnf = os.path.expanduser('~/.my.cnf') + if not os.path.exists(mycnf): + return False + try: + config.readfp(open(mycnf)) + except (IOError): + return False + except: + config = _safe_cnf_load(config, mycnf) + + # We support two forms of passwords in .my.cnf, both pass= and password=, + # as these are both supported by MySQL. + try: + passwd = config_get(config, 'client', 'password') + except (ConfigParser.NoOptionError): + try: + passwd = config_get(config, 'client', 'pass') + except (ConfigParser.NoOptionError): + return False + + # If .my.cnf doesn't specify a user, default to user login name + try: + user = config_get(config, 'client', 'user') + except (ConfigParser.NoOptionError): + user = getpass.getuser() + creds = dict(user=user,passwd=passwd) + return creds + +def connect(module, login_user, login_password): + if module.params["login_unix_socket"]: + db_connection = MySQLdb.connect(host=module.params["login_host"], unix_socket=module.params["login_unix_socket"], user=login_user, passwd=login_password, db="mysql") + else: + db_connection = MySQLdb.connect(host=module.params["login_host"], port=int(module.params["login_port"]), user=login_user, passwd=login_password, db="mysql") + return db_connection.cursor() + +# =========================================== +# Module execution. +# + +def main(): + module = AnsibleModule( + argument_spec = dict( + login_user=dict(default=None), + login_password=dict(default=None), + login_host=dict(default="localhost"), + login_port=dict(default="3306"), + login_unix_socket=dict(default=None), + user=dict(required=True, aliases=['name']), + password=dict(default=None), + host=dict(default="localhost"), + state=dict(default="present", choices=["absent", "present"]), + priv=dict(default=None), + append_privs=dict(type="bool", default="no"), + check_implicit_admin=dict(default=False), + auth_plugin=dict(default=None) + ) + ) + user = module.params["user"] + password = module.params["password"] + host = module.params["host"] + state = module.params["state"] + priv = module.params["priv"] + check_implicit_admin = module.params['check_implicit_admin'] + append_privs = module.boolean(module.params["append_privs"]) + auth_plugin = module.params['auth_plugin'] + + if not mysqldb_found: + module.fail_json(msg="the python mysqldb module is required") + + if priv is not None: + try: + priv = privileges_unpack(priv) + except: + module.fail_json(msg="invalid privileges string") + + # Either the caller passes both a username and password with which to connect to + # mysql, or they pass neither and allow this module to read the credentials from + # ~/.my.cnf. + login_password = module.params["login_password"] + login_user = module.params["login_user"] + if login_user is None and login_password is None: + mycnf_creds = load_mycnf() + if mycnf_creds is False: + login_user = "root" + login_password = "" + else: + login_user = mycnf_creds["user"] + login_password = mycnf_creds["passwd"] + elif login_password is None or login_user is None: + module.fail_json(msg="when supplying login arguments, both login_user and login_password must be provided") + + cursor = None + try: + if check_implicit_admin: + try: + cursor = connect(module, 'root', '') + except: + pass + + if not cursor: + cursor = connect(module, login_user, login_password) + except Exception, e: + module.fail_json(msg="unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials") + + if state == "present": + if user_exists(cursor, user, host): + changed = user_mod(cursor, user, host, password, priv, append_privs, auth_plugin) + else: + if (password is None and auth_plugin is None) or (password is not None and auth_plugin is not None): + module.fail_json(msg="password xor auth_plugin is required when adding a user") + changed = user_add(cursor, user, host, password, priv, auth_plugin) + elif state == "absent": + if user_exists(cursor, user, host): + changed = user_delete(cursor, user, host) + else: + changed = False + module.exit_json(changed=changed, user=user) + +# this is magic, see lib/ansible/module_common.py +#<<INCLUDE_ANSIBLE_MODULE_COMMON>> +main() |