def insert_data(username, password, temp): """ 把数据插入到表client_info中 :param username: :param password: :param temp: :return: """ # 连接本地mysql数据库octastack_fuzhou conn, cursor = gc.connect_localhost() # 将要插入数据库client_info表中的数据组织成json格式 insert_row = { "client_info": [{ "db_name": temp['name'], "db_type": temp['type'], "db_ip": temp['ip'], "db_port": temp['port'], "sql_uname": username, "sql_passwd": password, "db_size": temp['size'], 'db_volume': temp['volume'] }] } # 插入数据 mysql_base_api.insert_row(conn, cursor, insert_row) # 关闭连接 mysql_base_api.sql_close(conn, cursor)
def api_test(): conn, cursor = mysql_base_api.sql_init(gc.TEST_IP3, "finger", "finger", None) print("Create Database: api_test") print(uniform_test(conn, cursor, '1.json')) mysql_base_api.sql_close(conn, cursor) conn, cursor = mysql_base_api.sql_init(gc.TEST_IP3, "finger", "finger", "base_api_test") print("Create Tables: ") print(uniform_test(conn, cursor, '3.json')) print("Insert data with foreignkey") print(uniform_test(conn, cursor, '6.json')) print("Select") print(uniform_test(conn, cursor, '9.json')) print("Insert data without foreignkey") print(uniform_test(conn, cursor, '5.json')) print("Select") print(uniform_test(conn, cursor, '9.json')) print("Insert data without commit") print(uniform_test(conn, cursor, '16.json')) conn.commit() mysql_base_api.sql_close(conn, cursor)
def clam_scan(self, invoke_ip, conf_str): """ 执行病毒扫描的线程 :param invoke_ip: :return: """ global SCAN_KILLED try: # 更新数据扫描标记位 # MachineList.objects.filter(hostip=invoke_ip).update(is_scan=True) # 开始扫描 result = clamav.clam_scan(invoke_ip, conf_str) if len(result) is not 0 and not SCAN_KILLED: # 将扫描日志和标记位更新 conn, cursor = mysql_base_api.sql_init(LOCAL_CONFIG.mysql_host, LOCAL_CONFIG.mysql_user, LOCAL_CONFIG.mysql_pass, LOCAL_CONFIG.mysql_database, LOCAL_CONFIG.mysql_port) # 初始化数据库 # 从数据库中查询 sql = 'update app_fuzhou_machinelist set is_scan=0,scan_log="%s" where hostip="%s"' \ % (result, invoke_ip) mysql_base_api.sql_execute(conn, cursor, sql, "") # 关闭数据库 mysql_base_api.sql_close(conn, cursor) # MachineList.objects.filter(hostip=invoke_ip).update( # scan_log=str(result), is_scan=False) except Exception as e: logger.error(e) finally: try: MachineList.objects.filter(hostip=invoke_ip).update(is_scan=False) except Exception as e: logger.error(e)
def set_clamav_config(host_ip, config): """ 保存clamascan配置的参数 :param config: 配置参数 :return: 是否保存成功 """ if host_ip is None or config is None: return False execute_now(config) # 若相应主机IP的记录存在则更新,否则更新 sql = "INSERT INTO system_config(host_ip, type, config) VALUES(%s, %s, %s) " \ "ON DUPLICATE KEY UPDATE config=%s" conn = None cursor = None try: conn, cursor = mysql_base_api.sql_init(CONFIG.mysql_host, CONFIG.mysql_user, CONFIG.mysql_pass, CONFIG.mysql_database) config_str = json.dumps(config) mysql_base_api.sql_execute(conn, cursor, sql, [host_ip, TYPE, config_str, config_str]) return True except Exception as e: logger.error(e) finally: if conn is not None and cursor is not None: mysql_base_api.sql_close(conn, cursor) return False
def get_scan_config(host_ip): """ 获取制定主机的扫描配置 :param host_ip: 主机IP, :return: 配置信息 """ conn = None cursor = None default = "{}" try: conn, cursor = mysql_base_api.sql_init(CONFIG.mysql_host, CONFIG.mysql_user, CONFIG.mysql_pass, CONFIG.mysql_database) sql = "SELECT config FROM system_config WHERE host_ip=%s and type=%s" result = mysql_base_api.select_one_row(cursor, sql, [host_ip, TYPE]) if result is None: return default return result.get('config', default) except Exception as e: logger.error(e) finally: if conn is not None and cursor is not None: mysql_base_api.sql_close(conn, cursor)
def audit_add_old(request): """ 添加数据库审计用户或者行为 该方案暂用,见新 audit_add 方法 :param request:req :return:json """ # return_dict = {} return_dict = {"code": "201"} # 设置默认值为"201",表示本次操作失败 _type = request.POST.get("type") ip = request.POST.get("ip") value = request.POST.get("value") db_type = request.POST.get("db_type").lower() # 目前,前端传过来的是 空字符串 if db_type == "": # 设置,mysql为默认数据库类型 db_type = "mysql" if _type == "2" and value not in ACTION_LIST: # 如果不合法,直接返回 return HttpResponse(json.dumps({'code': '202'})) conn, cursor = mysql_base_api.sql_init(jc.mysql_host, jc.mysql_user, jc.mysql_pass, jc.mysql_database, jc.mysql_port) sql = "select count(id) as count from white_user_action WHERE type ='" \ + _type + "' and ip = '" + ip + "' and value = '" + value + "' and db_type = '" + db_type + "'" # 当添加的已经存在,返回给前端300值 # 查询之指定的白名单用户或行为,是否已经添加 result = mysql_base_api.select_onesql(cursor, sql) # result= ({'count': 1},) if result[0]['count'] > 0: # 如果>0,则表明相应的白名单用户或行为已经添加 # mysql_base_api.sql_close(conn, cursor) return_dict['code'] = "300" # 已经存在,用户白名单添加重复 return HttpResponse(json.dumps(return_dict)) try: if db_type == 0: # 0 mysql audit_rpc = AuditRpcClient(ip, AUDIT_RPC_PORT) rpc_result = audit_rpc.add(value, _type) logger.debug(rpc_result) if rpc_result == 0: return_dict['code'] = "200" # insert_action_name = {'white_user_action': [ # {"value": value, "ip": ip, 'type': _type, 'db_type': db_type}]} # mysql_base_api.insert_row(conn, cursor, insert_action_name) # mysql_base_api.sql_close(conn, cursor) else: return_dict['code'] = "201" elif db_type == 1: # 1 postgresql insert_action_name = {'white_user_action': [ {"value": value, "ip": ip, 'type': _type, 'db_type': db_type}]} mysql_base_api.insert_row(conn, cursor, insert_action_name) mysql_base_api.sql_close(conn, cursor) return_dict['code'] = "200" except Exception as e: logger.error(e) return_dict['code'] = "201" mysql_base_api.sql_close(conn, cursor) return HttpResponse(json.dumps(return_dict)) return HttpResponse(json.dumps(return_dict))
def while_result(request): """ 前台不断想后台轮训访问的api,用于判断用户白名单,行为添加删除和清空操作后台是否已经完成 如,当前台添加用户白名单后,前台每隔1s会询问后台while_result, while_result返回给前台250说明成功,否则失败 """ user_type = request.POST["type"] deal_uer_content = request.POST["whiteNameIP"] shenji_ip = request.POST["shenji_ip"] if user_type == "1": # user_type值为1,表示用户白名单的结果轮训 deal_uer_content = eval(deal_uer_content) my_content = deal_uer_content['IPcontent'] elif user_type == "3": # 当user_type为3,表示用户行为的结果轮训 deal_uer_content = eval(deal_uer_content) my_content = deal_uer_content['actionContent'] else: my_content = deal_uer_content # 后端处理结果查询 deal_result = 0 _dict = {} if deal_result == 0: # 获取数据库操作信息, conn, cursor = mysql_base_api.sql_init(jc.mysql_host, jc.mysql_user, jc.mysql_pass, jc.mysql_database, jc.mysql_port) if user_type == "1": # 删除数据库中的用户白名单,删除的是单条白名单 del_sql = ["delete from whiteuser where user_name='" + my_content + "' and user_ip = '" + shenji_ip + "'"] mysql_base_api.update_row(conn, cursor, del_sql) elif user_type == "2": # 清空特定ip服务器下的,用户白名单 del_sql = ["delete from whiteuser where user_ip='" + shenji_ip + "'"] mysql_base_api.update_row(conn, cursor, del_sql) elif user_type == "3": # 删除数据库中的用户行为白名单,删除的是单条行为 del_sql = ["delete from whiteaction where action_name='" + my_content + "' and action_ip = '" + shenji_ip + "'"] mysql_base_api.update_row(conn, cursor, del_sql) elif user_type == "4": # 清空特定ip服务器下的用户白名单行为 del_sql = ["delete from whiteaction where action_ip='" + shenji_ip + "'"] mysql_base_api.update_row(conn, cursor, del_sql) elif user_type == "5": # 后台添加用户白名单成功,现在把这个用户白名单添加到数据库中 insert_user_name = {'whiteuser': [{"user_name": my_content, "user_ip": shenji_ip}]} mysql_base_api.insert_row(conn, cursor, insert_user_name) elif user_type == "6": # 后台添加用户行为成功,现在把这个行为添加到数据库中 insert_action_name = {'whiteaction': [{"action_name": my_content, "action_ip": shenji_ip}]} mysql_base_api.insert_row(conn, cursor, insert_action_name) _dict['code'] = "250" # 关闭数据库 mysql_base_api.sql_close(conn, cursor) else: _dict['code'] = "251" return HttpResponse(json.dumps(_dict))
def _init_tables(self): """ 初始化表格 octa_waf_host_status: 初次使用此模块的时候,安装zmq client的客户端主机,配置在配置文件中, 需要同步到数据库中 初始化表格 octa_global_setting: 默认全部都是打开状态 :return: """ conn, cursor = mysql_base_api.sql_init(LOCAL_CONFIG.mysql_host, LOCAL_CONFIG.mysql_user, LOCAL_CONFIG.mysql_pass, LOCAL_CONFIG.mysql_database, LOCAL_CONFIG.mysql_port) hosts = LOCAL_CONFIG.client_audit_hosts for host in hosts: _ip = host['ip'] _name = host['name'] sql = "select count(id) as count from octa_waf_host_status WHERE ip = %s " count = mysql_base_api.sql_execute(conn, cursor, sql, [_ip]) _int_count = count[0]['count'] if _int_count == 0: mysql_base_api.sql_execute( conn, cursor, "insert into octa_waf_host_status(ip, name, http, web, " "dataTrack, errorCheck, dos, whole) VALUES (%s, %s, 'on', " "'on', 'on', 'on', 'on', 'on')", [_ip, _name]) logger.debug( "the ip :%s ,the name %s insert to table: octa_waf_host_status successfully", _ip, _name) waf_rule_param_keys = [ 'http', 'web', 'dataTrack', 'errorCheck', 'dos', 'all' ] for key in waf_rule_param_keys: count = mysql_base_api.sql_execute( conn, cursor, "select count(id) as count from octa_global_setting WHERE param = %s", [key]) _int_count = count[0]['count'] if _int_count == 0: mysql_base_api.sql_execute( conn, cursor, "insert into octa_global_setting (param,value) VALUES (%s,'on')", [key]) logger.debug( "the key :%s not exist, insert to table:octa_global_setting successfully", key) # 关闭数据库 mysql_base_api.sql_close(conn, cursor)
def insert_alarm(error_count, error_type): """ 向alarm表中插入错误信息,前端会定时查询该表,展示报警 :param error_count: :param error_type: :return: """ conn, cursor = get_conn_cursor() try: # 将错误信息保存到数据库 前端定时查询数据库 datetime_now = datetime.datetime.now() sql = "INSERT INTO app_fuzhou_alarm (create_time, alarm_type, count) VALUES (%s, %s, %s)" mysql_base_api.sql_execute(conn, cursor, sql, (datetime_now, error_type, error_count)) except Exception as e: logger.error(e) finally: mysql_base_api.sql_close(conn, cursor)
def get_trustlog_errors(): """ 查询数据库中trustlog表的行数 每一行代表一条错误信息 :return: """ conn, cursor = get_conn_cursor() result = () try: time_delta = jc.alarm_second beginning = datetime.datetime.now() - datetime.timedelta(seconds=time_delta) # 计算时间点 sql = "SELECT * FROM app_fuzhou_trustlog WHERE state = 0 and time > '{start_time}' ORDER BY time DESC" result = mysql_base_api.sql_execute(conn, cursor, sql.format(start_time=beginning.strftime(format="%Y/%m/%d %H:%M:%S")), ()) except Exception as e: logger.error(e) finally: mysql_base_api.sql_close(conn, cursor) return result
def insert_alarm_detail(ip, hostname, alarm_type, create_time, info): """ 插入告警详情 :param ip:机器ip :param hostname:主机名 :param alarm_type:类型 :param create_time:时间 :param info:详情 :return: """ conn, cursor = get_conn_cursor() try: # 将错误信息保存到数据库 前端定时查询数据库 sql = "INSERT INTO app_fuzhou_alarm_detail (ip,hostname,info,alarm_type,create_time) " \ "VALUES (%s, %s, %s,%s,%s)" mysql_base_api.sql_execute(conn, cursor, sql, (ip, hostname, info, alarm_type, create_time)) except Exception as e: logger.error(e) finally: mysql_base_api.sql_close(conn, cursor)
def mysql_insert_alarm(count, error_type): """ 将错误信息保存到数据库 前端定时查询数据库 :param count: :param error_type: :return: """ try: datetime_now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') conn, cursor = mysql_base_api.sql_init( db_host=LOCAL_CONFIG.mysql_host, db_port=LOCAL_CONFIG.mysql_port, db_user=LOCAL_CONFIG.mysql_user, db_passwd=LOCAL_CONFIG.mysql_pass, db_name=LOCAL_CONFIG.mysql_database) sql = "INSERT INTO app_fuzhou_alarm (create_time, alarm_type, count) VALUES (%s, %s, %s)" mysql_base_api.sql_execute(conn, cursor, sql, (datetime_now, error_type, count)) mysql_base_api.sql_close(conn, cursor) except Exception as e: logger.error(e)
def query_mail_to_list(): """ 从数据库中查询收件人 :return: """ mail_to = [] conn, cursor = mysql_base_api.sql_init(CONF.mysql_host, CONF.mysql_user, CONF.mysql_pass, CONF.mysql_database, CONF.mysql_port) conn_list = mysql_base_api.sql_execute( conn, cursor, "select email from app_fuzhou_warninglist where enabled = 1", "") # 关闭数据库 mysql_base_api.sql_close(conn, cursor) if len(conn_list) == 0: return ["*****@*****.**"] for item in conn_list: mail_to.append(item['email']) return mail_to
def _save_2_bdp_mysql(log_results): logger.debug("----") if not log_results: return results = {} for host in HOST_LIST: # 初始化每台机器对应的拆分后的日志结合结果 results[host['ip']] = [] for log in log_results: for host in HOST_LIST: _re_rex = re.compile(host['ip']) try: if _re_rex.findall(str(log)): results[host['ip']].append(log) continue except Exception as e: logger.debug(log) logger.error(e) continue for host in HOST_LIST: # 初始化每台机器对应的拆分后的日志结合结果 tx_id, private_key, public_key = exec_write(str(results[host['ip']]), None, LOCAL_CONFIG.bdb_host, LOCAL_CONFIG.bdb_port, False) logger.debug(tx_id) # 获取数据库中的数据,用户白名单 和用户行为值 conn, cursor = mysql_base_api.sql_init(LOCAL_CONFIG.mysql_host, LOCAL_CONFIG.mysql_user, LOCAL_CONFIG.mysql_pass, LOCAL_CONFIG.mysql_database) insert_list = {'bdb_whitelist': [ {"tx_id": tx_id, "private_key": private_key, "host": host['ip'], "create_time": datetime.now()}]} mysql_base_api.insert_row(conn, cursor, insert_list) mysql_base_api.sql_close(conn, cursor) logger.debug(insert_list)
def update_db(request): response = Response() if request.method != 'POST': # 非post访问 response.setcode('WRONG_CONNECTION') ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) dbname = request.POST['dbname'] new_dbname = request.POST['newDBname'] sql_type = request.POST['type'] ip = request.POST['ip'] port = request.POST['port'] user = request.POST['username'] cur_password = request.POST['curPassword'] new_password = request.POST['newPassword'] if sql_type == 'MySQL': if port == "": port = 3306 conn, cursor = mysql_base_api.sql_init(ip, user, cur_password, None, port) else: conn, cursor = mysql_base_api.sql_init(ip, user, cur_password, None, int(port)) if str(type(conn)).find('int') != -1: response.setcode('WRONG_CONNECTION') ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) # 如果数据库名有变更,则修改数据库名 if dbname != new_dbname: # 首先创建目标数据库 r = mysql_base_api.create_database(conn, cursor, new_dbname) # 创建新的数据库 if len(r) > 0 and r[0] == 1007: ret = [BIZ_CODE['DATABASE_EXIST']] return HttpResponse(json.dumps(ret)) elif str(type(r)).find('tuple'): # 获取所有源库的表名 mysql_base_api.sql_execute(conn, cursor, "use information_schema;", "") tables = mysql_base_api.sql_execute( conn, cursor, "select table_name from TABLES where " "TABLE_SCHEMA='" + dbname + "';", "") # 按照以下命令一个个修改原数据库表名 if len(tables) > 0: for table in tables: table_name = table['table_name'] mysql_base_api.sql_execute( conn, cursor, "rename table " + dbname + "." + table_name + " to " + new_dbname + "." + table_name + ";", "") mysql_base_api.drop_database(cursor, dbname) # 删除原来的数据库 # 获取新数据库的size和volume mysql_base_api.sql_execute(conn, cursor, "use information_schema;", "") volume = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as data from TABLES", "") size = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as " "data from TABLES where table_schema='" + new_dbname + "';", "") if size[0]['data'] is None: size[0]['data'] = 0 # 更新client_info表 conn, cursor = mysql_base_api.sql_init(jc.mysql_host, jc.mysql_user, jc.mysql_pass, None, jc.mysql_port) mysql_base_api.sql_execute(conn, cursor, "use octastack_fuzhou;", "") update_row = [ "UPDATE `client_info` SET db_name='" + new_dbname + "', db_size = " + str(size[0]['data']) + ", db_volume = " + str(volume[0]['data']) + " where db_name='" + dbname + "'" + " and db_ip='" + ip + "'" ] mysql_base_api.update_row(conn, cursor, update_row) else: ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) # 如果数据库用户密码有变更,则修改密码 if new_password != "": # 同时修改密码 logger.debug( "---------------- test change password ---------------" + new_password) mysql_base_api.sql_execute(conn, cursor, "use mysql;", None) mysql_base_api.sql_execute( conn, cursor, "UPDATE user SET Password = "******"PASSWORD(" + new_password + ") WHERE user = '******';", None) mysql_base_api.sql_execute(conn, cursor, "FLUSH PRIVILEGES;", None) logger.debug( "-------------------- success changed password-----------------" ) # 获取数据库的size和volume mysql_base_api.sql_execute(conn, cursor, "use information_schema;", None) volume = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as data from TABLES", "") size = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as " "data from TABLES where table_schema='" + dbname + "';", "") if size[0]['data'] is None: size[0]['data'] = 0 ret = [] mysql_base_api.sql_close(conn, cursor) temp = { 'name': new_dbname, 'type': sql_type, 'ip': ip, 'port': port, 'size': float(size[0]['data']), 'volume': float(volume[0]['data']) } ret.append(temp) response.setcode('SUCCESS') ret.append(BIZ_CODE['SUCCESS']) return HttpResponse(json.dumps(ret)) elif sql_type == 'PostgreSQL': conn = psycopg2.connect(user=user, password=cur_password, host=ip, port=port) conn.set_isolation_level(0) cur = conn.cursor() if dbname != new_dbname: cur.execute("ALTER DATABASE " + dbname + " RENAME TO " + new_dbname + ";") conn.set_isolation_level(1) cur.close() conn.close() # 更新client_info表 mysql_conn, mysql_cursor = mysql_base_api.sql_init( jc.mysql_host, jc.mysql_user, jc.mysql_pass, None, jc.mysql_port) mysql_base_api.sql_execute(mysql_conn, mysql_cursor, "use octastack_fuzhou;", "") update_row = [ "UPDATE `client_info` SET db_name='" + new_dbname + "', db_size = 0" + ", db_volume = 0" + " where db_name='" + dbname + "'" + " and db_ip='" + ip + "'" ] mysql_base_api.update_row(mysql_conn, mysql_cursor, update_row) mysql_base_api.sql_close(mysql_conn, mysql_cursor) ret = [] temp = { 'name': new_dbname, 'type': sql_type, 'ip': ip, 'port': port, 'size': 0, 'volume': 0 } ret.append(temp) response.setcode('SUCCESS') ret.append(BIZ_CODE['SUCCESS']) return HttpResponse(json.dumps(ret)) else: response.setcode('NOT_SUPPORT_DABABASE_TYPE') ret = [BIZ_CODE['NOT_SUPPORT_DABABASE_TYPE']] return HttpResponse(json.dumps(ret))
def audit_log(ip, db_type, keyword=None, page=1, size=0, date_time=None): """ 访问es,获取对应mysqlAudit日志或pgAudit日志,处理后返回 :param ip: :param db_type: 根据db_type,获取mysqlAudit日志或pgAudit日志 :param keyword: 搜索关键字 :param page: 页码 :param size: 页大小 :return: by xing ming """ if date_time: # date_time = "2017-07-26" if db_type == "mysql": # 如果是mysql日志,需要将搜索时间转换成linux时间戳(以毫秒为单位) start_time = date_time + " 00:00:00" # 搜索时间的起点 end_time = date_time + " 23:59:59" # 搜索时间的终点 start_time = str( int( time.mktime(time.strptime(start_time, '%Y-%m-%d %X')) * 1000)) end_time = str( int( time.mktime(time.strptime(end_time, '%Y-%m-%d %X')) * 1000)) # mysqlAudit日志 按照date字段过滤 time_filter = { "range": { "date": { "gte": start_time, "lte": end_time } } } else: # postgresql日志需要转换成 时间戳 start_time = date_time + "T00:00:00.000Z" # 搜索时间的起点 end_time = date_time + "T23:59:59.999Z" # 搜索时间的终点 # pgAudit日志 按照时间戳过滤 time_filter = { "range": { "@timestamp": { "gte": start_time, "lte": end_time } } } else: # 如果用户没有输入日期,则表明不需要进行时间搜索 time_filter = None # 由于使用了mysqlAudit插件,白名单用户和白名单行为 相应的操作日记都不记录! # 因此,ES中没有 白名单用户和白名单行为 对应的日志信息 if db_type == "mysql": result, total = get_mysql_audit_from_es(ip, keyword, page, size, time_filter) total = 7000 if total > 7000 else total return handle_mysql_audit(result), total else: # postgresql # postgresql数据库没有使用安全插件,记录了所有的日志信息; # 因此,从ES中去取日志的时候,要将 白名单用户和白名单行为 产生的日志 过滤掉! # 获取 用户白名单 和 行为白名单 conn, cursor = mysql_base_api.sql_init( LOCAL_CONFIG.mysql_host, LOCAL_CONFIG.mysql_user, LOCAL_CONFIG.mysql_pass, LOCAL_CONFIG.mysql_database, LOCAL_CONFIG.mysql_port) # 初始化数据库 # 从数据库中查询,db_type = 'postgresql', ip为指定ip的记录 sql = "select * from white_user_action WHERE ip = '%s' and db_type = 'postgresql'" % ( ip, ) result_list = mysql_base_api.sql_execute(conn, cursor, sql, "") # 关闭数据库 mysql_base_api.sql_close(conn, cursor) white_user = [] # 白名单用户 white_action = [] # 白名单行为 for item in result_list: if item['type'] == '1': # '1'表示白名单用户 match_rule = {"match_phrase_prefix": {"user": item['value']}} white_user.append(match_rule) elif item['type'] == '2': # '2'表示白名单行为 match_rule = {"match_phrase_prefix": {"query": item['value']}} white_action.append(match_rule) user_action_filter = white_user + white_action result, total = get_postgresql_audit_from_es(ip, keyword, page, size, time_filter, user_action_filter) total = 7000 if total > 7000 else total return handle_postgresql_audit(result), total
def deletedb(request): response = Response() if request.method != 'POST': # 非post访问 response.setcode('WRONG_CONNECTION') ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) dbname = request.POST['dbname'] ip = request.POST['ip'] port = request.POST['port'] user = request.POST['username'] password = request.POST['password'] sql_type = request.POST['type'] if sql_type == 'MySQL': try: if port == '': conn, cursor = mysql_base_api.sql_init(ip, user, password, dbname) else: conn, cursor = mysql_base_api.sql_init(ip, user, password, dbname, int(port)) if str(type(conn)).find('int') != -1: response.setcode('WRONG_CONNECTION') ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) # 删除数据库 mysql_base_api.drop_database(cursor, dbname) # 删除client_info表中对应数据 conn, cursor = mysql_base_api.sql_init(jc.mysql_host, jc.mysql_user, jc.mysql_pass, None, jc.mysql_port) mysql_base_api.sql_execute(conn, cursor, "use `octastack_fuzhou`", "") del_list = [ "DELETE FROM `client_info` where db_name = '" + dbname + "'" + " and db_ip='" + ip + "'" ] mysql_base_api.update_row(conn, cursor, del_list) mysql_base_api.sql_close(conn, cursor) response.setcode('SUCCESS') ret = [BIZ_CODE['SUCCESS']] return HttpResponse(json.dumps(ret)) except: response.setcode('WRONG_CONNECTION') ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) elif sql_type == 'PostgreSQL': try: # 数据库连接参数 conn = psycopg2.connect(user=user, password=password, host=ip, port=port) conn.set_isolation_level(0) cur = conn.cursor() cur.execute("DROP DATABASE IF EXISTS " + dbname + " ;") if conn is not None: conn.commit( ) # DELETE, INSERT, UPDATE operations should commit. conn.set_isolation_level(1) # 删除client_info表中对应数据 mysql_conn, mysql_cursor = mysql_base_api.sql_init( jc.mysql_host, jc.mysql_user, jc.mysql_pass, None, jc.mysql_port) mysql_base_api.sql_execute(mysql_conn, mysql_cursor, "use `octastack_fuzhou`", "") del_list = [ "DELETE FROM `client_info` where db_name = '" + dbname + "'" + " and db_ip='" + ip + "'" ] mysql_base_api.update_row(mysql_conn, mysql_cursor, del_list) mysql_base_api.sql_close(mysql_conn, mysql_cursor) ret = [BIZ_CODE['SUCCESS']] return HttpResponse(json.dumps(ret)) except: response.setcode('WRONG_CONNECTION') ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) finally: cur.close() conn.close()
def db_info(request): """ :param request: :return: """ response = Response() ret = [] data_list = [] # 用于判断是否连接上数据库 is_connected = False # 连接本地mysql数据库octastack_fuzhou conn, cursor = gc.connect_localhost() # 从数据库取出client_info数据 data_set = mysql_base_api.sql_execute(conn, cursor, "select * from client_info;", None) if len(data_set) > 0: # 若client_info有数据 # 获取主机相关信息 common_set, record_list = get_common_set(data_set) # 按照注释中的形式组织数据 data_list = organize_db_info(common_set, record_list) for d_list in data_list: if len(d_list) > 0: infos = d_list['diff'] # 取出diff中的list conn, cursor = mysql_base_api.sql_init(d_list['db_ip'], d_list['sql_uname'], d_list['sql_passwd'], None, d_list['db_port']) if str(type(conn)).find( 'int') != -1: # 若连接时返回的是整数,说明连接失败,则继续连接其他数据库 continue check_db = mysql_base_api.sql_execute(conn, cursor, "show databases;", "") db_list = [] if len(check_db) > 0: # 首先将连接接到的远程数据库放在list中 for db in check_db: db_list.append(db['Database']) for info in infos: if info[0] != 'octastack_fuzhou': # octastack_fuzhou这个是系统数据库,不展示 if info[0] in db_list: temp = { 'name': info[0], 'type': 'MySQL', 'ip': d_list['db_ip'], 'port': d_list['db_port'], 'size': info[1], 'volume': info[3] } ret.append(temp) else: conn, cursor = mysql_base_api.sql_init( jc.mysql_host, jc.mysql_user, jc.mysql_pass, None, jc.mysql_port) mysql_base_api.sql_execute(conn, cursor, "use octastack_fuzhou;", "") mysql_base_api.sql_execute( conn, cursor, "delete from `client_info` WHERE db_name = '" + info[0] + "';", "") is_connected = True mysql_base_api.sql_close(conn, cursor) if is_connected: response.setcode('SUCCESS') ret.append(BIZ_CODE['SUCCESS']) else: response.setcode('WRONG_CONNECTION') ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) return HttpResponse(json.dumps(ret))
def ssh_cmd(params, cmd_port): import pexpect ssh_connect = pexpect.spawn('ssh %s@%s' % (params['osName'], params['ip2'])) ssh_connect.timeout = 10 i = ssh_connect.expect([ 'continue connecting (yes/no)?', 'password', pexpect.EOF, pexpect.TIMEOUT ]) if i == 0: ssh_connect.sendline('yes') i = ssh_connect.expect(['password', 'Warning: Permanently added']) if i == 0: ssh_connect.sendline(params['osPassword']) if params['port1'] != params['port2']: ssh_connect.sendline(cmd_port) # j = ssh_connect.expect(['password', pexpect.EOF, pexpect.TIMEOUT]) # if j == 0: # ssh_connect.sendline(params['osPassword']) # else: ssh_connect.sendline(params['osPassword']) return {'result': True} if i == 1: ssh_connect.sendline(params['osPassword']) if params['ip1'] != params['ip2']: ssh_conn = pexpect.spawn("scp /tmp/database.sql " + params['osName'] + "@" + params['ip2'] + ":/tmp/database.sql") r2 = ssh_conn.expect([ 'continue connecting (yes/no)?', 'password', pexpect.EOF, pexpect.TIMEOUT ]) if r2 == 0: ssh_conn.sendline(params['preOSPassword']) r = ssh_conn.expect( ['password:'******'osPassword']) if r2 == 1: ssh_conn.sendline(params['osPassword']) if params['port1'] != params['port2']: ssh_connect.sendline(cmd_port) # j = ssh_connect.expect(['password', pexpect.EOF, pexpect.TIMEOUT]) # if j == 0: # ssh_connect.sendline(params['osPassword']) # else: ssh_connect.sendline(params['osPassword']) conn, cursor = mysql_base_api.sql_init(params['ip2'], params['username2'], params['password2'], None, int(params['port2'])) return_dic = {} if str(type(conn)).find('int') != -1: if conn == 2003 and str(cursor).find( '113'): # 1. 网络不通 conn为整数2003, cursor 中包含错误(113) ret = [BIZ_CODE['NET_UNREACHABLE']] return HttpResponse(json.dumps(ret)) if conn == 2003 and str(cursor).find( '111'): # 2. 没有数据库或无法连接到数据库,conn为2003,cursor中包含错误(111) ret = [BIZ_CODE['NOT_SUPPORT_DABABASE_TYPE']] return HttpResponse(json.dumps(ret)) if conn == 1045: # 3. 用户名密码错误,conn为1045 ret = [BIZ_CODE['WRONG_CONNECTION']] return HttpResponse(json.dumps(ret)) if conn == 1130: # 4. 主机被限制连接,conn为1130 ret = [BIZ_CODE['HOST_BINDED']] return HttpResponse(json.dumps(ret)) if conn == 1049: # 5. database is not exist ret = [BIZ_CODE['NOT_SUPPORT_DABABASE_TYPE']] return HttpResponse(json.dumps(ret)) else: r = mysql_base_api.create_database(conn, cursor, params['dbname']) # 创建新的数据库 if len(r) > 0 and r[0] == 1007: mysql_base_api.sql_execute(conn, cursor, "use " + params['dbname'] + "", "") mysql_base_api.sql_execute(conn, cursor, 'source /tmp/database.sql;', "") # 获取新数据库的size和volume mysql_base_api.sql_execute(conn, cursor, "use information_schema;", "") volume = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as data from TABLES", "") size = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as " "data from TABLES where table_schema='" + params['dbname'] + "';", "") if size[0]['data'] is None: size[0]['data'] = 0 # 更新client_info表 conn, cursor = mysql_base_api.sql_init( jc.mysql_host, jc.mysql_user, jc.mysql_pass, None, jc.mysql_port) mysql_base_api.sql_execute(conn, cursor, "use octastack_fuzhou;", "") update_row = [ "UPDATE `client_info` SET db_ip='" + params['ip2'] + "', db_size = " + str(size[0]['data']) + ", db_volume = " + str(volume[0]['data']) + " where db_name='" + params['dbname'] + "'" ] mysql_base_api.update_row(conn, cursor, update_row) # 删除数据库 conn, cursor = mysql_base_api.sql_init( params['ip1'], params['username1'], params['password1'], params['dbname'], int(params['port1'])) mysql_base_api.drop_database(cursor, params['dbname']) elif str(type(r)).find('tuple'): mysql_base_api.sql_execute(conn, cursor, "use " + params['dbname'] + "", "") mysql_base_api.sql_execute(conn, cursor, 'source /tmp/database.sql;', "") # 获取新数据库的size和volume mysql_base_api.sql_execute(conn, cursor, "use information_schema;", "") volume = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as data from TABLES", "") size = mysql_base_api.sql_execute( conn, cursor, "select concat(round(sum(DATA_LENGTH/1024/1024),2)) as " "data from TABLES where table_schema='" + params['dbname'] + "';", "") if size[0]['data'] is None: size[0]['data'] = 0 # 更新client_info表 conn, cursor = mysql_base_api.sql_init( jc.mysql_host, jc.mysql_user, jc.mysql_pass, jc.mysql_database, jc.mysql_port) # mysql_base_api.sql_execute(conn, cursor, "use octastack_fuzhou;", "") update_row = [ "UPDATE `client_info` SET db_ip='" + params['ip2'] + "', db_size = " + str(size[0]['data']) + ", db_volume = " + str(volume[0]['data']) + " where db_name='" + params['dbname'] + "'" ] mysql_base_api.update_row(conn, cursor, update_row) # 删除数据库 conn, cursor = mysql_base_api.sql_init( params['ip1'], params['username1'], params['password1'], params['dbname'], int(params['port1'])) mysql_base_api.drop_database(cursor, params['dbname']) return_dic['volume'] = volume[0]['data'] return_dic['size'] = size[0]['data'] mysql_base_api.sql_close(conn, cursor) return_dic['result'] = True return return_dic return {'result': False}