def _create_db_user_gran_priv_if_needed(con_fun, user, password,
db, priv, create):
"""
Helping function to create db and user
"""
if create:
log.info("Creating %s and user to access it" % (db, ))
else:
log.info("Setting user to access %s" % (db, ))
m_su_con, m_su_cur = con_fun(True, None)
client_host = get_db_client_host(m_su_cur)
if create:
_cursor_execute(
m_su_cur,
"CREATE DATABASE IF NOT EXISTS %s CHARACTER SET utf8" %
(cv(db), ))
create_user_if_not_exists(m_su_con,
m_su_cur,
user,
password,
client_host,
dry_run=akrr.dry_run)
_cursor_execute(
m_su_cur,
"GRANT " + cv(priv) + " ON " + cv(db) + ".* TO %s@%s",
(user, client_host))
m_su_con.commit()
def _check_user_db_priv_on_dbserver(user: str, password: str, host: str, port: int, db_name: str, priv: str) \
-> Tuple[bool, bool, bool]:
"""
Check if user and database already exists and privileges are ok
Returns: user_exists, db_exists, user_rights_are_correct
"""
# check if user, db already there
try:
# connect with provided user, Exception will raise if user can not connect
_, cur = get_con_to_db(user, password, host, port)
client_host = get_db_client_host(cur)
user_exists = True
db_exists = db_exist(cur, db_name)
if not db_exists:
log.debug("Database %s doesn't exists on %s", db_name, host)
user_rights_are_correct = db_check_priv(cur, db_name, priv, user,
client_host)
if not user_rights_are_correct:
log.debug(
"User %s doesn't have right privilege on %s, should be %s",
user, db_name, priv)
except MySQLdb.Error:
user_exists = False
db_exists = False
user_rights_are_correct = False
log.debug("User (%s) does not exists on %s", user, host)
return user_exists, db_exists, user_rights_are_correct
def _create_db_user_gran_priv_if_needed(con_fun, user, password,
db, priv):
log.info("Creating %s and user to access it if needed" %
(db, ))
su_con, su_cur = con_fun(True, None)
client_host = get_db_client_host(su_cur)
_cursor_execute(
su_cur, "CREATE DATABASE IF NOT EXISTS %s" % (cv(db), ))
su_cur.execute(
"SELECT * FROM mysql.user WHERE User=%s AND Host=%s",
(user, client_host))
if len(su_cur.fetchall()) == 0:
# Older version of MySQL do not support CREATE USER IF NOT EXISTS
# so need to do checking
_cursor_execute(su_cur,
"CREATE USER %s@%s IDENTIFIED BY %s",
(user, client_host, password))
_cursor_execute(
su_cur,
"GRANT " + cv(priv) + " ON " + cv(db) + ".* TO %s@%s",
(user, client_host))
su_con.commit()
def test_db_check_priv(self):
from akrr.util.sql import get_user_password_host_port
from akrr.util.sql import get_db_client_host
from akrr.util.sql import get_con_to_db
from akrr.util.sql import db_check_priv
from akrr.util.sql import cv
from akrr.util.sql import create_user_if_not_exists
su_user, \
su_password, \
db_host, \
db_port = get_user_password_host_port(self.su_sql)
su_con, su_cur = get_con_to_db(su_user, su_password, db_host, db_port)
client_host = get_db_client_host(su_cur)
# create user
create_user_if_not_exists(su_con, su_cur, self.user1, self.password1,
client_host)
# check su rights
self.assertEqual(db_check_priv(su_cur, "mysql", "ALL"), True)
self.assertEqual(db_check_priv(su_cur, "dontexists", "ALL"), True)
self.assertEqual(db_check_priv(su_cur, "mysql", "ALL", self.user1),
False)
self.assertEqual(
db_check_priv(su_cur, "dontexists", "ALL", self.user1), False)
self.assertEqual(
db_check_priv(su_cur, "mysql", "ALL", self.user1, client_host),
False)
self.assertEqual(
db_check_priv(su_cur, "dontexists", "ALL", self.user1,
client_host), False)
# connect as user
_, cur = get_con_to_db(self.user1, self.password1, "localhost")
self.assertEqual(
db_check_priv(su_cur, "dontexists", "ALL", self.user1), False)
# create db and give permission to user1
su_cur.execute("CREATE DATABASE IF NOT EXISTS %s CHARACTER SET utf8" %
(cv(self.db1), ))
su_cur.execute("CREATE DATABASE IF NOT EXISTS %s CHARACTER SET utf8" %
(cv(self.db2), ))
su_con.commit()
su_cur.execute("GRANT ALL ON " + cv(self.db1) + ".* TO %s@%s",
(self.user1, client_host))
su_cur.execute("GRANT SELECT ON " + cv(self.db2) + ".* TO %s@%s",
(self.user1, client_host))
su_con.commit()
# check rights as current regular user
self.assertEqual(db_check_priv(cur, "mysql", "ALL"), False)
self.assertEqual(db_check_priv(cur, self.db1, "ALL"), True)
self.assertEqual(db_check_priv(cur, self.db1, "SELECT"), True)
self.assertEqual(db_check_priv(cur, self.db2, "ALL"), False)
self.assertEqual(db_check_priv(cur, self.db2, "SELECT"), True)
def _clean_db(self):
from akrr.util.sql import get_db_client_host
from akrr.util.sql import get_user_password_host_port
from akrr.util.sql import get_con_to_db
from akrr.util.sql import cv
su_user,\
su_password,\
db_host,\
db_port=get_user_password_host_port(self.su_sql)
_, su_cur = get_con_to_db(su_user, su_password, db_host, db_port)
client_host = get_db_client_host(su_cur)
#remove db
su_cur.execute("DROP DATABASE IF EXISTS %s" % (cv(self.db1), ))
su_cur.execute("DROP DATABASE IF EXISTS %s" % (cv(self.db2), ))
#remove user
su_cur.execute("DROP USER IF EXISTS %s@%s", (self.user1, client_host))
def read_db_user_credentials(self):
###
# mod_akrr
log.info(
"Before Installation continues we need to setup the database.")
self.akrr_db_user_name, self.akrr_db_user_password = _read_username_password(
"Please specify a database user to access mod_akrr database (Used by AKRR)"
"(This user will be created if it does not already exist):",
self.akrr_db_user_name, self.akrr_db_user_password,
self.default_akrr_user)
log.empty_line()
# check if user, db already there
user_exists = False
db_exists = False
user_rights_are_correct = False
try:
# connect with provided user, Exception will raise if user can not connect
_, cur = get_con_to_db(self.akrr_db_user_name,
self.akrr_db_user_password,
self.akrr_db_host, self.akrr_db_port)
client_host = get_db_client_host(cur)
user_exists = True
db_exists = db_exist(cur, self.akrr_db_name)
if not db_exists:
log.debug("Database {} doesn't exists on {}".format(
self.akrr_db_name, self.akrr_db_host))
user_rights_are_correct = db_check_priv(cur, self.akrr_db_name,
"ALL",
self.akrr_db_user_name,
client_host)
if not user_rights_are_correct:
log.debug(
"User {} doesn't have right privilege on {}, should be ALL"
.format(self.akrr_db_user_name, self.akrr_db_name))
except MySQLdb.Error:
user_exists = False
log.debug("User ({}) does not exists on {}".format(
self.akrr_db_user_name, self.akrr_db_host))
# ask for su user on this machine if needed
if not user_exists or not db_exists or not user_rights_are_correct:
self.akrr_db_su_user_name, \
self.akrr_db_su_user_password = _read_sql_su_credentials(self.akrr_db_host, self.akrr_db_port)
log.empty_line()
###
# mod_appkernel
same_host_as_ak = self.ak_db_host == self.akrr_db_host and self.ak_db_port == self.akrr_db_port
self.ak_db_user_name, self.ak_db_user_password = _read_username_password(
"Please specify a database user to access mod_appkernel database "
"(Used by XDMoD appkernel module, AKRR creates and syncronize resource and appkernel description)"
"(This user will be created if it does not already exist):",
self.ak_db_user_name, self.ak_db_user_password,
self.akrr_db_user_name,
self.akrr_db_user_password if same_host_as_ak else None)
log.empty_line()
# ask for su user on this machine
user_exists = False
db_exists = False
user_rights_are_correct = False
try:
_, cur = get_con_to_db(self.ak_db_user_name,
self.ak_db_user_password, self.ak_db_host,
self.ak_db_port)
client_host = get_db_client_host(cur)
user_exists = True
db_exists = db_exist(cur, self.ak_db_name)
if not db_exists:
log.debug("Database {} doesn't exists on {}".format(
self.ak_db_name, self.ak_db_host))
user_rights_are_correct = db_check_priv(cur, self.ak_db_name,
"ALL",
self.ak_db_user_name,
client_host)
if not user_rights_are_correct:
log.debug(
"User {} doesn't have right privelege on {}, should be ALL"
.format(self.ak_db_user_name, self.ak_db_name))
except Exception as e:
user_exists = False
log.debug("User ({}) does not exists on {}".format(
self.akrr_db_user_name, self.akrr_db_host))
if not user_exists or not db_exists or not user_rights_are_correct:
self.ak_db_su_user_name = self.akrr_db_su_user_name
self.ak_db_su_user_password = self.akrr_db_su_user_password
try:
get_con_to_db(self.ak_db_su_user_name,
self.ak_db_su_user_password, self.ak_db_host,
self.ak_db_port)
except:
self.ak_db_su_user_name, \
self.ak_db_su_user_password = _read_sql_su_credentials(self.ak_db_host, self.ak_db_port)
log.empty_line()
##
# modw
same_host_as_xd = self.xd_db_host == self.ak_db_host and self.xd_db_port == self.ak_db_port
self.xd_db_user_name, \
self.xd_db_user_password = _read_username_password(
"Please specify the user that will be connecting to the XDMoD database (modw):",
self.xd_db_user_name,
self.xd_db_user_password,
self.ak_db_user_name,
self.ak_db_user_password if same_host_as_xd else None
)
log.empty_line()
# ask for su user on this machine
user_exists = False
db_exists = False
user_rights_are_correct = False
try:
_, cur = get_con_to_db(self.xd_db_user_name,
self.xd_db_user_password, self.xd_db_host,
self.xd_db_port)
client_host = get_db_client_host(cur)
user_exists = True
db_exists = db_exist(cur, "modw")
if not db_exists:
log.debug("Database {} doesn't exists on {}".format(
self.xd_db_name, self.xd_db_host))
user_rights_are_correct = db_check_priv(cur, self.xd_db_name,
"SELECT",
self.xd_db_user_name,
client_host)
if not user_rights_are_correct:
log.debug(
"User {} doesn't have right privelege on {}, should be at least SELECT"
.format(self.xd_db_user_name, self.xd_db_name))
except Exception as e:
user_exists = False
log.debug("User ({}) does not exists on {}".format(
self.xd_db_user_name, self.xd_db_host))
if not user_exists or not db_exists or not user_rights_are_correct:
self.xd_db_su_user_name = self.ak_db_su_user_name
self.xd_db_su_user_password = self.ak_db_su_user_password
try:
get_con_to_db(self.xd_db_su_user_name,
self.xd_db_su_user_password, self.xd_db_host,
self.xd_db_port)
except:
self.ak_db_su_user_name, \
self.ak_db_su_user_password = _read_sql_su_credentials(self.xd_db_host, self.xd_db_port)
log.empty_line()
