def get_host_auth_info(host_auth_id):
db = get_db()
field_a = ['id', 'auth_mode', 'user_name', 'user_pswd', 'user_param', 'cert_id', 'encrypt']
field_b = ['host_id', 'host_lock', 'host_ip', 'host_port', 'host_desc', 'group_id', 'host_sys_type', 'protocol']
sql = 'SELECT {},{} ' \
'FROM `{}auth_info` AS a ' \
'LEFT JOIN `{}host_info` AS b ON `a`.`host_id`=`b`.`host_id` ' \
'WHERE `a`.`id`={};'.format(','.join(['`a`.`{}`'.format(i) for i in field_a]),
','.join(['`b`.`{}`'.format(i) for i in field_b]),
db.table_prefix, db.table_prefix,
host_auth_id)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) != 1:
return None
x = DbItem()
x.load(db_ret[0], ['a_{}'.format(i) for i in field_a] + ['b_{}'.format(i) for i in field_b])
h = dict()
h['host_ip'] = x.b_host_ip
h['host_port'] = x.b_host_port
h['sys_type'] = x.b_host_sys_type
h['auth_mode'] = x.a_auth_mode
h['user_name'] = x.a_user_name
h['protocol'] = x.b_protocol
if x.a_encrypt is None:
h['encrypt'] = 1
else:
h['encrypt'] = x.a_encrypt
if x.a_user_param is None:
h['user_param'] = ''
else:
h['user_param'] = x.a_user_param
h['user_auth'] = x.a_user_pswd
if x.a_auth_mode == 1:
h['user_auth'] = x.a_user_pswd
elif x.a_auth_mode == 2:
if x.a_cert_id is None:
cert_id = 0
else:
cert_id = int(x.a_cert_id) # int(user_auth)
sql = 'SELECT `cert_pri` FROM `{}key` WHERE `cert_id`={};'.format(db.table_prefix, cert_id)
db_ret = db.query(sql)
if db_ret is not None and len(db_ret) == 1:
(cert_pri,) = db_ret[0]
h['user_auth'] = cert_pri
else:
return None
elif x.a_auth_mode == 0:
h['user_auth'] = ''
else:
return None
return h
def get_user_list(with_admin=False):
db = get_db()
ret = list()
field_a = ['account_id', 'account_type', 'account_name', 'account_status', 'account_lock', 'account_desc']
if with_admin:
where = ''
else:
where = 'WHERE `a`.`account_type`<100'
sql = 'SELECT {} FROM `{}account` as a {} ORDER BY `account_name`;'.format(','.join(['`a`.`{}`'.format(i) for i in field_a]), db.table_prefix, where)
db_ret = db.query(sql)
if db_ret is None:
return ret
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a])
h = dict()
h['user_id'] = x.a_account_id
h['user_type'] = x.a_account_type
h['user_name'] = x.a_account_name
h['user_status'] = x.a_account_status
h['user_lock'] = x.a_account_lock
h['user_desc'] = x.a_account_desc
ret.append(h)
return ret
def alloc_host(user_name, host_list):
db = get_db()
field_a = ['host_id']
sql = 'SELECT {} FROM `{}auth` AS a WHERE `account_name`="{}";'.format(','.join(['`a`.`{}`'.format(i) for i in field_a]), db.table_prefix, user_name)
db_ret = db.query(sql)
ret = dict()
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a])
host_id = int(x.a_host_id)
ret[host_id] = host_id
a_list = list()
for item in host_list:
if item in ret:
pass
else:
a_list.append(item)
try:
for item in a_list:
host_id = int(item)
sql = 'INSERT INTO `{}auth` (`account_name`, `host_id`) VALUES ("{}", {});'.format(db.table_prefix, user_name, host_id)
ret = db.exec(sql)
if not ret:
return False
return True
except:
return False
def get_cert_list():
db = get_db()
field_a = ['cert_id', 'cert_name', 'cert_pub', 'cert_pri', 'cert_desc']
sql = 'SELECT {} FROM `{}key` AS a;'.format(','.join(['`a`.`{}`'.format(i) for i in field_a]), db.table_prefix)
db_ret = db.query(sql)
ret = list()
if db_ret is None:
return ret
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a])
h = dict()
h['cert_id'] = x.a_cert_id
if x.a_cert_name is None:
x.a_cert_name = ''
h['cert_name'] = x.a_cert_name
h['cert_pub'] = x.a_cert_pub
h['cert_pri'] = x.a_cert_pri
if x.a_cert_desc is None:
x.a_cert_desc = ''
h['cert_desc'] = x.a_cert_desc
ret.append(h)
return ret
def alloc_host_user(user_name, host_auth_dict):
db = get_db()
field_a = ['host_id', 'host_auth_id']
sql = 'SELECT {} FROM `{}auth` AS a WHERE `account_name`="{}";'.format(
','.join(['`a`.`{}`'.format(i) for i in field_a]), db.table_prefix,
user_name)
db_ret = db.query(sql)
ret = dict()
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a])
host_id = int(x.a_host_id)
host_auth_id = int(x.a_host_auth_id)
if host_id not in ret:
ret[host_id] = dict()
temp = ret[host_id]
temp[host_auth_id] = host_id
ret[host_id] = temp
add_dict = dict()
for k, v in host_auth_dict.items():
host_id = int(k)
auth_id_list = v
for item in auth_id_list:
host_auth_id = int(item)
if host_id not in ret:
add_dict[host_auth_id] = host_id
continue
temp = ret[host_id]
if host_auth_id not in temp:
add_dict[host_auth_id] = host_id
continue
try:
for k, v in add_dict.items():
host_auth_id = int(k)
host_id = int(v)
sql = 'INSERT INTO `{}auth` (`account_name`, `host_id`, `host_auth_id`) VALUES ("{}", {}, {});'.format(
db.table_prefix, user_name, host_id, host_auth_id)
ret = db.exec(sql)
if not ret:
return False
return True
except:
return False
def sys_user_list(host_id, with_pwd=True, host_auth_id=0):
db = get_db()
field_a = ['id', 'host_id', 'auth_mode', 'user_name', 'user_pswd', 'user_param', 'cert_id', 'log_time']
if host_auth_id == 0:
sql = 'SELECT {} ' \
'FROM `{}auth_info` AS a ' \
'WHERE `a`.`host_id`={};'.format(','.join(['`a`.`{}`'.format(i) for i in field_a]), db.table_prefix, int(host_id))
else:
sql = 'SELECT {} ' \
'FROM `{}auth_info` AS a ' \
'WHERE `a`.`id`={} and `a`.`host_id`={};'.format(','.join(['`a`.`{}`'.format(i) for i in field_a]), db.table_prefix, int(host_auth_id), int(host_id))
db_ret = db.query(sql)
if db_ret is None:
return None
ret = list()
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a])
h = dict()
# h['id'] = x.a_id
h['host_auth_id'] = x.a_id
h['host_id'] = x.a_host_id
# h['pro_type'] = x.a_pro_type
h['auth_mode'] = x.a_auth_mode
h['user_name'] = x.a_user_name
if with_pwd:
h['user_pswd'] = x.a_user_pswd
if x.a_user_param is None:
h['user_param'] = ''
else:
h['user_param'] = x.a_user_param
h['cert_id'] = x.a_cert_id
h['log_time'] = x.a_log_time
# if x.a_auth_mode == 2:
# h['user_auth'] = x.a_user_auth
# else:
# h['user_auth'] = "******"
ret.append(h)
return ret
def get_group_list():
db = get_db()
field_a = ['group_id', 'group_name']
sql = 'SELECT {} FROM `{}group` AS a; '.format(','.join(['`a`.`{}`'.format(i) for i in field_a]), db.table_prefix)
db_ret = db.query(sql)
if db_ret is None:
return list()
ret = list()
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a])
h = dict()
h['id'] = x.a_group_id
h['group_name'] = x.a_group_name
ret.append(h)
return ret
def get_log_list(_filter, limit):
db = get_db()
_where = ''
if len(_filter) > 0:
_where = 'WHERE ( '
need_and = False
for k in _filter:
if k == 'account_name':
if need_and:
_where += ' AND'
_where += ' `a`.`account_name`="{}"'.format(_filter[k])
need_and = True
if k == 'user_name':
if need_and:
_where += ' AND'
_where += ' `a`.`account_name`="{}"'.format(_filter[k])
need_and = True
elif k == 'search':
# 查找，限于主机ID和IP地址，前者是数字，只能精确查找，后者可以模糊匹配
# 因此，先判断搜索项能否转换为数字。
if need_and:
_where += ' AND '
_where += '('
_where += '`a`.`host_ip` LIKE "%{}%" )'.format(_filter[k])
need_and = True
_where += ')'
# http://www.jb51.net/article/46015.htm
field_a = ['id', 'session_id', 'account_name', 'host_ip', 'host_port', 'auth_type', 'sys_type', 'user_name', 'ret_code',
'begin_time', 'end_time', 'log_time', 'protocol']
sql = 'SELECT COUNT(*) FROM `{}log` AS a {};'.format(db.table_prefix, _where)
db_ret = db.query(sql)
total_count = db_ret[0][0]
# 修正分页数据
_limit = ''
if len(limit) > 0:
_page_index = limit['page_index']
_per_page = limit['per_page']
_limit = 'LIMIT {},{}'.format(_page_index * _per_page, (_page_index + 1) * _per_page)
if _page_index * _per_page >= total_count:
_page_index = int(total_count / _per_page)
_limit = 'LIMIT {},{}'.format(_page_index * _per_page, (_page_index + 1) * _per_page)
sql = 'SELECT {} FROM `{}log` AS a {} ORDER BY `begin_time` DESC {};'.format(','.join(['a.{}'.format(i) for i in field_a]), db.table_prefix, _where, _limit)
db_ret = db.query(sql)
ret = list()
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a])
h = dict()
h['id'] = x.a_id
h['session_id'] = x.a_session_id
h['account_name'] = x.a_account_name
h['host_ip'] = x.a_host_ip
h['host_port'] = x.a_host_port
h['auth_type'] = x.a_auth_type
h['sys_type'] = x.a_sys_type
h['user_name'] = x.a_user_name
h['ret_code'] = x.a_ret_code
cost_time = (x.a_end_time - x.a_begin_time)
if cost_time < 0:
cost_time = 0
h['cost_time'] = cost_time
h['begin_time'] = x.a_begin_time
if x.a_protocol is not None:
h['protocol'] = x.a_protocol
else:
if x.a_sys_type == 1:
h['protocol'] = 1
else:
h['protocol'] = 2
ret.append(h)
return total_count, ret
def get_all_host_info_list(_filter, order, limit, with_pwd=False):
db = get_db()
_where = ''
if len(_filter) > 0:
_where = 'WHERE ( '
need_and = False
for k in _filter:
if k == 'host_group':
if need_and:
_where += ' AND'
_where += ' `b`.`group_id`={}'.format(_filter[k])
need_and = True
elif k == 'host_sys_type':
if need_and:
_where += ' AND'
_where += ' `a`.`host_sys_type`={}'.format(_filter[k])
need_and = True
elif k == 'search':
# 查找，限于主机ID和IP地址，前者是数字，只能精确查找，后者可以模糊匹配
# 因此，先判断搜索项能否转换为数字。
if need_and:
_where += ' AND '
_where += '('
_where += '`a`.`host_ip` LIKE "%{}%" OR `a`.`host_desc` LIKE "%{}%" )'.format(_filter[k], _filter[k], _filter[k])
need_and = True
_where += ')'
# http://www.jb51.net/article/46015.htm
field_a = ['host_id', 'host_lock', 'host_ip', 'host_port', 'protocol', 'host_desc', 'group_id', 'host_sys_type']
field_b = ['group_name']
sql = 'SELECT COUNT(*) ' \
'FROM `{}host_info` AS a ' \
'LEFT JOIN `{}group` AS b ON `a`.`group_id`=`b`.`group_id` ' \
'{};'.format(db.table_prefix, db.table_prefix, _where)
db_ret = db.query(sql)
if db_ret is None:
return 0, list()
total_count = db_ret[0][0]
# 修正分页数据
_limit = ''
if len(limit) > 0:
_page_index = limit['page_index']
_per_page = limit['per_page']
_limit = 'LIMIT {},{}'.format(_page_index * _per_page, (_page_index + 1) * _per_page)
if _page_index * _per_page >= total_count:
_page_index = int(total_count / _per_page)
_limit = 'LIMIT {},{}'.format(_page_index * _per_page, (_page_index + 1) * _per_page)
# 生成排序规则
_order = ''
if order is not None:
_order = 'ORDER BY '
if 'host_id' == order['name']:
_order += '`a`.`host_id`'
elif 'ip' == order['name']:
_order += '`a`.`host_ip`'
else:
_order = ''
if not order['asc'] and len(_order) > 0:
_order += ' DESC'
sql = 'SELECT {},{} ' \
'FROM `{}host_info` AS a ' \
'LEFT JOIN `{}group` AS b ON `a`.`group_id`=`b`.`group_id` ' \
'{} {} {};'.format(','.join(['`a`.`{}`'.format(i) for i in field_a]),
','.join(['`b`.`{}`'.format(i) for i in field_b]),
db.table_prefix, db.table_prefix,
_where, _order, _limit)
db_ret = db.query(sql)
if db_ret is None:
return 0, list()
ret = list()
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a] +
['b_{}'.format(i) for i in field_b])
h = dict()
h['host_id'] = x.a_host_id
h['host_port'] = x.a_host_port
h['protocol'] = x.a_protocol
h['host_lock'] = x.a_host_lock
h['host_ip'] = x.a_host_ip
h['host_desc'] = x.a_host_desc
h['group_id'] = x.a_group_id
h['host_sys_type'] = x.a_host_sys_type
group_name = '默认分组'
if x.b_group_name is not None:
group_name = x.b_group_name
h['group_name'] = group_name
# h['auth_list'] = list()
# auth_list = h['auth_list']
h['auth_list'] = sys_user_list(x.a_host_id, with_pwd)
# auth = dict()
# auth['host_auth_id'] = x.c_id
# auth['auth_mode'] = x.c_auth_mode
# auth['user_name'] = x.c_user_name
# auth_list.append(auth)
ret.append(h)
return total_count, ret
def get_auth_info(auth_id):
"""
根据指定的auth_id查询相关的认证信息（远程主机IP、端口、登录用户名、登录密码或私钥，等等）
@param auth_id: integer
@return:
"""
db = get_db()
field_a = ['auth_id', 'account_name', 'host_auth_id', 'host_id']
field_b = ['host_sys_type', 'host_ip', 'host_port', 'protocol']
field_c = ['user_pswd', 'cert_id', 'user_name', 'encrypt', 'auth_mode', 'user_param']
field_d = ['account_lock']
sql = 'SELECT {},{},{},{} ' \
'FROM `{}auth` AS a ' \
'LEFT JOIN `{}host_info` AS b ON `a`.`host_id`=`b`.`host_id` ' \
'LEFT JOIN `{}auth_info` AS c ON `a`.`host_auth_id`=`c`.`id` ' \
'LEFT JOIN `{}account` AS d ON `a`.`account_name`=`d`.`account_name` ' \
'WHERE `a`.`auth_id`={};' \
''.format(','.join(['`a`.`{}`'.format(i) for i in field_a]),
','.join(['`b`.`{}`'.format(i) for i in field_b]),
','.join(['`c`.`{}`'.format(i) for i in field_c]),
','.join(['`d`.`{}`'.format(i) for i in field_d]),
db.table_prefix, db.table_prefix, db.table_prefix, db.table_prefix,
auth_id)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) != 1:
return None
db_item = DbItem()
db_item.load(db_ret[0],
['a_{}'.format(i) for i in field_a] +
['b_{}'.format(i) for i in field_b] +
['c_{}'.format(i) for i in field_c] +
['d_{}'.format(i) for i in field_d]
)
ret = dict()
ret['host_ip'] = db_item.b_host_ip
ret['sys_type'] = db_item.b_host_sys_type
ret['account_name'] = db_item.a_account_name
ret['account_lock'] = db_item.d_account_lock
# h['host_lock'] = x.a_host_lock
ret['host_port'] = db_item.b_host_port
ret['protocol'] = db_item.b_protocol
ret['encrypt'] = db_item.c_encrypt
ret['auth_mode'] = db_item.c_auth_mode
ret['user_name'] = db_item.c_user_name
ret['user_param'] = db_item.c_user_param
if db_item.c_auth_mode == 1:
ret['user_auth'] = db_item.c_user_pswd
elif db_item.c_auth_mode == 2:
cert_id = db_item.c_cert_id
sql = 'SELECT `cert_pri` FROM `{}key` WHERE `cert_id`={}'.format(db.table_prefix, int(cert_id))
db_ret = db.query(sql)
if db_ret is None or len(db_ret) > 1:
return None
ret['user_auth'] = db_ret[0][0]
else:
pass
return ret
def get_host_info_list_by_user(_filter, order, limit):
db = get_db()
_where = ''
if len(_filter) > 0:
_where = 'WHERE ( '
need_and = False
for k in _filter:
if k == 'host_group':
if need_and:
_where += ' AND'
_where += ' b.group_id={}'.format(_filter[k])
need_and = True
elif k == 'host_sys_type':
if need_and:
_where += ' AND'
_where += ' b.host_sys_type={}'.format(_filter[k])
need_and = True
elif k == 'account_name':
if need_and:
_where += ' AND'
_where += ' a.account_name=\'{}\''.format(_filter[k])
need_and = True
elif k == 'search':
# 查找，限于主机ID和IP地址，前者是数字，只能精确查找，后者可以模糊匹配
# 因此，先判断搜索项能否转换为数字。
if need_and:
_where += ' AND '
_where += '('
_where += 'b.host_ip LIKE "%{}%" OR b.host_desc LIKE "%{}%" )'.format(_filter[k], _filter[k], _filter[k])
need_and = True
_where += ')'
field_a = ['auth_id', 'host_id', 'account_name', 'host_auth_id']
field_b = ['host_id', 'host_lock', 'host_ip', 'protocol', 'host_port', 'host_desc', 'group_id', 'host_sys_type']
field_c = ['group_name']
field_d = ['auth_mode', 'user_name']
sql = 'SELECT COUNT(DISTINCT a.host_id) ' \
'FROM {}auth AS a ' \
'LEFT JOIN {}host_info AS b ON a.host_id = b.host_id ' \
'{};'.format(db.table_prefix, db.table_prefix, _where)
db_ret = db.query(sql)
total_count = db_ret[0][0]
# 修正分页数据
_limit = ''
if len(limit) > 0:
_page_index = limit['page_index']
_per_page = limit['per_page']
_limit = 'LIMIT {},{}'.format(_page_index * _per_page, (_page_index + 1) * _per_page)
if _page_index * _per_page >= total_count:
_page_index = int(total_count / _per_page)
_limit = 'LIMIT {},{}'.format(_page_index * _per_page, (_page_index + 1) * _per_page)
# 生成排序规则
_order = ''
# log.d(order['name'])
if order is not None:
_order = 'ORDER BY '
if 'host_id' == order['name']:
_order += 'b.host_id'
elif 'ip' == order['name']:
_order += 'b.host_ip'
else:
_order = ''
if not order['asc'] and len(_order) > 0:
_order += ' DESC'
sql = 'SELECT {}, {},{},{} ' \
'FROM {}auth AS a ' \
'LEFT JOIN {}host_info AS b ON a.host_id=b.host_id ' \
'LEFT JOIN {}group AS c ON b.group_id = c.group_id ' \
'LEFT JOIN {}auth_info AS d ON d.id = a.host_auth_id ' \
'{} {} {};' \
''.format(','.join(['a.{}'.format(i) for i in field_a]),
','.join(['b.{}'.format(i) for i in field_b]),
','.join(['c.{}'.format(i) for i in field_c]),
','.join(['d.{}'.format(i) for i in field_d]),
db.table_prefix, db.table_prefix, db.table_prefix, db.table_prefix,
_where, _order, _limit)
db_ret = db.query(sql)
ret = list()
temp = dict()
for item in db_ret:
x = DbItem()
x.load(item, ['a_{}'.format(i) for i in field_a] + ['b_{}'.format(i) for i in field_b] + ['c_{}'.format(i) for i in field_c] + ['d_{}'.format(i) for i in field_d])
host_ip = x.b_host_ip
protocol = x.b_protocol
key = '{}-{}'.format(host_ip, protocol)
temp_auth = None
extend_auth_list = sys_user_list(x.b_host_id, False, x.a_host_auth_id)
if extend_auth_list is not None and len(extend_auth_list) > 0:
auth = extend_auth_list[0]
auth['auth_id'] = x.a_auth_id
temp_auth = auth
add = False
if key in temp:
h = temp[key]
auth_list = h['auth_list']
auth_list.append(temp_auth)
h['auth_list'] = auth_list
else:
h = dict()
h['host_id'] = x.b_host_id
h['host_lock'] = x.b_host_lock
h['host_ip'] = host_ip
h['host_port'] = x.b_host_port
h['host_desc'] = x.b_host_desc
h['group_id'] = x.b_group_id
h['host_sys_type'] = x.b_host_sys_type
h['protocol'] = x.b_protocol
group_name = '默认分组'
if x.c_group_name is not None:
group_name = x.c_group_name
h['group_name'] = group_name
add = True
temp[key] = h
h['auth_list'] = list()
auth_list = h['auth_list']
auth_list.append(temp_auth)
h['auth_list'] = auth_list
if add:
ret.append(h)
return total_count, ret