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 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 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 su_cur.execute("CREATE USER IF NOT EXISTS %s@%s IDENTIFIED BY %s", (self.user1, client_host, self.password1)) #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" % (cv(self.db1), )) su_cur.execute("CREATE DATABASE IF NOT EXISTS %s" % (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 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()