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 connection_test(request):
if request.method == 'POST':
dbname = request.POST['dbname']
uname = request.POST['username']
passwd = request.POST['password']
ip = request.POST['ip']
port = int(
request.POST['port']) if (request.POST['port'] != '') else 3306
conn, cursor = mysql_base_api.sql_init(ip, uname, passwd, dbname, port)
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:
os.system("mysqldump --opt -h" + ip + " -u" + uname + " -p" +
passwd + " --skip-lock-tables " + dbname +
" > /tmp/database.sql") # export database
ret = [BIZ_CODE['SUCCESS']]
return HttpResponse(json.dumps(ret))
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 judge_port(ip, username, password, port):
"""
根据port的数据类型做相应处理
:param ip:
:param username:
:param password:
:param port:
:return:
"""
if port == '':
port = 3306
conn, cursor = mysql_base_api.sql_init(ip, username, password, None,
port)
else:
conn, cursor = mysql_base_api.sql_init(ip, username, password, None,
int(port))
return conn, cursor
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 get_conn_cursor():
try:
conn, cursor = mysql_base_api.sql_init(db_host=jc.mysql_host,
db_port=jc.mysql_port,
db_user=jc.mysql_user,
db_passwd=jc.mysql_pass,
db_name=jc.mysql_database)
except Exception as e:
logger.error(e)
return conn, cursor
def connect_localhost(self):
"""
连接本地mysql数据库octastack_fuzhou
update by wangjiashuai:不能连本地,要连配置的
:return conn, cursor:
"""
conn, cursor = mysql_base_api.sql_init(jc.mysql_host, jc.mysql_user,
jc.mysql_pass,
jc.mysql_database,
jc.mysql_port)
return conn, cursor
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 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 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 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 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}
def transfer_db(request):
if request.method != 'POST':
response = Response()
# 非post访问
response.setcode('WRONG_CONNECTION')
ret = [BIZ_CODE['WRONG_CONNECTION']]
return HttpResponse(json.dumps(ret))
username1 = request.POST['username1']
dbname = request.POST['dbname']
password1 = request.POST['password1']
ip1 = request.POST['ip1']
ip2 = request.POST['ip2']
type2 = request.POST['type2']
port1 = request.POST['port1']
port2 = request.POST['port2']
ip1 = 'localhost' if ip1 == gc.LOCALHOST else ip1
ip2 = 'localhost' if ip2 == gc.LOCALHOST else ip2
# 首先判断数据库类型在客户机上是否存在
if type2 != 'MySQL':
ret = [BIZ_CODE['NOT_SUPPORT_DABABASE_TYPE']]
return HttpResponse(json.dumps(ret))
if type2 == 'MySQL':
conn, cursor = mysql_base_api.sql_init(ip1, username1, password1,
dbname, int(port1))
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:
ret = [BIZ_CODE['NOT_SUPPORT_DABABASE_TYPE']]
return HttpResponse(json.dumps(ret))
else:
command1 = "sudo sed -i 's/^port/port = " + port2 + \
"#/' /etc/mysql/my.cnf"
res = ssh_cmd(request.POST, command1)
if res['result']:
ret = []
temp = {
'name': dbname,
'type': type2,
'ip': ip2,
'size': float(res['size']),
'volume': float(res['volume']),
'port': port2
}
ret.append(temp)
ret.append(BIZ_CODE['SUCCESS'])
return HttpResponse(json.dumps(ret))
else:
ret = [BIZ_CODE['TRANSFER_DB_FAILED']]
return HttpResponse(json.dumps(ret))
