def __init__(self, host = "bpcweb7", db = "test", read_default_file = ""): # , read_default_file=os.path.expanduser("~/.my.cnf"), port = 3306 self.conn = None self.cursor = None self.cursorD = None self.rows = 0 self.new_id = None self.lastrowid = None port_env = 3306 try: print("host = " + str(host) + ", db = " + str(db)) print("=" * 40) read_default_file = os.path.expanduser("~/.my.cnf_node") if is_local(): host = "127.0.0.1" read_default_file = "~/.my.cnf_local" self.conn = mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port_env) self.cursor = self.conn.cursor() self.cursorD = self.conn.cursor(mysql.cursors.DictCursor) except (AttributeError, mysql.OperationalError): self.conn = mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port_env) self.cursor = self.conn.cursor() except mysql.Error: e = sys.exc_info()[1] print("Error %d: %s" % (e.args[0], e.args[1])) raise except: # catch everything print("Unexpected:") print(sys.exc_info()[0]) raise # re-throw caught exception
def __init__(self, host="vampsdev", db="test", read_default_group="client"): self.conn = None self.cursor = None self.rows = 0 self.new_id = None self.lastrowid = None try: print("=" * 40) print("host = " + str(host) + ", db = " + str(db) + ", group = " + str(read_default_group)) print("=" * 40) self.conn = mysql.connect(host=host, db=db, read_default_group=read_default_group, read_default_file="~/.my.cnf") self.cursor = self.conn.cursor() except mysql.OperationalError: e = sys.exc_info()[1] print("Error %d: %s" % (e.args[0], e.args[1])) print("=" * 40) # print("No read_default_group, use host and db. (mysql.OperationalError)") print("host = " + str(host) + ", db = " + str(db)) print("=" * 40) self.conn = mysql.connect(host=host, db=db, read_default_file="~/.my.cnf") self.cursor = self.conn.cursor() except mysql.Error: raise except: # catch everything print("Unexpected:") # handle unexpected exceptions) raise # re-throw caught exception
def check_mysql_connection(host, user, password=''): """ A function used to check the ability to login to MySQL/Mariadb :param host: ie. 'localhost' - :type String :param user: mysql user ie. 'root' - :type String :param password: mysql user's password - :type String :return: True||False """ try: mysql.connect(host=host, user=user, passwd=password) return True except mysql.Error: return False
def __init__(self, host = "vampsdev", db = "test", read_default_group = "client", read_default_file = os.path.expanduser("~/.my.cnf")): self.utils = Utils() self.conn = None self.cursor = None self.rows = 0 self.new_id = None self.lastrowid = None self.rowcount = None self.dict_cursor = None if read_default_file == "": if self.utils.is_local(): read_default_file = os.path.expanduser("~/.my.cnf_local") # print("read_default_file = %s" % read_default_file) try: self.utils.print_both("=" * 40) self.utils.print_both("host = " + str(host) + ", db = " + str(db) + ", read_default_group = " + str(read_default_group) + ", read_default_file = " + str(read_default_file)) self.utils.print_both("=" * 40) # self.conn = mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port) self.conn = mysql.connect(host = host, db = db, read_default_group = read_default_group, read_default_file = read_default_file) # print("host = %s, db = %s, read_default_file = %s" % (host, db, read_default_file)) self.cursor = self.conn.cursor() self.dict_cursor = self.conn.cursor(mysql.cursors.DictCursor) except mysql.Error as e: self.utils.print_both("Error %d: %s" % (e.args[0], e.args[1])) raise except: # catch everything self.utils.print_both("Unexpected:") self.utils.print_both(sys.exc_info()[0]) raise # re-throw caught exception
def renewMySQLconnection(self): try: if hasattr(self,'db') and self.db: self.db.close() del self.db if mysql_api == MySQLdb: self.db = mysql_api.connect(db=self.db_name, host=self.host, user=self.user, passwd=self.passwd) else: #if mysql_api == mysql.connector: self.db = mysql_api.connect(database=self.db_name, host=self.host, user=self.user, password=self.passwd) self.setAutocommit() except Exception,e: self.error('Unable to create a mysql_api connection to ' '"%s"@%s.%s: %s'%(self.user,self.host,self.db_name,str(e))) traceback.print_exc() raise Exception,e
def get_new_connection(self, conn_params): return Database.connect(**conn_params)
def mysql_secure_installation(login_password, new_password, user='******',login_host='localhost', hosts=['hostname'], change_root_password= True, remove_anonymous_user= True, disallow_root_login_remotely= False, remove_test_db= True, mysql_version_8=False): """ A function to perform the steps of mysql_secure_installation script :param login_password: Root's password to login to MySQL :param new_password: New desired Root password :type String :param user: MySQL user - default: 'root' :type String :param login_host: host to connect to - default: 'localhost' :type String :param hosts: List of hosts for the provided user i.e ['localhost', '127.0.0.1', '::1'] :type List :param change_root_password: default: True - :type Boolean :param remove_anonymous_user: default: True - :type: Boolean :param disallow_root_login_remotely: default: False - :type Boolean :param remove_test_db: default: True - :type: Boolean :param mysql_version_8: default: False - :type Boolean :return: """ if isinstance(hosts, str): hosts = hosts.split(',') info = {'change_root_pwd': None, 'hosts_failed': [], 'hosts_success': [],'remove_anonymous_user': None, 'remove_test_db': None, 'disallow_root_remotely': None } def remove_anon_user(cursor): if remove_anonymous_user: cursor.execute("select user, host from mysql.user where user='';") anon_user = cursor.fetchall() if len(anon_user) >= 1: cursor.execute('use mysql;') cursor.execute("DELETE FROM user WHERE user='';") cursor.execute("update mysql.user set plugin=null where user='******';") cursor.execute("select user, host from mysql.user where user='';") check = cursor.fetchall() if len(check) >= 1: info['remove_anonymous_user'] = 1 else: info['remove_anonymous_user'] = 0 else: info['remove_anonymous_user'] = 0 def remove_testdb(cursor): if remove_test_db: cursor.execute("show databases;") testdb = cursor.fetchall() if 'test' in list(chain.from_iterable(testdb)): # if database "test" exists in the "db's list" cursor.execute("drop database test;") cursor.execute("show databases;") # Test if the "test" db deleted check_test_db = cursor.fetchall() if 'test' in list(chain.from_iterable(check_test_db)): # return 1 if the db still exists info['remove_test_db'] = 1 else: info['remove_test_db'] = 0 else: # means "test" db does not exist info['remove_test_db'] = 0 def disallow_root_remotely(cursor): if disallow_root_login_remotely: cursor.execute("select user, host from mysql.user WHERE User='******' AND Host NOT IN ('localhost', '127.0.0.1', '::1');") remote = cursor.fetchall() if len(remote) >= 1: cursor.execute("DELETE FROM mysql.user WHERE User='******' AND Host NOT IN ('localhost', '127.0.0.1', '::1');") cursor.execute("flush privileges;") cursor.execute("select user, host from mysql.user WHERE User='******' AND Host NOT IN ('localhost', '127.0.0.1', '::1');") check_remote = cursor.fetchall() if len(check_remote) >= 1: # test info['disallow_root_remotely'] = 1 else: info['disallow_root_remotely'] = 0 else: info['disallow_root_remotely'] = 0 if check_mysql_connection(host=login_host, user=user, password=login_password): try: connection = mysql.connect(host=login_host, user=user, passwd=login_password, db='mysql') cursor = connection.cursor() remove_anon_user(cursor) remove_testdb(cursor) disallow_root_remotely(cursor) if change_root_password: pwd = {} for host in hosts: cursor.execute('use mysql;') if mysql_version_8: cursor.execute( "alter user '{}'@'{}' IDENTIFIED WITH caching_sha2_password BY '{}';".format(user, host, new_password)) else: cursor.execute('update user set password=PASSWORD("{}") where User="******" AND Host="{}";'.format(new_password, user, host)) cursor.execute('flush privileges;') if mysql_version_8: cursor.execute('select user, host, authentication_string from mysql.user where user="******";'.format(user)) else: cursor.execute('select user, host, password from mysql.user where user="******";'.format(user)) data = cursor.fetchall() for d in data: if d[1] == host: pwd['{}'.format(d[1])] = d[2] out = set(hosts).symmetric_difference(set(pwd.keys())) info['hosts_failed'] = list(out) hosts_ = list(set(hosts) - set(list(out))) for host in hosts_: if pwd[host] == pwd[login_host]: info['hosts_success'].append(host) else: info['hosts_failed'].append(login_host) if len(info['hosts_success']) >= 1: info['stdout'] = 'Password for user: {} @ Hosts: {} changed to the desired state'.format(user, info['hosts_success']) if len(info['hosts_failed']) >= 1: info['change_root_pwd'] = 1 # info['stderr'] = 'Could NOT change password for User: {} @ Hosts: {}'.format(user,info['hosts_failed']) else: info['change_root_pwd'] = 0 connection.close() except mysql.Error as e: info['change_root_pwd'] = 1 info['stderr'] = e elif check_mysql_connection(host=login_host, user=user, password=new_password): connection = mysql.connect(host=login_host, user=user, passwd=new_password, db='mysql') cursor_ = connection.cursor() remove_anon_user(cursor_) remove_testdb(cursor_) disallow_root_remotely(cursor_) info['change_root_pwd'] = 0 info['stdout'] = 'Password of {}@{} Already meets the desired state'.format(user, login_host) else: info['change_root_pwd'] = 1 info['stdout'] = 'Neither the provided old passwd nor the new passwd are correct' return info
def connect(host, db, read_default_file, port_env): return mysql.connect(host = host, db = db, read_default_file = read_default_file, port = port_env)
elif args.dbhost == 'vampsdev' or args.dbhost == 'bpcweb7': args.json_file_path = '/groups/vampsweb/vampsdev/nodejs/json' args.NODE_DATABASE = 'vamps2' hostname = 'bpcweb7' else: args.json_file_path = os.path.join(args.process_dir,'public','json') args.NODE_DATABASE = args.db hostname = 'localhost' if args.infile_type == 'json': if os.path.isdir(args.json_file_path): print('json_file_path VALIDATED') else: print('json_file_path DID NOT VALIDATE:', args.json_file_path) sys.exit('Exiting') db = mysql.connect(db = args.NODE_DATABASE, host=hostname, read_default_file=os.path.expanduser("~/.my.cnf_node") ) cur = db.cursor() cur.execute("SHOW databases") dbs = [] db_str = '' i = 0 if args.NODE_DATABASE: NODE_DATABASE = args.NODE_DATABASE else: for row in cur.fetchall(): if row[0] != 'mysql' and row[0] != 'information_schema': dbs.append(row[0]) db_str += str(i)+'-'+row[0]+'; ' print(str(i)+' - '+row[0]+'; ') i += 1