def check_rpl_user(self, user, host):
"""Check replication user exists and has the correct privileges.
user[in] user name of rpl_user
host[in] host name of rpl_user
Returns [] - no exceptions, list if exceptions found
"""
from mysql.utilities.common.user import User
errors = []
if host == '127.0.0.1':
host = 'localhost'
result = self.user_host_exists(user, host)
if result is None or result == []:
errors.append("The replication user %s@%s was not found "
"on %s:%s." % (user, host, self.host, self.port))
else:
rpl_user = User(self, "%s@" % user + result)
if not rpl_user.has_privilege('*', '*',
'REPLICATION SLAVE'):
errors.append("Replication user does not have the "
"correct privilege. She needs "
"'REPLICATION SLAVE' on all replicated "
"databases.")
return errors
def _check_privileges(server, options):
"""Check required privileges to move binary logs from server.
This method check if the used user possess the required privileges to
relocate binary logs from the server. More specifically, the following
privilege is required: RELOAD (to flush the binary logs).
An exception is thrown if the user doesn't have enough privileges.
server[in] Server instance to check.
options[in] Dictionary of options (skip_flush_binlogs, verbosity).
"""
skip_flush = options['skip_flush_binlogs']
if not skip_flush:
# Only need to check privileges if flush is not skipped.
verbosity = options['verbosity']
if verbosity > 0:
print("# Checking user permission to move binary logs...\n" "#")
# Check privileges
user_obj = User(server, "{0}@{1}".format(server.user, server.host))
if not user_obj.has_privilege('*', '*', 'RELOAD'):
raise UtilError(
ERROR_USER_WITHOUT_PRIVILEGES.format(
user=server.user,
host=server.host,
port=server.port,
operation='perform binary log move',
req_privileges='RELOAD'))
def check_rpl_user(self, user, host):
"""Check replication user exists and has the correct privileges.
user[in] user name of rpl_user
host[in] host name of rpl_user
Returns [] - no exceptions, list if exceptions found
"""
from mysql.utilities.common.user import User
errors = []
result = self.exec_query("SELECT * FROM mysql.user WHERE user = '******' "
"AND host = '%s'" % (user, host))
if result is None or result == []:
errors.append("The replication user %s@%s was not found "
"on the master." % (user, host))
else:
rpl_user = User(self, "%s@%s" % (user, host))
if not rpl_user.has_privilege('*', '*',
'REPLICATION SLAVE'):
errors.append("Replication user does not have the "
"correct privilege. She needs "
"'REPLICATION SLAVE' on all replicated "
"databases.")
return errors
def drop_user(self, user_name, server):
user = User(server, user_name)
if user.exists():
res = user.drop()
if res is not None:
print "cleanup: failed to drop user %s" % user_name
return True
def drop_user(self, user_name, server):
user = User(server, user_name)
if user.exists():
res = user.drop()
if res:
print "cleanup: failed to drop user %s" % user_name
return True
def _show_user_grants(source, user_source, base_user, verbosity):
"""Show grants for a specific user.
"""
try:
if not user_source:
user_source = User(source, base_user, verbosity)
print "# Dumping grants for user " + base_user
user_source.print_grants()
except UtilError:
print "# Cannot show grants for user %s." % base_user + "Please check user and host for valid names."
def _show_user_grants(source, user_source, base_user, verbosity):
"""Show grants for a specific user.
"""
try:
if not user_source:
user_source = User(source, base_user, verbosity)
print("# Dumping grants for user " + base_user)
user_source.print_grants()
except UtilError:
print("# Cannot show grants for user %s." % base_user + \
"Please check user and host for valid names.")
def drop_user(user_name, server):
"""Drops user.
user_name[in] User name.
server[in] Server instance.
"""
user = User(server, user_name)
if user.exists():
res = user.drop()
if not res:
print("cleanup: failed to drop user {0}".format(user_name))
return True
def drop_user(user_name, server):
"""Drops user.
user_name[in] User.
server[in] Server instance.
"""
user = User(server, user_name)
if user.exists():
res = user.drop()
if not res:
print("cleanup: failed to drop user {0}".format(user_name))
return True
def _show_user_grants(source, user_source, base_user, verbosity):
"""Show grants for a specific user.
"""
from mysql.utilities.common.user import User
try:
if not user_source:
user_source = User(source, base_user, verbosity)
print "# Dumping grants for user " + base_user
user_source.print_grants()
except UtilError, e:
print "# Cannot show grants for user %s." % base_user + \
"Please check user and host for valid names."
def _check_user_permissions(self, uname, host, access):
"""Check user permissions for a given privilege
uname[in] user name to check
host[in] host name of connection
acess[in] privilege to check (e.g. "SELECT")
Returns True if user has permission, False if not
"""
from mysql.utilities.common.user import User
user = User(self.source, uname+'@'+host)
result = user.has_privilege(access[0], '*', access[1])
return result
def _check_user_permissions(self, uname, host, access):
"""Check user permissions for a given privilege
uname[in] user name to check
host[in] host name of connection
acess[in] privilege to check (e.g. "SELECT")
Returns True if user has permission, False if not
"""
from mysql.utilities.common.user import User
user = User(self.source, uname + "@" + host)
result = user.has_privilege(access[0], "*", access[1])
return result
def _check_permissions(server, priv):
"""Check to see if user has permissions to execute.
server[in] Server class instance
priv[in] privilege to check
Returns True if permissions available, raises exception if not
"""
# Check user permissions
user_pass_host = server.user
if server.passwd is not None and len(server.passwd) > 0:
user_pass_host += ":" + server.passwd
user_pass_host += "@" + server.host
user = User(server, user_pass_host, False)
if not user.has_privilege("*", "*", priv):
raise UtilError("Not enough permissions. The user must have the "
"%s privilege." % priv)
def _check_permissions(server, priv):
"""Check to see if user has permissions to execute.
server[in] Server class instance
priv[in] privilege to check
Returns True if permissions available, raises exception if not
"""
# Check user permissions
user_pass_host = server.user
if server.passwd is not None and len(server.passwd) > 0:
user_pass_host += ":" + server.passwd
user_pass_host += "@" + server.host
user = User(server, user_pass_host, False)
if not user.has_privilege("*", "*", priv):
raise UtilError("Not enough permissions. The user must have the "
"%s privilege." % priv)
def _check_privileges(server):
"""Verify required privileges to check grantee privileges.
server[in] Instance of Server class.
This method checks if the used User for the server possesses
the required privileges get the list of grantees and respective grants
for the objects.
Specifically, the following privilege is required: SELECT on mysql.*
An exception is thrown if the user doesn't have this privilege.
"""
user_obj = User(server, "{0}@{1}".format(server.user, server.host))
has_privilege = user_obj.has_privilege('mysql', '*', 'SELECT')
if not has_privilege:
raise UtilError(ERROR_USER_WITHOUT_PRIVILEGES.format(
user=server.user, host=server.host,
port=server.port,
operation='read the available grants',
req_privileges="SELECT on mysql.*"
))
def clone_user(src_val, dest_val, base_user, new_user_list, options):
"""Clone a user to one or more new user accounts
This method will create one or more new user accounts copying the
grant statements from a given user. If source and destination are the
same, the copy will occur on a single server otherwise, the caller may
specify a destination server to where the user accounts will be copied.
NOTES:
The user is responsible for making sure the databases and objects
referenced in the cloned GRANT statements exist prior to running this
utility.
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
dest_val[in] a dictionary containing connection information for the
destination including:
(user, password, host, port, socket)
base_user[in] the user account on the source machine to be used as
the template for the new users
user_list[in] a list of new user accounts in the form:
(username:password@host)
options[in] optional parameters dictionary including:
dump_sql - if True, print grants for base user
(no new users are created)
force - drop new users if they exist
verbosity - print add'l information during operation
quiet - do not print information during operation
Note: Error messages are printed regardless
global_privs - include global privileges (i.e. user@%)
Returns bool True = success, raises UtilError if error
"""
dump_sql = options.get("dump", False)
overwrite = options.get("overwrite", False)
verbosity = options.get("verbosity", False)
quiet = options.get("quiet", False)
global_privs = options.get("global_privs", False)
# Don't require destination for dumping base user grants
conn_options = {"quiet": quiet, "version": "5.1.0"}
# Add ssl certs if there are any.
conn_options["ssl_cert"] = options.get("ssl_cert", None)
conn_options["ssl_ca"] = options.get("ssl_ca", None)
conn_options["ssl_key"] = options.get("ssl_key", None)
if dump_sql:
servers = connect_servers(src_val, None, conn_options)
else:
servers = connect_servers(src_val, dest_val, conn_options)
source = servers[0]
destination = servers[1]
if destination is None:
destination = servers[0]
# Create an instance of the user class for source.
user_source = User(source, base_user, verbosity)
# Create an instance of the user class for destination.
user_dest = User(destination, base_user, verbosity)
# First find out what is the user that will be giving of grants in the
# destination server.
try:
res = destination.exec_query("SELECT CURRENT_USER()")
except UtilDBError as err:
raise UtilError(
"Unable to obtain information about the account used "
"to connect to the destination server: "
"{0}".format(err.errmsg)
)
# Create an instance of the user who will be giving the privileges.
user_priv_giver = User(destination, res[0][0], verbosity)
# Check to ensure base user exists.
if not user_source.exists(base_user):
raise UtilError("Base user does not exist!")
# Process dump operation
if dump_sql and not quiet:
_show_user_grants(source, user_source, base_user, verbosity)
return True
# Check to ensure new users don't exist.
if overwrite is None:
for new_user in new_user_list:
if user_dest.exists(new_user):
raise UtilError("User %s already exists. Use --force " "to drop and recreate user." % new_user)
if not quiet:
print "# Cloning %d users..." % (len(new_user_list))
# Check privileges to create/delete users.
can_create = can_drop = False
if user_priv_giver.has_privilege("*", "*", "CREATE_USER"):
can_create = can_drop = True
else:
if user_priv_giver.has_privilege("mysql", "*", "INSERT"):
can_create = True
if user_priv_giver.has_privilege("mysql", "*", "DELETE"):
can_drop = True
if not can_create: # Destination user cannot create new users.
raise UtilError(
"Destination user {0}@{1} needs either the "
"'CREATE USER' on *.* or 'INSERT' on mysql.* "
"privilege to create new users."
"".format(user_priv_giver.user, user_priv_giver.host)
)
# Perform the clone here. Loop through new users and clone.
for new_user in new_user_list:
if not quiet:
print "# Cloning %s to user %s " % (base_user, new_user)
# Check to see if user exists.
if user_dest.exists(new_user):
if not can_drop: # Destination user cannot drop existing users.
raise UtilError(
"Destination user {0}@{1} needs either the "
"'CREATE USER' on *.* or 'DELETE' on mysql.* "
"privilege to drop existing users."
"".format(user_priv_giver.user, user_priv_giver.host)
)
user_dest.drop(new_user)
# Clone user.
try:
missing_privs = user_priv_giver.missing_user_privileges(user_source, plus_grant_option=True)
if not missing_privs:
user_source.clone(new_user, destination, global_privs)
else:
# Our user lacks some privileges, lets create an informative
# error message
pluralize = "" if len(missing_privs) == 1 else "s"
missing_privs_str = ", ".join(
["{0} on {1}.{2}".format(priv, db, table) for priv, db, table in missing_privs]
)
raise UtilError(
"User {0} cannot be cloned because destination"
" user {1}@{2} is missing the following "
"privilege{3}: {4}."
"".format(new_user, user_priv_giver.user, user_priv_giver.host, pluralize, missing_privs_str)
)
except UtilError:
raise
if not quiet:
print "# ...done."
return True
def show_log_usage(server, datadir, options):
"""Show binary or relay log disk space usage.
Display binary log file information if binlog turned on if log_type =
'binary log' (default) or show relay log file information is server is
a slave and relay log is engaged.
server[in] Connected server to operate against
datadir[in] The datadir for the server
options[in] Required options for operation: format, no_headers.
log_type
return True or raise exception on error
"""
log_type = options.get("log_type", "binary log")
have_read = options.get("have_read", False)
is_remote = options.get("is_remote", False)
quiet = options.get("quiet", False)
# Check privileges to execute required queries: SUPER or REPLICATION CLIENT
user_inst = User(server, "{0}@{1}".format(server.user, server.host))
has_super = user_inst.has_privilege("*", "*", "SUPER")
has_rpl_client = user_inst.has_privilege("*", "*", "REPLICATION CLIENT")
# Verify necessary permissions (access to filesystem) and privileges
# (execute queries) to get logs usage information.
if log_type == "binary log":
# Check for binlog ON first.
res = server.show_server_variable("log_bin")
if res and res[0][1].upper() == "OFF":
print ("# Binary logging is turned off on the server.")
return True
# Check required privileges according to the access to the datadir.
if not is_remote and have_read:
# Requires SUPER or REPLICATION CLIENT to execute:
# SHOW MASTER STATUS.
if not has_super and not has_rpl_client:
print (
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize())
)
return True
else:
# Requires SUPER for server < 5.6.6 or also REPLICATION CLIENT for
# server >= 5.6.6 to execute: SHOW BINARY LOGS.
if server.check_version_compat(5, 6, 6) and not has_super and not has_rpl_client:
print (
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize())
)
return True
elif not has_super:
print (
"# {0} information not accessible. User must have the "
"SUPER "
"privilege.".format(log_type.capitalize())
)
return True
else: # relay log
# Requires SUPER or REPLICATION CLIENT to execute SHOW SLAVE STATUS.
if not has_super and not has_rpl_client:
print (
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize())
)
return True
# Can only retrieve usage information from the localhost filesystem.
if is_remote:
print ("# {0} information not accessible from a remote host." "".format(log_type.capitalize()))
return True
elif not have_read:
print (
"# {0} information not accessible. Check your permissions "
"to {1}.".format(log_type.capitalize(), datadir)
)
return True
# Check server status and availability of specified log file type.
if log_type == "binary log":
try:
res = server.exec_query("SHOW MASTER STATUS")
if res:
current_log = res[0][0]
else:
print ("# Cannot access files - no binary log information")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
else:
try:
res = server.exec_query("SHOW SLAVE STATUS")
if res:
current_log = res[0][7]
else:
print ("# Server is not an active slave - no relay log " "information.")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
# Enough permissions and privileges, get the usage information.
if not quiet:
print ("# {0} information:".format(log_type.capitalize()))
print ("Current {0} file = {1}".format(log_type, current_log))
if log_type == "binary log" and (is_remote or not have_read):
# Retrieve binlog usage info from SHOW BINARY LOGS.
try:
logs = server.exec_query("SHOW BINARY LOGS")
if logs:
# Calculate total size.
total = sum([int(item[1]) for item in logs])
else:
print ("# No binary logs data available.")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
else:
# Retrieve usage info from localhost filesystem.
# Note: as of 5.6.2, users can specify location of binlog and relaylog.
if server.check_version_compat(5, 6, 2):
if log_type == "binary log":
res = server.show_server_variable("log_bin_basename")[0]
else:
res = server.show_server_variable("relay_log_basename")[0]
log_path, log_prefix = os.path.split(res[1])
# In case log_path and log_prefix are '' (not defined) set them
# to the default value.
if not log_path:
log_path = datadir
if not log_prefix:
log_prefix = os.path.splitext(current_log)[0]
else:
log_path = datadir
log_prefix = os.path.splitext(current_log)[0]
logs, total = _build_log_list(log_path, log_prefix)
if not logs:
raise UtilError("The {0}s are missing.".format(log_type))
# Print logs usage information.
_print_logs(logs, total, options)
return True
def clone_user(src_val, dest_val, base_user, new_user_list, options):
"""Clone a user to one or more new user accounts
This method will create one or more new user accounts copying the
grant statements from a given user. If source and destination are the
same, the copy will occur on a single server otherwise, the caller may
specify a destination server to where the user accounts will be copied.
NOTES:
The user is responsible for making sure the databases and objects
referenced in the cloned GRANT statements exist prior to running this
utility.
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
dest_val[in] a dictionary containing connection information for the
destination including:
(user, password, host, port, socket)
base_user[in] the user account on the source machine to be used as
the template for the new users
user_list[in] a list of new user accounts in the form:
(username:password@host)
options[in] optional parameters dictionary including:
dump_sql - if True, print grants for base user
(no new users are created)
force - drop new users if they exist
verbosity - print add'l information during operation
quiet - do not print information during operation
Note: Error messages are printed regardless
global_privs - include global privileges (i.e. user@%)
Returns bool True = success, raises UtilError if error
"""
from mysql.utilities.common.server import connect_servers
from mysql.utilities.common.user import User
dump_sql = options.get("dump", False)
overwrite = options.get("overwrite", False)
verbosity = options.get("verbosity", False)
quiet = options.get("quiet", False)
global_privs = options.get("global_privs", False)
# Don't require destination for dumping base user grants
conn_options = {
'quiet' : quiet,
'version' : "5.1.0",
}
if dump_sql:
servers = connect_servers(src_val, None, conn_options)
else:
servers = connect_servers(src_val, dest_val, conn_options)
source = servers[0]
destination = servers[1]
if destination is None:
destination = servers[0]
# Create an instance of the user class for source.
user_source = User(source, base_user, verbosity)
# Create an instance of the user class for destination.
user_dest = User(destination, base_user, verbosity)
# Check to ensure base user exists.
if not user_source.exists(base_user):
raise UtilError("Base user does not exist!")
# Process dump operation
if dump_sql and not quiet:
print "Dumping grants for user " + base_user
user_source.print_grants()
return True
# Check to ensure new users don't exist.
if overwrite is None:
for new_user in new_user_list:
if user_dest.exists(new_user):
raise UtilError("User %s already exists. Use --force "
"to drop and recreate user." % new_user)
if not quiet:
print "# Cloning %d users..." % (len(new_user_list))
# Perform the clone here. Loop through new users and clone.
for new_user in new_user_list:
if not quiet:
print "# Cloning %s to user %s " % (base_user, new_user)
# Check to see if user exists.
if user_dest.exists(new_user):
user_dest.drop(new_user)
# Clone user.
try:
user_source.clone(new_user, destination, global_privs)
except UtilError, e:
raise
def _check_privileges(self):
"""Check required privileges to perform the multi-source replication.
This method check if the used users for the slave and masters have
the required privileges to perform the multi-source replication.
The following privileges are required:
- on slave: SUPER, SELECT, INSERT, UPDATE, REPLICATION
SLAVE AND GRANT OPTION;
- on the master: SUPER, SELECT, INSERT, UPDATE, REPLICATION SLAVE
AND GRANT OPTION.
An exception is thrown if users doesn't have enough privileges.
"""
if self.verbosity > 0:
print("# Checking users privileges for replication.\n#")
# Connection dictionary
conn_dict = {
"conn_info": None,
"quiet": True,
"verbose": self.verbosity > 0,
}
# Check privileges for master.
master_priv = [('SUPER',), ('SELECT',), ('INSERT',), ('UPDATE',),
('REPLICATION SLAVE',), ('GRANT OPTION',)]
master_priv_str = ("SUPER, SELECT, INSERT, UPDATE, REPLICATION SLAVE "
"AND GRANT OPTION")
for master_vals in self.masters_vals:
conn_dict["conn_info"] = master_vals
master = Master(conn_dict)
master.connect()
user_obj = User(master, "{0}@{1}".format(master.user, master.host))
for any_priv_tuple in master_priv:
has_privilege = any(
[user_obj.has_privilege('*', '*', priv)
for priv in any_priv_tuple]
)
if not has_privilege:
msg = ERROR_USER_WITHOUT_PRIVILEGES.format(
user=master.user, host=master.host, port=master.port,
operation='perform replication',
req_privileges=master_priv_str
)
self._report(msg, logging.CRITICAL, False)
raise UtilRplError(msg)
master.disconnect()
# Check privileges for slave
slave_priv = [('SUPER',), ('SELECT',), ('INSERT',), ('UPDATE',),
('REPLICATION SLAVE',), ('GRANT OPTION',)]
slave_priv_str = ("SUPER, SELECT, INSERT, UPDATE, REPLICATION SLAVE "
"AND GRANT OPTION")
conn_dict["conn_info"] = self.slave_vals
slave = Slave(conn_dict)
slave.connect()
user_obj = User(slave, "{0}@{1}".format(slave.user, slave.host))
for any_priv_tuple in slave_priv:
has_privilege = any(
[user_obj.has_privilege('*', '*', priv)
for priv in any_priv_tuple]
)
if not has_privilege:
msg = ("User '{0}' on '{1}@{2}' does not have sufficient "
"privileges to perform replication (required: {3})."
"".format(slave.user, slave.host, slave.port,
slave_priv_str))
self._report(msg, logging.CRITICAL, False)
raise UtilRplError(msg)
slave.disconnect()
def clone_user(src_val, dest_val, base_user, new_user_list, options):
"""Clone a user to one or more new user accounts
This method will create one or more new user accounts copying the
grant statements from a given user. If source and destination are the
same, the copy will occur on a single server otherwise, the caller may
specify a destination server to where the user accounts will be copied.
NOTES:
The user is responsible for making sure the databases and objects
referenced in the cloned GRANT statements exist prior to running this
utility.
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
dest_val[in] a dictionary containing connection information for the
destination including:
(user, password, host, port, socket)
base_user[in] the user account on the source machine to be used as
the template for the new users
user_list[in] a list of new user accounts in the form:
(username:password@host)
options[in] optional parameters dictionary including:
dump_sql - if True, print grants for base user
(no new users are created)
force - drop new users if they exist
verbosity - print add'l information during operation
quiet - do not print information during operation
Note: Error messages are printed regardless
global_privs - include global privileges (i.e. user@%)
Returns bool True = success, raises UtilError if error
"""
dump_sql = options.get("dump", False)
overwrite = options.get("overwrite", False)
verbosity = options.get("verbosity", False)
quiet = options.get("quiet", False)
global_privs = options.get("global_privs", False)
# Don't require destination for dumping base user grants
conn_options = {
'quiet': quiet,
'version': "5.1.0",
}
# Add ssl certs if there are any.
conn_options['ssl_cert'] = options.get("ssl_cert", None)
conn_options['ssl_ca'] = options.get("ssl_ca", None)
conn_options['ssl_key'] = options.get("ssl_key", None)
if dump_sql:
servers = connect_servers(src_val, None, conn_options)
else:
servers = connect_servers(src_val, dest_val, conn_options)
source = servers[0]
destination = servers[1]
if destination is None:
destination = servers[0]
# Create an instance of the user class for source.
user_source = User(source, base_user, verbosity)
# Create an instance of the user class for destination.
user_dest = User(destination, base_user, verbosity)
# First find out what is the user that will be giving of grants in the
# destination server.
try:
res = destination.exec_query("SELECT CURRENT_USER()")
except UtilDBError as err:
raise UtilError("Unable to obtain information about the account used "
"to connect to the destination server: "
"{0}".format(err.errmsg))
# Create an instance of the user who will be giving the privileges.
user_priv_giver = User(destination, res[0][0], verbosity)
# Check to ensure base user exists.
if not user_source.exists(base_user):
raise UtilError("Base user does not exist!")
# Process dump operation
if dump_sql and not quiet:
_show_user_grants(source, user_source, base_user, verbosity)
return True
# Check to ensure new users don't exist.
if overwrite is None:
for new_user in new_user_list:
if user_dest.exists(new_user):
raise UtilError("User %s already exists. Use --force "
"to drop and recreate user." % new_user)
if not quiet:
print("# Cloning %d users..." % (len(new_user_list)))
# Check privileges to create/delete users.
can_create = can_drop = False
if user_priv_giver.has_privilege('*', '*', "CREATE_USER"):
can_create = can_drop = True
else:
if user_priv_giver.has_privilege('mysql', '*', "INSERT"):
can_create = True
if user_priv_giver.has_privilege('mysql', '*', "DELETE"):
can_drop = True
if not can_create: # Destination user cannot create new users.
raise UtilError("Destination user {0}@{1} needs either the "
"'CREATE USER' on *.* or 'INSERT' on mysql.* "
"privilege to create new users."
"".format(user_priv_giver.user, user_priv_giver.host))
# Perform the clone here. Loop through new users and clone.
for new_user in new_user_list:
if not quiet:
print("# Cloning %s to user %s " % (base_user, new_user))
# Check to see if user exists.
if user_dest.exists(new_user):
if not can_drop: # Destination user cannot drop existing users.
raise UtilError("Destination user {0}@{1} needs either the "
"'CREATE USER' on *.* or 'DELETE' on mysql.* "
"privilege to drop existing users."
"".format(user_priv_giver.user,
user_priv_giver.host))
user_dest.drop(new_user)
# Clone user.
try:
missing_privs = user_priv_giver.missing_user_privileges(
user_source, plus_grant_option=True)
if not missing_privs:
user_source.clone(new_user, destination, global_privs)
else:
# Our user lacks some privileges, lets create an informative
# error message
pluralize = '' if len(missing_privs) == 1 else 's'
missing_privs_str = ', '.join(
["{0} on {1}.{2}".format(priv, db, table) for
priv, db, table in missing_privs])
raise UtilError("User {0} cannot be cloned because destination"
" user {1}@{2} is missing the following "
"privilege{3}: {4}."
"".format(new_user, user_priv_giver.user,
user_priv_giver.host, pluralize,
missing_privs_str))
except UtilError:
raise
if not quiet:
print("# ...done.")
return True
def check_grants(server_cnx_val, options, dict_of_objects):
"""Show list of privileges over a set of objects
This function creates a GrantShow object which shows the list of
users with (the optionally specified list of ) privileges over the
specified set of objects.
server_cnx_val[in] Dictionary with the connection values to the
server.
options[in] Dictionary of options (verbosity, privileges,
show_mode).
list_of_objects[in] Dictionary of objects (set of databases, tables
and procedures) by database to check.
"""
# Create server connection:
server = connect_servers(server_cnx_val, None, options)[0]
# Check user permissions to consult the grant information.
_check_privileges(server)
# Validate the dict of objects against our server.
valid_dict_of_objects = validate_obj_type_dict(server, dict_of_objects)
# Get optional list of required privileges
req_privs = set(options['privileges']) if options['privileges'] else None
# If we specify some privileges that are not valid for all the objects
# print warning message stating that some will be ignored.
if req_privs:
for obj_type in valid_dict_of_objects:
# get list of privileges that applies to the object type
filtered_req_privs = filter_grants(req_privs, obj_type)
# if the size of the set is different that means that some of the
# privileges cannot be applied to this object type, print warning
if len(filtered_req_privs) != len(req_privs):
if obj_type.upper() == DATABASE_TYPE:
obj_lst = [obj_tpl[0] for obj_tpl in
valid_dict_of_objects[obj_type]]
else:
obj_lst = [".".join(obj_tpl) for obj_tpl in
valid_dict_of_objects[obj_type]]
obj_lst_str = join_and_build_str(obj_lst)
missing_privs = sorted(req_privs - filtered_req_privs)
priv_str = join_and_build_str(missing_privs)
verb = "do" if len(missing_privs) > 1 else "does"
print("# WARNING: {0} {1} not apply to {2}s "
"and will be ignored for: {3}.".format(
priv_str, verb, obj_type.lower(), obj_lst_str))
# get the grantee information dictionary
grantee_info_dict = get_grantees(server, valid_dict_of_objects,
req_privileges=req_privs)
# Print the information
obj_type_lst = [DATABASE_TYPE, TABLE_TYPE, ROUTINE_TYPE]
for obj_type in obj_type_lst:
if obj_type in grantee_info_dict:
# Sort by object name
for obj_name in sorted(grantee_info_dict[obj_type]):
print("\n# {0} {1}:".format(obj_type, obj_name))
if options['show_mode'] == 'users':
# Sort by grantee name
output_str = ", ".join(
sorted(grantee_info_dict[obj_type][obj_name].keys()))
print("# - {0}".format(output_str))
elif options['show_mode'] == 'user_grants':
# Sort by grantee name
for grantee, priv_set in sorted(
grantee_info_dict[obj_type][obj_name].iteritems()):
# print privileges sorted by name
print("# - {0} : {1}".format(
grantee, ", ".join(sorted(priv_set))))
else: # raw mode
# Sort by grantee name
for grantee in sorted(
grantee_info_dict[obj_type][obj_name].keys()):
user = User(server, grantee)
grant_stms = sorted(
user.get_grants_for_object(obj_name, obj_type))
if grant_stms:
print("# - For {0}".format(grantee))
for grant_stm in grant_stms:
print("{0}".format(grant_stm))
def clone_user(src_val, dest_val, base_user, new_user_list, options):
"""Clone a user to one or more new user accounts
This method will create one or more new user accounts copying the
grant statements from a given user. If source and destination are the
same, the copy will occur on a single server otherwise, the caller may
specify a destination server to where the user accounts will be copied.
NOTES:
The user is responsible for making sure the databases and objects
referenced in the cloned GRANT statements exist prior to running this
utility.
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
dest_val[in] a dictionary containing connection information for the
destination including:
(user, password, host, port, socket)
base_user[in] the user account on the source machine to be used as
the template for the new users
user_list[in] a list of new user accounts in the form:
(username:password@host)
options[in] optional parameters dictionary including:
dump_sql - if True, print grants for base user
(no new users are created)
force - drop new users if they exist
verbosity - print add'l information during operation
quiet - do not print information during operation
Note: Error messages are printed regardless
global_privs - include global privileges (i.e. user@%)
Returns bool True = success, raises UtilError if error
"""
from mysql.utilities.common.server import connect_servers
from mysql.utilities.common.user import User
dump_sql = options.get("dump", False)
overwrite = options.get("overwrite", False)
verbosity = options.get("verbosity", False)
quiet = options.get("quiet", False)
global_privs = options.get("global_privs", False)
# Don't require destination for dumping base user grants
conn_options = {
'quiet': quiet,
'version': "5.1.0",
}
if dump_sql:
servers = connect_servers(src_val, None, conn_options)
else:
servers = connect_servers(src_val, dest_val, conn_options)
source = servers[0]
destination = servers[1]
if destination is None:
destination = servers[0]
# Create an instance of the user class for source.
user_source = User(source, base_user, verbosity)
# Create an instance of the user class for destination.
user_dest = User(destination, base_user, verbosity)
# Check to ensure base user exists.
if not user_source.exists(base_user):
raise UtilError("Base user does not exist!")
# Process dump operation
if dump_sql and not quiet:
_show_user_grants(source, user_source, base_user, verbosity)
return True
# Check to ensure new users don't exist.
if overwrite is None:
for new_user in new_user_list:
if user_dest.exists(new_user):
raise UtilError("User %s already exists. Use --force "
"to drop and recreate user." % new_user)
if not quiet:
print "# Cloning %d users..." % (len(new_user_list))
# Perform the clone here. Loop through new users and clone.
for new_user in new_user_list:
if not quiet:
print "# Cloning %s to user %s " % (base_user, new_user)
# Check to see if user exists.
if user_dest.exists(new_user):
user_dest.drop(new_user)
# Clone user.
try:
user_source.clone(new_user, destination, global_privs)
except UtilError, e:
raise
def show_log_usage(server, datadir, options):
"""Show binary or relay log disk space usage.
Display binary log file information if binlog turned on if log_type =
'binary log' (default) or show relay log file information is server is
a slave and relay log is engaged.
server[in] Connected server to operate against
datadir[in] The datadir for the server
options[in] Required options for operation: format, no_headers.
log_type
return True or raise exception on error
"""
log_type = options.get("log_type", "binary log")
quiet = options.get("quiet", False)
# Check privileges to execute required queries: SUPER or REPLICATION CLIENT
user_inst = User(server, "{0}@{1}".format(server.user, server.host))
has_super = user_inst.has_privilege("*", "*", "SUPER")
has_rpl_client = user_inst.has_privilege("*", "*", "REPLICATION CLIENT")
# Verify necessary permissions (access to filesystem) and privileges
# (execute queries) to get logs usage information.
if log_type == 'binary log':
# Check for binlog ON first.
res = server.show_server_variable('log_bin')
if res and res[0][1].upper() == 'OFF':
print("# Binary logging is turned off on the server.")
return True
# Check required privileges according to the access to the datadir.
if os.access(datadir, os.R_OK):
# Requires SUPER or REPLICATION CLIENT to execute:
# SHOW MASTER STATUS.
if not has_super and not has_rpl_client:
print(
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize()))
return True
else:
# Requires SUPER for server < 5.6.6 or also REPLICATION CLIENT for
# server >= 5.6.6 to execute: SHOW BINARY LOGS.
if (server.check_version_compat(5, 6, 6) and not has_super
and not has_rpl_client):
print(
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize()))
return True
elif not has_super:
print(
"# {0} information not accessible. User must have the "
"SUPER "
"privilege.".format(log_type.capitalize()))
return True
else: # relay log
# Requires SUPER or REPLICATION CLIENT to execute SHOW SLAVE STATUS.
if not has_super and not has_rpl_client:
print(
"# {0} information not accessible. User must have the "
"SUPER or REPLICATION CLIENT "
"privilege.".format(log_type.capitalize()))
return True
# Can only retrieve usage information from the localhost filesystem.
if not os.access(datadir, os.R_OK):
if not server.is_alias("localhost"):
print(
"# {0} information not accessible from a remote "
"host.".format(log_type.capitalize()))
else:
print(
"# {0} information not accessible. Check your "
"permissions to {1}.".format(log_type.capitalize(),
datadir))
return True
# Check server status and availability of specified log file type.
if log_type == 'binary log':
try:
res = server.exec_query("SHOW MASTER STATUS")
if res:
current_log = res[0][0]
else:
print("# Cannot access files - no binary log information")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
else:
try:
res = server.exec_query("SHOW SLAVE STATUS")
if res:
current_log = res[0][7]
else:
print(
"# Server is not an active slave - no relay log "
"information.")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
# Enough permissions and privileges, get the usage information.
if not quiet:
print("# {0} information:".format(log_type.capitalize()))
print("Current {0} file = {1}".format(log_type, current_log))
if log_type == 'binary log' and not os.access(datadir, os.R_OK):
# Retrieve binlog usage info from SHOW BINARY LOGS.
try:
logs = server.exec_query("SHOW BINARY LOGS")
if logs:
# Calculate total size.
total = sum([int(item[1]) for item in logs])
else:
print("# No binary logs data available.")
return True
except:
raise UtilError("Cannot get {0} information.".format(log_type))
else:
# Retrieve usage info from localhost filesystem.
# Note: as of 5.6.2, users can specify location of binlog and relaylog.
if server.check_version_compat(5, 6, 2):
if log_type == 'binary log':
res = server.show_server_variable("log_bin_basename")[0]
else:
res = server.show_server_variable("relay_log_basename")[0]
log_path, log_prefix = os.path.split(res[1])
# In case log_path and log_prefix are '' (not defined) set them
# to the default value.
if not log_path:
log_path = datadir
if not log_prefix:
log_prefix = os.path.splitext(current_log)[0]
else:
log_path = datadir
log_prefix = os.path.splitext(current_log)[0]
logs, total = _build_log_list(log_path, log_prefix)
if not logs:
raise UtilError("The {0}s are missing.".format(log_type))
# Print logs usage information.
_print_logs(logs, total, options)
return True
def _check_privileges(self):
"""Check required privileges to perform the multi-source replication.
This method check if the used users for the slave and masters have
the required privileges to perform the multi-source replication.
The following privileges are required:
- on slave: SUPER, SELECT, INSERT, UPDATE, REPLICATION
SLAVE AND GRANT OPTION;
- on the master: SUPER, SELECT, INSERT, UPDATE, REPLICATION SLAVE
AND GRANT OPTION.
An exception is thrown if users doesn't have enough privileges.
"""
if self.verbosity > 0:
print("# Checking users privileges for replication.\n#")
# Connection dictionary
conn_dict = {
"conn_info": None,
"quiet": True,
"verbose": self.verbosity > 0,
}
# Check privileges for master.
master_priv = [('SUPER',), ('SELECT',), ('INSERT',), ('UPDATE',),
('REPLICATION SLAVE',), ('GRANT OPTION',)]
master_priv_str = ("SUPER, SELECT, INSERT, UPDATE, REPLICATION SLAVE "
"AND GRANT OPTION")
for master_vals in self.masters_vals:
conn_dict["conn_info"] = master_vals
master = Master(conn_dict)
master.connect()
user_obj = User(master, "{0}@{1}".format(master.user, master.host))
for any_priv_tuple in master_priv:
has_privilege = any(
[user_obj.has_privilege('*', '*', priv)
for priv in any_priv_tuple]
)
if not has_privilege:
msg = ERROR_USER_WITHOUT_PRIVILEGES.format(
user=master.user, host=master.host, port=master.port,
operation='perform replication',
req_privileges=master_priv_str
)
self._report(msg, logging.CRITICAL, False)
raise UtilRplError(msg)
master.disconnect()
# Check privileges for slave
slave_priv = [('SUPER',), ('SELECT',), ('INSERT',), ('UPDATE',),
('REPLICATION SLAVE',), ('GRANT OPTION',)]
slave_priv_str = ("SUPER, SELECT, INSERT, UPDATE, REPLICATION SLAVE "
"AND GRANT OPTION")
conn_dict["conn_info"] = self.slave_vals
slave = Slave(conn_dict)
slave.connect()
user_obj = User(slave, "{0}@{1}".format(slave.user, slave.host))
for any_priv_tuple in slave_priv:
has_privilege = any(
[user_obj.has_privilege('*', '*', priv)
for priv in any_priv_tuple]
)
if not has_privilege:
msg = ("User '{0}' on '{1}@{2}' does not have sufficient "
"privileges to perform replication (required: {3})."
"".format(slave.user, slave.host, slave.port,
slave_priv_str))
self._report(msg, logging.CRITICAL, False)
raise UtilRplError(msg)
slave.disconnect()
