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 _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 get_akrr_db(self, su=False, dbname=""):
return get_con_to_db(
self.akrr_db_user_name if not su else self.akrr_db_su_user_name,
self.akrr_db_user_password
if not su else self.akrr_db_su_user_password, self.akrr_db_host,
self.akrr_db_port, self.akrr_db_name if dbname == "" else dbname)
def _check_and_read_su_credentials_for_dbserver(user: Optional[str],
password: Optional[str],
host: str,
port: int) -> Tuple[str, str]:
"""
Ask for administrative credential on db server
if user and password is provided check them first
Returns: user, password
"""
if not user or not password:
try:
get_con_to_db(user, password, host, port)
return user, password
except Exception:
return _read_sql_su_credentials(host, port)
else:
return _read_sql_su_credentials(host, port)
def _read_sql_su_credentials(host, port):
while True:
log.log_input(
"Please provide an administrative database user (for {}:{}) "
"under which the installation sql script should "
"run (This user must have privileges to create "
"users and databases).".format(host, port))
su_username = input("Username: "******"Please provide the password for the the user which you previously entered:")
su_password = getpass.getpass()
try:
get_con_to_db(su_username, su_password, host, port)
return su_username, su_password
except Exception as e:
log.error("MySQL error: " + str(e))
log.error("Entered credential is not valid. Please try again.")
def get_xd_db(self, su=False, dbname: Optional[str] = ""):
"""
get connector and cursor to XDMoD's modw DB
"""
return get_con_to_db(
self.xd_db_user_name if not su else self.xd_db_su_user_name,
self.xd_db_user_password
if not su else self.xd_db_su_user_password, self.xd_db_host,
self.xd_db_port, self.xd_db_name if dbname == "" else dbname)
def get_ak_db(self, su=False, dbname: Optional[str] = ""):
"""
get connector and cursor to mod_appkernel DB
"""
return get_con_to_db(
self.ak_db_user_name if not su else self.ak_db_su_user_name,
self.ak_db_user_password
if not su else self.ak_db_su_user_password, self.ak_db_host,
self.ak_db_port, self.ak_db_name if dbname == "" else dbname)
def get_xd_db(su=False, dict_cursor=True):
"""get access to db xdmod.modw, su - superuser"""
from . import cfg
con, cur = get_con_to_db(
host=cfg.xd_db_host,
port=cfg.xd_db_port,
user=cfg.xd_db_user if not su else cfg.sql_root_name,
password=cfg.xd_db_passwd if not su else cfg.sql_root_password,
db_name=cfg.xd_db_name if not su else None,
dict_cursor=dict_cursor)
return con, cur
def get_akrr_db(su=False, dict_cursor=True):
"""get access to db mod_akrr, su - superuser"""
from . import cfg
# def get_con_to_db(user,password,host='localhost',port=3306, db_name=None, dictCursor=True):
con, cur = get_con_to_db(
user=cfg.akrr_db_user if not su else cfg.sql_root_name,
password=cfg.akrr_db_passwd if not su else cfg.sql_root_password,
host=cfg.akrr_db_host,
port=cfg.akrr_db_port,
db_name=cfg.akrr_db_name if not su else None,
dict_cursor=dict_cursor)
return con, cur
def get_akrr_db(dict_cursor=False):
"""
Get connector and cursor to mod_akrr database
"""
from akrr.cfg import akrr_db_host, akrr_db_port, akrr_db_user, akrr_db_passwd, akrr_db_name
return get_con_to_db(user=akrr_db_user,
password=akrr_db_passwd,
host=akrr_db_host,
port=akrr_db_port,
db_name=akrr_db_name,
dict_cursor=dict_cursor)
def get_xd_db(dict_cursor=False):
"""
Get connector and cursor to modw database
"""
from akrr.cfg import xd_db_host, xd_db_port, xd_db_user, xd_db_passwd, xd_db_name
if xd_db_host is None:
return None, None
return get_con_to_db(user=xd_db_user,
password=xd_db_passwd,
host=xd_db_host,
port=xd_db_port,
db_name=xd_db_name,
dict_cursor=dict_cursor)
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()
