def check_user_privileges(self, priv_values, alt_server=None):
"""Verifies the given user's names accounts privileges
:param priv_values: Dictionary with user name as key and list of
privileges as value.
:type priv_values: dict
:param alt_server: An alternative server instance to use.
:type alt_server: Server instance.
:raise GadgetError: If the user does not have SELECT
privilege on mysql.user.
:return: A dictionary with the results, including the key pass with
True if the user has the privileges and False otherwise, and
a key with the checked users and the missing privileges as
value (or 'NO EXISTS!' if user does not exists).
:rtype: dict
"""
_LOGGER.debug('privileges Checking')
# Get the correct server to Validate
server = self._get_server(alt_server=alt_server)
# Store Result
results = {}
# Check required privileges
for user, privs in priv_values.items():
_LOGGER.debug('User: %s required privileges: %s', user, privs)
user_obj = User(server, user)
try:
# verify the user exists
if user_obj.exists():
missing_privs = user_obj.check_missing_privileges(privs)
_LOGGER.debug('missing privileges: %s', missing_privs)
results[user] = missing_privs
if missing_privs:
results["pass"] = False
else:
# The user's result will be set to ['NO EXISTS!']
# as [] and None are evaluated to False
results[user] = ['NO EXISTS!']
results["pass"] = False
except GadgetQueryError as err:
if "SELECT command denied" in err.errmsg:
raise GadgetError("User {} does not have SELECT privileges"
" on user table; unable to check "
"privileges with this account."
"".format(server.user))
else:
raise
if "pass" not in results.keys():
results["pass"] = True
_LOGGER.debug('Privileges check result: %s', results)
return results
def check_user_privileges(self, priv_values, alt_server=None):
"""Verifies the given user's names accounts privileges
:param priv_values: Dictionary with user name as key and list of
privileges as value.
:type priv_values: dict
:param alt_server: An alternative server instance to use.
:type alt_server: Server instance.
:raise GadgetError: If the user does not have SELECT
privilege on mysql.user.
:return: A dictionary with the results, including the key pass with
True if the user has the privileges and False otherwise, and
a key with the checked users and the missing privileges as
value (or 'NO EXISTS!' if user does not exists).
:rtype: dict
"""
_LOGGER.debug('privileges Checking')
# Get the correct server to Validate
server = self._get_server(alt_server=alt_server)
# Store Result
results = {}
# Check required privileges
for user, privs in priv_values.items():
_LOGGER.debug('User: %s required privileges: %s', user, privs)
user_obj = User(server, user)
try:
# verify the user exists
if user_obj.exists():
missing_privs = user_obj.check_missing_privileges(privs)
_LOGGER.debug('missing privileges: %s', missing_privs)
results[user] = missing_privs
if missing_privs:
results["pass"] = False
else:
# The user's result will be set to ['NO EXISTS!']
# as [] and None are evaluated to False
results[user] = ['NO EXISTS!']
results["pass"] = False
except GadgetQueryError as err:
if "SELECT command denied" in err.errmsg:
raise GadgetError("User {} does not have SELECT privileges"
" on user table; unable to check "
"privileges with this account."
"".format(server.user))
else:
raise
if "pass" not in results.keys():
results["pass"] = True
_LOGGER.debug('Privileges check result: %s', results)
return results
def test_create_user(self):
""" Tests User.create_user methods"""
self.server.exec_query("Drop USER if exists 'joe'@'users'")
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
user_name = 'Jonh_CAPITALS'
user_root.create(new_user="******".format(user_name,
self.server.host),
passwd="his_pass", ssl=True, disable_binlog=True)
user_root.exists("{0}@{1}".format(user_name, self.server.host))
user_obj2 = User(self.server, "{0}@{1}".format('joe', 'users'))
user_root.drop(new_user="******".format(user_name, self.server.host))
user_obj2.drop()
self.assertFalse((self.server.user_host_exists('Jonh_CAPITALS',
self.server.host)))
def test_get_grants(self):
""" Tests get_grants method"""
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
user_obj2 = User(self.server, "{0}@{1}".format('jose', '%'),
verbosity=True)
self.assertListEqual(user_obj2.get_grants(globals_privs=True), [])
user_obj2.create()
# Test user has none grants
self.assertListEqual(user_obj2.get_grants(globals_privs=True),
[("GRANT USAGE ON *.* TO 'jose'@'%'",)])
self.assertDictEqual(user_obj2.get_grants(as_dict=True),
{'*': {'*': {'USAGE'}}})
# Test get global privileges
self.server.exec_query("GRANT PROXY ON '{0}'@'{1}' TO '{0}'@'%'"
"".format('jose', self.server.host))
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format('jose', '%'))
exp_list_res = [("GRANT USAGE ON *.* TO 'jose'@'%'",),
("GRANT UPDATE ON `mysql`.* TO 'jose'@'%'",),
("GRANT PROXY ON 'jose'@'{0}' TO 'jose'@'%'"
"".format(self.server.host),)]
self.assertListEqual(user_obj2.get_grants(globals_privs=True,
refresh=True),
exp_list_res)
self.assertDictEqual(user_obj2.get_grants(as_dict=True, refresh=True),
{'*': {'*': {'USAGE'}},
'`mysql`': {'*': {'UPDATE'}}})
user_obj2.drop()
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
def test_create_user(self):
""" Tests User.create_user methods"""
self.server.exec_query("Drop USER if exists 'joe'@'users'")
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
user_name = 'Jonh_CAPITALS'
user_root.create(new_user="******".format(user_name,
self.server.host),
passwd="his_pass",
ssl=True,
disable_binlog=True)
user_root.exists("{0}@{1}".format(user_name, self.server.host))
user_obj2 = User(self.server, "{0}@{1}".format('joe', 'users'))
user_root.drop(new_user="******".format(user_name, self.server.host))
user_obj2.drop()
self.assertFalse((self.server.user_host_exists('Jonh_CAPITALS',
self.server.host)))
def test_parse_grant_statement(self):
""" Tests parse_grant_statement Method.
"""
# Test function
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE, EXECUTE ON FUNCTION `util_test`.`f1` TO "
"'priv_test_user2'@'%' WITH GRANT OPTION")
self.assertEqual(
parsed_statement,
(set(['GRANT OPTION', 'EXECUTE', 'ALTER ROUTINE'
]), None, '`util_test`', '`f1`', "'priv_test_user2'@'%'"))
# Test procedure
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO "
"'priv_test_user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'ALTER ROUTINE']), None,
'`util_test`', '`p1`', "'priv_test_user2'@'%'"))
# Test with quoted objects
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.``export_2` TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``',
'``export_2`', "'priv_test_user'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.* TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``', '*',
"'priv_test_user'@'%'"))
# Test multiple grants with password and grant option
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT'
]), None, '`mysql`', '*', "'user2'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT'
]), None, '`mysql`', '*', "'user2'@'%'"))
# Test proxy privileges
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY'
]), "''@''", None, None, "'root'@'localhost'"))
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON 'root'@'%' TO 'root'@'localhost' WITH GRANT "
"OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY']), "'root'@'%'", None,
None, "'root'@'localhost'"))
# Test parse grant with ansi quotes
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'",
sql_mode="ANSI_QUOTES")
self.assertEqual(parsed_statement[4], ("user2@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[2], '`mysql`')
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[3], '`user`')
self.assertRaises(
GadgetError, User.parse_grant_statement,
"GRANT PROXY 'root'@'%' TO 'root'@'localhost' WITH "
"GRANT OPTION")
def test_user_has_privilege(self):
""" Tests USER.has_privilege method"""
user_name = 'Jonh_Update'
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
db_ = "mysql"
obj = "user"
access = "UPDATE"
skip_grant = False
# Test object level privilege with user with global * privilege
self.assertTrue(user_root.has_privilege(db_, obj, access, skip_grant))
# create new user to test missing privileges.
user_update = User(self.server,
"{0}@{1}".format(user_name, self.server.host))
user_update.create()
# Test privileges disabled
self.server.grants_enabled = False
skip_grant = True
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.grants_enabled = True
skip_grant = True
# Test missing privilege
self.assertFalse(
user_update.has_privilege(db_, obj, access, skip_grant))
access = "USAGE"
# Test default privilege USAGE at db level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.exec_query("GRANT UPDATE ON mysql.user TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
access = "UPDATE"
# Test privilege at object level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
# Test privilege at db level
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
user_update.drop()
def test_get_grants(self):
""" Tests get_grants method"""
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
user_obj2 = User(self.server,
"{0}@{1}".format('jose', '%'),
verbosity=True)
self.assertListEqual(user_obj2.get_grants(globals_privs=True), [])
user_obj2.create()
# Test user has none grants
self.assertListEqual(user_obj2.get_grants(globals_privs=True),
[("GRANT USAGE ON *.* TO 'jose'@'%'", )])
self.assertDictEqual(user_obj2.get_grants(as_dict=True),
{'*': {
'*': {'USAGE'}
}})
# Test get global privileges
self.server.exec_query("GRANT PROXY ON '{0}'@'{1}' TO '{0}'@'%'"
"".format('jose', self.server.host))
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format('jose', '%'))
exp_list_res = [("GRANT USAGE ON *.* TO 'jose'@'%'", ),
("GRANT UPDATE ON `mysql`.* TO 'jose'@'%'", ),
("GRANT PROXY ON 'jose'@'{0}' TO 'jose'@'%'"
"".format(self.server.host), )]
self.assertListEqual(
user_obj2.get_grants(globals_privs=True, refresh=True),
exp_list_res)
self.assertDictEqual(user_obj2.get_grants(as_dict=True, refresh=True),
{
'*': {
'*': {'USAGE'}
},
'`mysql`': {
'*': {'UPDATE'}
}
})
user_obj2.drop()
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
def test_check_missing_privileges(self):
""" Tests the User's check_missing_privileges method"""
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_name = 'Jonh_CAPITALS'
user_obj = User(self.server,
"{0}@{1}".format(user_name, self.server.host),
verbosity=1)
self.assertFalse(user_obj.exists())
user_obj.create(disable_binlog=True, ssl=True)
self.assertTrue(user_obj.exists())
self.assertListEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"], as_string=False),
["CREATE USER", "REPLICATION SLAVE"])
self.assertEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"]),
"CREATE USER and REPLICATION SLAVE")
change_user_privileges(self.server,
user_name,
self.server.host,
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True)
self.assertListEqual(user_obj.get_grants(refresh=True),
[("GRANT REPLICATION SLAVE, CREATE USER ON *.* "
"TO 'Jonh_CAPITALS'@'localhost'", )])
self.assertListEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"], as_string=False), [])
self.assertEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"]), "")
user_obj.drop()
user_name = 'some_user'
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
change_user_privileges(self.server,
user_name,
self.server.host,
user_passwd="some pass",
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True,
create_user=True)
change_user_privileges(self.server,
user_name,
self.server.host,
revoke_list=["REPLICATION SLAVE"],
disable_binlog=True)
# expect GadgetError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
user_obj.drop(user_name)
exception = test_raises.exception
self.assertTrue("Cannot parse user@host" in exception.errmsg,
"The exception message was not the expected")
user_obj.drop("{0}@{1}".format(user_name, self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
def test_check_server_requirements(self):
"""Tests check_server_requirements method"""
skip_if_not_GR_approved(self.server1)
self.server.exec_query("drop user if exists 'new_rpl_user'")
self.server.exec_query("drop user if exists 'replic_user'@'%'")
self.server.exec_query("drop user if exists 'replic_user'@'localhost'")
# Test with default values, server.user must have SUPER
options = {
"replication_user": "******",
"rep_user_passwd": "rplr_pass",
}
rpl_settings = get_rpl_usr(options)
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
self.assertTrue(
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
self.assertTrue(
User(self.server, "replic_user@%", None).exists(),
"User was not created on check_server_requirements")
# Test using an admin user without CREATE USER privilege.
grant_list = ["SELECT"]
self.server.exec_query("DROP USER IF EXISTS "
"'replic_user'@'localhost'")
change_user_privileges(self.server,
"replic_user",
'localhost',
"rplr_pass",
grant_list=grant_list,
create_user=True)
server2 = Server({
"conn_info":
"replic_user@localhost:{0}"
"".format(self.server.port)
})
server2.passwd = "rplr_pass"
server2.connect()
qry_key = ("select MEMBER_HOST, MEMBER_PORT from {0}"
"".format(REP_GROUP_MEMBERS_TABLE))
frozen_queries = {qry_key: [[server2.host, server2.port]]}
mock_server = get_mock_server(server2, variables=frozen_queries)
options = {
"replication_user": "******",
"rep_user_passwd": "rpl_pass",
}
rpl_settings = get_rpl_usr(options)
req_dict = get_req_dict(server2, rpl_settings["replication_user"])
# expect GadgetError: Query failed: No required privileges
# to create the replication user
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"required privileges to create" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test existing user and admin user without REPLICATION SLAVE grant
grant_list = ["SELECT"]
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list,
revoke_list=revoke_list,
create_user=True,
with_grant=True)
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"replic_user",
"%",
revoke_list=revoke_list)
# expect GadgetError: does not have the REPLICATION SLAVE privilege,
# and can not be granted by.
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"does not have the REPLICATION SLAVE privilege, "
"and can not be granted by" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
self.assertTrue(
"SUPER privilege required to disable the binlog."
in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# self.server.exec_query("drop user if exists 'replic_user'@'%'")
# Test existing user and admin user without REPLICATION SLAVE grant
grant_list = ["SELECT"]
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list,
revoke_list=revoke_list)
grant_list = ["REPLICATION SLAVE", "SUPER"]
change_user_privileges(self.server,
"replic_user",
"localhost",
grant_list=grant_list,
with_grant=True)
# reset session to get new privileges.
server2.disconnect()
server2.connect()
mock_server = get_mock_server(server2, variables=frozen_queries)
self.assertTrue(
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
# Test existing rpl user and admin user without grant
# admin user: replic_user
# rpl user: new_rpl_user
grant_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list)
grant_list = ["SELECT", "CREATE USER"]
change_user_privileges(self.server,
"create_rpl",
"127.0.0.1",
user_passwd="c_pass",
grant_list=grant_list,
create_user=True,
with_grant=True)
server3 = Server({
"conn_info":
"[email protected]:{0}"
"".format(self.server.port)
})
server3.passwd = "c_pass"
server3.connect()
req_dict3 = get_req_dict(server3, rpl_settings["replication_user"])
# expect GadgetError: No required privileges
# to grant Replication Slave privilege
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(server3,
req_dict3,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"SUPER privilege needed to run the CHANGE MASTER "
"command" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test invalid server_id
mock_server = get_mock_server(self.server,
variables={"server_id": "0"})
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
# expect GadgetError: server_id not valid
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"is not valid, it must be a positive integer" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test duplicate server_id
mock_server = get_mock_server(self.server,
variables={"server_id": "101"})
req_dict["SERVER_ID"] = {"peers": [mock_server]}
# expect GadgetError: server_id is already used by peer
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"is already used by peer" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test existing user and admin with required grants
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
grant_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list)
self.assertTrue(
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
# Tests server variables not meet required values
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
req_dict[SERVER_VARIABLES] = {
"log_bin": {
ONE_OF: ("0", )
},
"binlog_format": {
ONE_OF: ("", )
},
"binlog_checksum": {
ONE_OF: ("", )
},
"gtid_mode": {
ONE_OF: ("OFF", )
},
"log_slave_updates": {
ONE_OF: ("0", )
},
"enforce_gtid_consistency": {
ONE_OF: ("OFF", )
},
}
# expect GadgetError: change the configuration values
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=True,
update=False,
skip_backup=True)
exception = test_raises.exception
self.assertIn(
"on server {0} are incompatible with Group "
"Replication.".format(self.server), exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test server version
req_dict[SERVER_VERSION] = "99.9.9"
# expect GadgetError: Query failed: server version
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=True,
update=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"does not meet the required MySQL server version"
in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
self.server.exec_query("drop user if exists 'replic_user'")
self.server.exec_query("drop user if exists 'create_rpl'")
self.server.exec_query("drop user if exists 'new_rpl_user'")
def pre_validation(connection_dict):
""" Checks for requirements before clone operation is executed.
If requirements are not met, the implementation must raise an exception
and as a result the clone operation will be cancelled before it starts.
The message of the exception will be logged as the cause of the clone
operation having not met the pre-clone requirements.
param connection_dict: dictionary of dictionaries of connection
information: mysql users and host users. It can
have the following keys: MYSQL_SOURCE,
MYSQL_DEST, HOST_SOURCE and HOST_DEST.
Each of these keys has as a value a dict with
the following keys: user, hostname, port,
passwd and their respective values.
:raises: Exception if pre-clone requirements are not met.
"""
# Creating Server instances for source and destination servers
source_dict = connection_dict[MYSQL_SOURCE]
destination_dict = connection_dict[MYSQL_DEST]
try:
source_server = Server({'conn_info': source_dict})
except exceptions.GadgetError as err:
_LOGGER.error(
"Unable to create a Server instance for source server."
"Source dict was: %s", source_dict)
raise err
try:
destination_server = Server({'conn_info': destination_dict})
except exceptions.GadgetError as err:
_LOGGER.error(
"Unable to create a Server instance for destination "
"server. Destination dict was: %s", destination_dict)
raise err
# Connect to source and destination servers
try:
source_server.connect()
except exceptions.GadgetServerError as err:
raise exceptions.GadgetError(
"Unable to connect to source server: {0}".format(str(err)))
try:
destination_server.connect()
except exceptions.GadgetServerError as err:
raise exceptions.GadgetError("Unable to connect to destination "
"server: {0}.".format(str(err)))
# Check if source server has the same GTID mode as destination server
_LOGGER.debug("Checking if source server and destination have the "
"same GTID mode.")
try:
source_has_gtids = source_server.supports_gtid()
except exceptions.GadgetServerError:
# if GTIDs are not supported it is the same as having them disabled
source_has_gtids = False
try:
destination_has_gtids = destination_server.supports_gtid()
except exceptions.GadgetServerError:
# if GTIDs are not supported it is the same as having them disabled
destination_has_gtids = False
if not source_has_gtids == destination_has_gtids:
raise exceptions.GadgetError(
"Cloning pre-condition check failed: Source and destination "
"servers must have the same GTID mode.")
if destination_has_gtids:
# if destination has GTID support enabled, we must make sure
# it is empty.
gtid_executed = destination_server.get_gtid_executed()
if gtid_executed:
raise exceptions.GadgetError(
"Cloning pre-condition check failed: GTID executed set "
"must be empty on destination server.")
# Check if user has super privilege on source
# required for the set super_only
_LOGGER.debug("Checking if MySQL source user has the required "
"SUPER privilege.")
source_username = "******".format(source_server.user,
source_server.host)
source_user = User(source_server, source_username,
source_server.passwd)
if not source_user.has_privilege('*', '*', 'SUPER'):
raise exceptions.GadgetError(
"SUPER privilege is required for the MySQL user '{0}' on "
"the source server.".format(source_server.user))
# Check if user has super privilege on destination
_LOGGER.debug("Checking if MySQL destination user has the "
"required SUPER privilege.")
dest_username = "******".format(destination_server.user,
destination_server.host)
dest_user = User(destination_server, dest_username,
destination_server.passwd)
if not dest_user.has_privilege('*', '*', 'SUPER'):
raise exceptions.GadgetError(
"SUPER privilege is required for the MySQL user '{0}' on "
"the destination server.".format(destination_server.user))
# After the clone operation, mysql user table on destination server
# will be replaced by the mysql user table from source server. So we
# must make sure that:
# *) Either source or destination users exist on source server
# with a hostname that matches destination server hostname.
# If this conditions holds, then if clone operation
# finishes successfully we are sure to be able to connect to the
# destination server to do any post_clone verification.
# Otherwise we must issue a warning stating that the post_clone
# verification might fail.
if (source_server.user_host_exists(destination_server.user,
destination_server.host)
or source_server.user_host_exists(source_server.user,
destination_server.host)):
return # Condition holds, no need to issue a warning.
else:
_LOGGER.warning(
"Cloning will replace mysql user table on "
"destination server with mysql user table from "
"source. Since neither source user account "
"'%s' nor destination user account '%s' exist on "
"source server with a wildcard hostname (%%), the "
"post clone requirement check might fail because "
"the tool might not be able to successfully "
"connect to the destination server.", source_server.user,
destination_server.user)
def test_parse_grant_statement(self):
""" Tests parse_grant_statement Method.
"""
# Test function
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE, EXECUTE ON FUNCTION `util_test`.`f1` TO "
"'priv_test_user2'@'%' WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'EXECUTE', 'ALTER ROUTINE']),
None, '`util_test`', '`f1`',
"'priv_test_user2'@'%'"))
# Test procedure
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO "
"'priv_test_user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'ALTER ROUTINE']), None,
'`util_test`', '`p1`', "'priv_test_user2'@'%'"))
# Test with quoted objects
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.``export_2` TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``',
'``export_2`', "'priv_test_user'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.* TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``', '*',
"'priv_test_user'@'%'"))
# Test multiple grants with password and grant option
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT']), None,
'`mysql`', '*', "'user2'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT']), None,
'`mysql`', '*', "'user2'@'%'"))
# Test proxy privileges
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY']), "''@''", None, None,
"'root'@'localhost'"))
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON 'root'@'%' TO 'root'@'localhost' WITH GRANT "
"OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY']), "'root'@'%'", None,
None, "'root'@'localhost'"))
# Test parse grant with ansi quotes
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'",
sql_mode="ANSI_QUOTES")
self.assertEqual(parsed_statement[4], ("user2@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[2], '`mysql`')
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[3], '`user`')
self.assertRaises(GadgetError, User.parse_grant_statement,
"GRANT PROXY 'root'@'%' TO 'root'@'localhost' WITH "
"GRANT OPTION")
def test_user_has_privilege(self):
""" Tests USER.has_privilege method"""
user_name = 'Jonh_Update'
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
db_ = "mysql"
obj = "user"
access = "UPDATE"
skip_grant = False
# Test object level privilege with user with global * privilege
self.assertTrue(user_root.has_privilege(db_, obj, access, skip_grant))
# create new user to test missing privileges.
user_update = User(self.server, "{0}@{1}".format(user_name,
self.server.host))
user_update.create()
# Test privileges disabled
self.server.grants_enabled = False
skip_grant = True
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.grants_enabled = True
skip_grant = True
# Test missing privilege
self.assertFalse(user_update.has_privilege(db_, obj, access,
skip_grant))
access = "USAGE"
# Test default privilege USAGE at db level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.exec_query("GRANT UPDATE ON mysql.user TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
access = "UPDATE"
# Test privilege at object level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
# Test privilege at db level
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
user_update.drop()
def test_check_missing_privileges(self):
""" Tests the User's check_missing_privileges method"""
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_name = 'Jonh_CAPITALS'
user_obj = User(self.server, "{0}@{1}".format(user_name,
self.server.host),
verbosity=1)
self.assertFalse(user_obj.exists())
user_obj.create(disable_binlog=True, ssl=True)
self.assertTrue(user_obj.exists())
self.assertListEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"], as_string=False),
["CREATE USER", "REPLICATION SLAVE"])
self.assertEqual(
user_obj.check_missing_privileges(["REPLICATION SLAVE",
"CREATE USER"]),
"CREATE USER and REPLICATION SLAVE")
change_user_privileges(self.server, user_name, self.server.host,
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True)
self.assertListEqual(user_obj.get_grants(refresh=True),
[("GRANT REPLICATION SLAVE, CREATE USER ON *.* "
"TO 'Jonh_CAPITALS'@'localhost'",)])
self.assertListEqual(
user_obj.check_missing_privileges(["REPLICATION SLAVE",
"CREATE USER"],
as_string=False), [])
self.assertEqual(
user_obj.check_missing_privileges(["REPLICATION SLAVE",
"CREATE USER"]), "")
user_obj.drop()
user_name = 'some_user'
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
change_user_privileges(self.server, user_name, self.server.host,
user_passwd="some pass",
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True, create_user=True)
change_user_privileges(self.server, user_name, self.server.host,
revoke_list=["REPLICATION SLAVE"],
disable_binlog=True)
# expect GadgetError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
user_obj.drop(user_name)
exception = test_raises.exception
self.assertTrue("Cannot parse user@host" in exception.errmsg,
"The exception message was not the expected")
user_obj.drop("{0}@{1}".format(user_name, self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))