def get_policies(sql_filter, sql_order, sql_limit):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'], alt_name='p')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
if k == 'state':
_where.append('p.state={}'.format(sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, s.total_count, 0, s.recorder
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
s.order_by('p.rank', True)
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_policies(sql_filter, sql_order, sql_limit):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'],
alt_name='p')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.
format(filter=sql_filter[k]))
if k == 'state':
_where.append('p.state={}'.format(sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, s.total_count, 0, s.recorder
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
s.order_by('p.rank', True)
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_host_accounts(host_id):
# 获取指定主机的所有账号
s = SQL(get_db())
# s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a')
s.select_from('acc', ['id', 'state', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt'], alt_name='a')
s.where('a.host_id={}'.format(host_id))
s.order_by('a.username', True)
err = s.query()
return err, s.recorder
def get_host_groups_for_user(user_id, user_privilege):
# get all host-groups for current logged in user.
db = get_db()
# step 0. return all host-groups if user have all host-group access privilege
if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE
| TP_PRIVILEGE_ASSET_GROUP)) != 0:
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(TP_GROUP_HOST))
s.order_by('g.name')
err = s.query()
return err, s.recorder
# step 1. get all hosts which could be access by this user.
sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format(
dbtp=db.table_prefix, dbph=db.place_holder)
db_ret = db.query(sql, (user_id, ))
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
hosts = []
for db_item in db_ret:
hosts.append(str(db_item[0]))
if len(hosts) == 0:
return TPE_NOT_EXISTS, None
# step 2. get groups which include those hosts.
sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format(
dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts))
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
groups = []
for db_item in db_ret:
groups.append(str(db_item[0]))
# step 3. get those groups id and name.
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.id IN ({})'.format(','.join(groups)))
s.order_by('g.name')
err = s.query()
return err, s.recorder
def get_host_accounts(host_id):
# 获取指定主机的所有账号
s = SQL(get_db())
# s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a')
s.select_from('acc', [
'id', 'state', 'protocol_type', 'protocol_port', 'auth_type',
'username', 'username_prompt', 'password_prompt'
],
alt_name='a')
s.where('a.host_id={}'.format(host_id))
s.order_by('a.username', True)
err = s.query()
return err, s.recorder
def get_host_groups_for_user(user_id, user_privilege):
# get all host-groups for current logged in user.
db = get_db()
# step 0. return all host-groups if user have all host-group access privilege
if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE | TP_PRIVILEGE_ASSET_GROUP)) != 0:
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(TP_GROUP_HOST))
s.order_by('g.name')
err = s.query()
return err, s.recorder
# step 1. get all hosts which could be access by this user.
sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format(dbtp=db.table_prefix, dbph=db.place_holder)
db_ret = db.query(sql, (user_id, ))
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
hosts = []
for db_item in db_ret:
hosts.append(str(db_item[0]))
if len(hosts) == 0:
return TPE_NOT_EXISTS, None
# step 2. get groups which include those hosts.
sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format(dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts))
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
groups = []
for db_item in db_ret:
groups.append(str(db_item[0]))
# step 3. get those groups id and name.
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.id IN ({})'.format(','.join(groups)))
s.order_by('g.name')
err = s.query()
return err, s.recorder
def get_logs(sql_filter, sql_order, sql_limit):
s = SQL(get_db())
s.select_from('syslog', [
'id', 'user_name', 'user_surname', 'client_ip', 'code', 'log_time',
'message'
],
alt_name='l')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'log_user_name':
_where.append('l.user_name="{}"'.format(sql_filter[k]))
# elif k == 'search_record':
# _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'log_time' == sql_order['name']:
s.order_by('l.log_time', _sort)
# elif 'name' == sql_order['name']:
# s.order_by('h.name', _sort)
# elif 'os_type' == sql_order['name']:
# s.order_by('h.os_type', _sort)
# elif 'cid' == sql_order['name']:
# s.order_by('h.cid', _sort)
# elif 'state' == sql_order['name']:
# s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.recorder
def get_logs(sql_filter, sql_order, sql_limit):
s = SQL(get_db())
s.select_from('syslog', ['id', 'user_name', 'user_surname', 'client_ip', 'code', 'log_time', 'message'], alt_name='l')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'log_user_name':
_where.append('l.user_name="{}"'.format(sql_filter[k]))
# elif k == 'search_record':
# _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'log_time' == sql_order['name']:
s.order_by('l.log_time', _sort)
# elif 'name' == sql_order['name']:
# s.order_by('h.name', _sort)
# elif 'os_type' == sql_order['name']:
# s.order_by('h.os_type', _sort)
# elif 'cid' == sql_order['name']:
# s.order_by('h.cid', _sort)
# elif 'state' == sql_order['name']:
# s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.recorder
def get_auditees(sql_filter, sql_order, sql_limit):
ss = SQL(get_db())
ss.select_from('audit_auz', ['id', 'policy_id', 'rtype', 'rid', 'name'],
alt_name='p')
_where = list()
_where.append('p.type=1')
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'policy_id':
# _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
_where.append('p.policy_id={}'.format(sql_filter[k]))
elif k == 'search':
_where.append(
'(p.name LIKE "%{filter}%")'.format(filter=sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, 0, 0, {}
if len(_where) > 0:
ss.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
ss.order_by('p.name', _sort)
elif 'rtype' == sql_order['name']:
ss.order_by('p.rtype', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, ss.total_count, 0, ss.recorder
if len(sql_limit) > 0:
ss.limit(sql_limit['page_index'], sql_limit['per_page'])
err = ss.query()
if err != TPE_OK:
return err, 0, 0, {}
# print(ss.recorder)
return TPE_OK, ss.total_count, ss.page_index, ss.recorder
def get_asset(sql_filter, sql_order, sql_limit):
ss = SQL(get_db())
ss.select_from('ops_auz', ['id', 'policy_id', 'rtype', 'rid', 'name'], alt_name='p')
_where = list()
_where.append('p.type=1')
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'policy_id':
# _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
_where.append('p.policy_id={}'.format(sql_filter[k]))
elif k == 'search':
_where.append('(p.name LIKE "%{filter}%")'.format(filter=sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, 0, 0, {}
if len(_where) > 0:
ss.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
ss.order_by('p.name', _sort)
elif 'rtype' == sql_order['name']:
ss.order_by('p.rtype', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, ss.total_count, 0, ss.recorder
if len(sql_limit) > 0:
ss.limit(sql_limit['page_index'], sql_limit['per_page'])
err = ss.query()
if err != TPE_OK:
return err, 0, 0, {}
# print(ss.recorder)
return TPE_OK, ss.total_count, ss.page_index, ss.recorder
def get_groups(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
str_where = ''
_where = list()
# if len(sql_restrict) > 0:
# for k in sql_restrict:
# if k == 'ops_policy_id':
# _where.append('g.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype=2)'.format(dbtp=dbtp, pid=sql_exclude[k]))
# else:
# log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
# if k == 'group_id':
# _where.append('u.id NOT IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_exclude[k]))
if k == 'ops_policy_id':
pid = sql_exclude[k]['pid']
gtype = sql_exclude[k]['gtype']
_where.append('g.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'.format(dbtp=dbtp, pid=pid, rtype=gtype))
elif k == 'auditor_policy_id':
pid = sql_exclude[k]['pid']
gtype = sql_exclude[k]['gtype']
_where.append('g.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'.format(dbtp=dbtp, pid=pid, ptype=TP_POLICY_OPERATOR, rtype=gtype))
elif k == 'auditee_policy_id':
pid = sql_exclude[k]['pid']
gtype = sql_exclude[k]['gtype']
_where.append('g.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'.format(dbtp=dbtp, pid=pid, ptype=TP_POLICY_ASSET, rtype=gtype))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'type':
_where.append('g.type={filter}'.format(filter=sql_filter[k]))
elif k == 'state':
_where.append('g.state={filter}'.format(filter=sql_filter[k]))
elif k == 'search':
_where.append('(g.name LIKE "%{filter}%" OR g.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, 0, 0, {}
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
s.order_by('g.name', _sort)
elif 'state' == sql_order['name']:
s.order_by('g.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, 0, 0, {}
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_group_with_member(sql_filter, sql_order, sql_limit):
"""
获取用户组列表，以及每个组的总成员数以及不超过5个的成员
"""
# 首先获取要查询的组的信息
sg = SQL(get_db())
sg.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
_where = list()
_where.append('g.type={}'.format(TP_GROUP_USER))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(g.name LIKE "%{filter}%" OR g.desc LIKE "%{filter}%")'.
format(filter=sql_filter[k]))
elif k == 'state':
_where.append(
'(g.state={filter})'.format(filter=sql_filter[k]))
if len(_where) > 0:
sg.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
sg.order_by('g.name', _sort)
elif 'state' == sql_order['name']:
sg.order_by('g.state', _sort)
else:
log.e('unknown order field.\n')
return TPE_PARAM, sg.total_count, sg.recorder
if len(sql_limit) > 0:
sg.limit(sql_limit['page_index'], sql_limit['per_page'])
err = sg.query()
if err != TPE_OK or len(sg.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
g['member_count'] = 0
g['members'] = []
g['_mid'] = [] # 临时使用，构建此组的前5个成员的id
# 对于本次要返回的用户组，取其中每一个组内成员的基本信息（id/用户名/真实名称等）
groups = [g['id'] for g in sg.recorder]
sgm = SQL(get_db())
sgm.select_from('group_map', ['gid', 'mid'], alt_name='gm')
# sgm.limit(0, 5)
_where = list()
# _where.append('({})'.format(' OR '.join(['gm.gid={}'.format(gid) for gid in groups])))
_where.append('gm.type={}'.format(TP_GROUP_USER))
_where.append('gm.gid IN ({})'.format(','.join(
[str(gid) for gid in groups])))
str_where = '( {} )'.format(' AND '.join(_where))
sgm.where(str_where)
err = sgm.query()
if err != TPE_OK or len(sgm.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
for gm in sgm.recorder:
if gm['gid'] == g['id']:
g['member_count'] += 1
if len(g['_mid']) < 5:
g['_mid'].append(gm['mid'])
# 将得到的用户id合并到列表中并去重，然后获取这些用户的信息
users = []
for g in sg.recorder:
users.extend(g['_mid'])
users = list(set(users))
su = SQL(get_db())
su.select_from('user', ['id', 'username', 'surname', 'email'],
alt_name='u')
su.where('u.id IN ({})'.format(','.join([str(uid) for uid in users])))
su.order_by('u.username')
err = su.query()
if err != TPE_OK or len(su.recorder) == 0:
return err, sg.total_count, sg.recorder
# 现在可以将具体的用户信息追加到组信息中了
for g in sg.recorder:
for u in su.recorder:
for m in g['_mid']:
if u['id'] == m:
g['members'].append(u)
return err, sg.total_count, sg.recorder
def get_users(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('user', [
'id', 'type', 'auth_type', 'username', 'surname', 'role_id', 'state',
'email', 'last_login', 'valid_from', 'valid_to'
],
alt_name='u')
s.left_join('role', ['name', 'privilege'],
join_on='r.id=u.role_id',
alt_name='r',
out_map={'name': 'role'})
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_sql = 'u.id IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
_where.append(
_sql.format(dbtp=dbtp,
gtype=TP_GROUP_USER,
gid=sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'u.id NOT IN ('
'SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
''.format(dbtp=dbtp,
gtype=TP_GROUP_USER,
gid=sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'u.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
''.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_USER))
elif k == 'auditor_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp,
pid=sql_exclude[k],
ptype=TP_POLICY_OPERATOR,
rtype=TP_USER))
elif k == 'auditee_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp,
pid=sql_exclude[k],
ptype=TP_POLICY_ASSET,
rtype=TP_USER))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'role':
_where.append(
'u.role_id={filter}'.format(filter=sql_filter[k]))
elif k == 'type':
_where.append('u.type={filter}'.format(filter=sql_filter[k]))
elif k == 'state':
_where.append('u.state={filter}'.format(filter=sql_filter[k]))
elif k == 'search':
_where.append('('
'u.username LIKE "%{filter}%" '
'OR u.surname LIKE "%{filter}%" '
'OR u.email LIKE "%{filter}%" '
'OR u.desc LIKE "%{filter}%"'
')'.format(filter=sql_filter[k]))
if len(_where) > 0:
s.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('u.username', _sort)
elif 'surname' == sql_order['name']:
s.order_by('u.surname', _sort)
elif 'role_id' == sql_order['name']:
s.order_by('u.role_id', _sort)
elif 'state' == sql_order['name']:
s.order_by('u.state', _sort)
elif 'type' == sql_order['name']:
s.order_by('u.type', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, 0, 0, {}
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_group_with_member(sql_filter, sql_order, sql_limit):
"""
获取主机组列表，以及每个组的总成员数以及不超过5个的成员
"""
# 首先获取要查询的组的信息
sg = SQL(get_db())
sg.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
_where = list()
_where.append('g.type={}'.format(TP_GROUP_HOST))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append('(g.name LIKE "%{}%" OR g.desc LIKE "%{}%")'.format(sql_filter[k], sql_filter[k]))
elif k == 'state':
_where.append('(g.state={filter})'.format(filter=sql_filter[k]))
if len(_where) > 0:
sg.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
sg.order_by('g.name', _sort)
elif 'state' == sql_order['name']:
sg.order_by('g.state', _sort)
else:
log.e('unknown order field.\n')
return TPE_PARAM, sg.total_count, sg.recorder
if len(sql_limit) > 0:
sg.limit(sql_limit['page_index'], sql_limit['per_page'])
err = sg.query()
if err != TPE_OK or len(sg.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
g['member_count'] = 0
g['members'] = []
g['_mid'] = [] # 临时使用，构建此组的前5个成员的id
# 对于本次要返回的用户组，取其中每一个组内成员的基本信息（id/用户名/真实名称等）
groups = [g['id'] for g in sg.recorder]
sgm = SQL(get_db())
sgm.select_from('group_map', ['gid', 'mid'], alt_name='gm')
# sgm.limit(0, 5)
_where = list()
# _where.append('({})'.format(' OR '.join(['gm.gid={}'.format(gid) for gid in groups])))
_where.append('gm.type={}'.format(TP_GROUP_HOST))
_where.append('gm.gid IN ({})'.format(','.join([str(gid) for gid in groups])))
str_where = '( {} )'.format(' AND '.join(_where))
sgm.where(str_where)
err = sgm.query()
if err != TPE_OK or len(sgm.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
for gm in sgm.recorder:
if gm['gid'] == g['id']:
g['member_count'] += 1
if len(g['_mid']) < 5:
g['_mid'].append(gm['mid'])
# 将得到的账号id合并到列表中并去重，然后获取这些账号的信息
users = []
for g in sg.recorder:
users.extend(g['_mid'])
users = list(set(users))
su = SQL(get_db())
su.select_from('host', ['id', 'os_type', 'name', 'ip', 'router_ip', 'router_port', 'cid'], alt_name='h')
su.where('h.id IN ({})'.format(','.join([str(uid) for uid in users])))
su.order_by('h.ip')
err = su.query()
if err != TPE_OK or len(su.recorder) == 0:
return err, sg.total_count, sg.recorder
# 现在可以将具体的用户信息追加到组信息中了
for g in sg.recorder:
for u in su.recorder:
for m in g['_mid']:
if u['id'] == m:
g['members'].append(u)
return err, sg.total_count, sg.recorder
def get_hosts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
s = SQL(get_db())
s.select_from('host', ['id', 'type', 'os_type', 'os_ver', 'name', 'ip', 'router_ip', 'router_port', 'state', 'acc_count', 'cid', 'desc'], alt_name='h')
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_where.append('h.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(get_db().table_prefix, TP_GROUP_HOST, sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append('h.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(get_db().table_prefix, TP_GROUP_HOST, sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append('h.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'.format(dbtp=get_db().table_prefix, pid=sql_exclude[k], rtype=TP_HOST))
elif k == 'auditee_policy_id':
_where.append('h.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'.format(dbtp=get_db().table_prefix, pid=sql_exclude[k], ptype=TP_POLICY_ASSET, rtype=TP_HOST))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'state':
_where.append('h.state={}'.format(sql_filter[k]))
elif k == 'search':
_where.append('(h.name LIKE "%{filter}%" OR h.ip LIKE "%{filter}%" OR h.router_ip LIKE "%{filter}%" OR h.desc LIKE "%{filter}%" OR h.cid LIKE "%{filter}%")'.format(filter=sql_filter[k]))
elif k == 'host_group':
shg = SQL(get_db())
shg.select_from('group_map', ['mid'], alt_name='g')
shg.where('g.type={} AND g.gid={}'.format(TP_GROUP_HOST, sql_filter[k]))
err = shg.query()
if err != TPE_OK:
return err, 0, 1, []
if len(shg.recorder) == 0:
return TPE_OK, 0, 1, []
h_list = ','.join([str(i['mid']) for i in shg.recorder])
_where.append('h.id IN ({})'.format(h_list))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'ip' == sql_order['name']:
s.order_by('h.ip', _sort)
elif 'name' == sql_order['name']:
s.order_by('h.name', _sort)
elif 'os_type' == sql_order['name']:
s.order_by('h.os_type', _sort)
elif 'cid' == sql_order['name']:
s.order_by('h.cid', _sort)
elif 'state' == sql_order['name']:
s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.page_index, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_accounts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
db = get_db()
dbtp = db.table_prefix
s = SQL(db)
# s.select_from('acc', ['id', 'host_id', 'host_ip', 'router_ip', 'router_port', 'username', 'protocol_type', 'auth_type', 'state'], alt_name='a')
s.select_from('acc', ['id', 'host_id', 'username', 'protocol_type', 'auth_type', 'state', 'username_prompt', 'password_prompt'], alt_name='a')
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_where.append('a.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(dbtp, TP_GROUP_ACCOUNT, sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append('a.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(dbtp, TP_GROUP_ACCOUNT, sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append('a.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_ACCOUNT))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append('(a.username LIKE "%{filter}%" OR a.host_ip LIKE "%{filter}%" OR a.router_ip LIKE "%{filter}%")'.format(filter=sql_filter[k]))
# _where.append('(a.username LIKE "%{filter}%")'.format(filter=sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('a.username', _sort)
elif 'protocol_type' == sql_order['name']:
s.order_by('a.protocol_type', _sort)
elif 'state' == sql_order['name']:
s.order_by('a.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, 1, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
if err != TPE_OK:
return err, 0, 1, None
# 得到主机id列表，然后查询相关主机的详细信息
host_ids = []
for _acc in s.recorder:
if _acc.host_id not in host_ids:
host_ids.append(_acc.host_id)
if len(host_ids) == 0:
return TPE_OK, 0, 1, None
s_host = SQL(db)
s_host.select_from('host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')
str_host_ids = ','.join([str(i) for i in host_ids])
s_host.where('h.id IN ({ids})'.format(ids=str_host_ids))
err = s_host.query()
if err != TPE_OK:
return err, 0, None
hosts = {}
for _host in s_host.recorder:
if _host.id not in hosts:
hosts[_host.id] = _host
for _acc in s.recorder:
_acc['_host'] = hosts[_acc.host_id]
return err, s.total_count, s.page_index, s.recorder
def get_groups(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
str_where = ''
_where = list()
# if len(sql_restrict) > 0:
# for k in sql_restrict:
# if k == 'ops_policy_id':
# _where.append('g.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype=2)'.format(dbtp=dbtp, pid=sql_exclude[k]))
# else:
# log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
# if k == 'group_id':
# _where.append('u.id NOT IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_exclude[k]))
if k == 'ops_policy_id':
pid = sql_exclude[k]['pid']
gtype = sql_exclude[k]['gtype']
_where.append(
'g.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
.format(dbtp=dbtp, pid=pid, rtype=gtype))
elif k == 'auditor_policy_id':
pid = sql_exclude[k]['pid']
gtype = sql_exclude[k]['gtype']
_where.append(
'g.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'
.format(dbtp=dbtp,
pid=pid,
ptype=TP_POLICY_OPERATOR,
rtype=gtype))
elif k == 'auditee_policy_id':
pid = sql_exclude[k]['pid']
gtype = sql_exclude[k]['gtype']
_where.append(
'g.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'
.format(dbtp=dbtp,
pid=pid,
ptype=TP_POLICY_ASSET,
rtype=gtype))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'type':
_where.append('g.type={filter}'.format(filter=sql_filter[k]))
elif k == 'state':
_where.append('g.state={filter}'.format(filter=sql_filter[k]))
elif k == 'search':
_where.append(
'(g.name LIKE "%{filter}%" OR g.desc LIKE "%{filter}%")'.
format(filter=sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, 0, 0, {}
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
s.order_by('g.name', _sort)
elif 'state' == sql_order['name']:
s.order_by('g.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, 0, 0, {}
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_accounts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
db = get_db()
dbtp = db.table_prefix
s = SQL(db)
# s.select_from('acc', ['id', 'host_id', 'host_ip', 'router_ip', 'router_port', 'username', 'protocol_type', 'auth_type', 'state'], alt_name='a')
s.select_from('acc', [
'id', 'host_id', 'username', 'protocol_type', 'auth_type', 'state',
'username_prompt', 'password_prompt'
],
alt_name='a')
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_where.append(
'a.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'
.format(dbtp, TP_GROUP_ACCOUNT, sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'a.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'
.format(dbtp, TP_GROUP_ACCOUNT, sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'a.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_ACCOUNT))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(a.username LIKE "%{filter}%" OR a.host_ip LIKE "%{filter}%" OR a.router_ip LIKE "%{filter}%")'
.format(filter=sql_filter[k]))
# _where.append('(a.username LIKE "%{filter}%")'.format(filter=sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('a.username', _sort)
elif 'protocol_type' == sql_order['name']:
s.order_by('a.protocol_type', _sort)
elif 'state' == sql_order['name']:
s.order_by('a.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, 1, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
if err != TPE_OK:
return err, 0, 1, None
# 得到主机id列表，然后查询相关主机的详细信息
host_ids = []
for _acc in s.recorder:
if _acc.host_id not in host_ids:
host_ids.append(_acc.host_id)
s_host = SQL(db)
s_host.select_from(
'host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'],
alt_name='h')
str_host_ids = ','.join([str(i) for i in host_ids])
s_host.where('h.id IN ({ids})'.format(ids=str_host_ids))
err = s_host.query()
if err != TPE_OK:
return err, 0, None
hosts = {}
for _host in s_host.recorder:
if _host.id not in hosts:
hosts[_host.id] = _host
for _acc in s.recorder:
_acc['_host'] = hosts[_acc.host_id]
return err, s.total_count, s.page_index, s.recorder
def get_users(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('user', ['id', 'type', 'auth_type', 'username', 'surname', 'role_id', 'state', 'email', 'last_login'],
alt_name='u')
s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'})
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_sql = 'u.id IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
_where.append(_sql.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'u.id NOT IN ('
'SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
''.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'u.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
''.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_USER))
elif k == 'auditor_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp, pid=sql_exclude[k], ptype=TP_POLICY_OPERATOR, rtype=TP_USER))
elif k == 'auditee_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp, pid=sql_exclude[k], ptype=TP_POLICY_ASSET, rtype=TP_USER))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'role':
_where.append('u.role_id={filter}'.format(filter=sql_filter[k]))
elif k == 'type':
_where.append('u.type={filter}'.format(filter=sql_filter[k]))
elif k == 'state':
_where.append('u.state={filter}'.format(filter=sql_filter[k]))
elif k == 'search':
_where.append('('
'u.username LIKE "%{filter}%" '
'OR u.surname LIKE "%{filter}%" '
'OR u.email LIKE "%{filter}%" '
'OR u.desc LIKE "%{filter}%"'
')'.format(filter=sql_filter[k]))
if len(_where) > 0:
s.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('u.username', _sort)
elif 'surname' == sql_order['name']:
s.order_by('u.surname', _sort)
elif 'role_id' == sql_order['name']:
s.order_by('u.role_id', _sort)
elif 'state' == sql_order['name']:
s.order_by('u.state', _sort)
elif 'type' == sql_order['name']:
s.order_by('u.type', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, 0, 0, {}
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_hosts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
s = SQL(get_db())
s.select_from('host', [
'id', 'type', 'os_type', 'os_ver', 'name', 'ip', 'router_ip',
'router_port', 'state', 'acc_count', 'cid', 'desc'
],
alt_name='h')
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_where.append(
'h.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'
.format(get_db().table_prefix, TP_GROUP_HOST,
sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'h.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'
.format(get_db().table_prefix, TP_GROUP_HOST,
sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'h.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
.format(dbtp=get_db().table_prefix,
pid=sql_exclude[k],
rtype=TP_HOST))
elif k == 'auditee_policy_id':
_where.append(
'h.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'
.format(dbtp=get_db().table_prefix,
pid=sql_exclude[k],
ptype=TP_POLICY_ASSET,
rtype=TP_HOST))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'state':
_where.append('h.state={}'.format(sql_filter[k]))
elif k == 'search':
_where.append(
'(h.name LIKE "%{filter}%" OR h.ip LIKE "%{filter}%" OR h.router_ip LIKE "%{filter}%" OR h.desc LIKE "%{filter}%" OR h.cid LIKE "%{filter}%")'
.format(filter=sql_filter[k]))
elif k == 'host_group':
shg = SQL(get_db())
shg.select_from('group_map', ['mid'], alt_name='g')
shg.where('g.type={} AND g.gid={}'.format(
TP_GROUP_HOST, sql_filter[k]))
err = shg.query()
if err != TPE_OK:
return err, 0, 1, []
if len(shg.recorder) == 0:
return TPE_OK, 0, 1, []
h_list = ','.join([str(i['mid']) for i in shg.recorder])
_where.append('h.id IN ({})'.format(h_list))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'ip' == sql_order['name']:
s.order_by('h.ip', _sort)
elif 'name' == sql_order['name']:
s.order_by('h.name', _sort)
elif 'os_type' == sql_order['name']:
s.order_by('h.os_type', _sort)
elif 'cid' == sql_order['name']:
s.order_by('h.cid', _sort)
elif 'state' == sql_order['name']:
s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.page_index, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_records(handler, sql_filter, sql_order, sql_limit, sql_restrict,
sql_exclude):
"""
获取会话列表
会话审计列表的显示策略（下列的`审计`操作指为会话做标记、置为保留状态、写备注等）：
1. 运维权限：可以查看自己的会话，但不能审计；
2. 运维授权权限：可以查看所有会话，但不能审计；
3. 审计权限：可以查看被授权的主机相关的会话，且可以审计；
4. 审计授权权限：可以查看所有会话，且可以审计。
"""
allow_uid = 0
allow_hids = list()
allow_all = False
user = handler.get_current_user()
if (user['privilege'] & TP_PRIVILEGE_OPS_AUZ) != 0 or (
user['privilege'] & TP_PRIVILEGE_AUDIT_AUZ) != 0:
allow_all = True
if not allow_all:
if (user['privilege'] & TP_PRIVILEGE_OPS) != 0:
allow_uid = user.id
if (user['privilege'] & TP_PRIVILEGE_AUDIT) != 0:
s = SQL(get_db())
s.select_from('audit_map', [
'u_id', 'h_id', 'p_state', 'policy_auth_type', 'u_state',
'gu_state'
],
alt_name='a')
s.where(
'a.u_id={user_id} AND '
'a.p_state={enable_state} AND'
'('
'((a.policy_auth_type={U2H} OR a.policy_auth_type={U2HG}) AND a.u_state={enable_state}) OR '
'((a.policy_auth_type={UG2H} OR a.policy_auth_type={UG2HG}) AND a.u_state={enable_state} AND a.gu_state={enable_state})'
')'.format(enable_state=TP_STATE_NORMAL,
user_id=user.id,
U2H=TP_POLICY_AUTH_USER_HOST,
U2HG=TP_POLICY_AUTH_USER_gHOST,
UG2H=TP_POLICY_AUTH_gUSER_HOST,
UG2HG=TP_POLICY_AUTH_gUSER_gHOST))
err = s.query()
if err != TPE_OK:
return err, 0, []
for h in s.recorder:
if h.h_id not in allow_hids:
allow_hids.append(h.h_id)
if len(allow_hids) == 0:
return TPE_OK, 0, []
if allow_uid == 0 and len(allow_hids) == 0:
return TPE_FAILED, 0, []
s = SQL(get_db())
s.select_from('record', [
'id', 'sid', 'user_id', 'host_id', 'acc_id', 'state', 'user_username',
'user_surname', 'host_ip', 'conn_ip', 'conn_port', 'client_ip',
'acc_username', 'protocol_type', 'protocol_sub_type', 'time_begin',
'time_end'
],
alt_name='r')
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'state':
_where.append('r.state IN ({})'.format(','.join(
[str(state) for state in sql_restrict[k]])))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'state':
_where.append('r.state NOT IN ({})'.format(','.join(
[str(state) for state in sql_exclude[k]])))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'state':
_where.append('r.state={}'.format(sql_filter[k]))
# elif k == 'search_record':
# _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))
if not allow_all:
if allow_uid != 0:
_where.append('r.user_id={uid}'.format(uid=allow_uid))
if len(allow_hids) > 0:
hids = [str(h) for h in allow_hids]
_where.append('r.host_id IN ({hids})'.format(hids=','.join(hids)))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'id' == sql_order['name']:
s.order_by('r.id', _sort)
elif 'time_begin' == sql_order['name']:
s.order_by('r.time_begin', _sort)
elif 'sid' == sql_order['name']:
s.order_by('r.sid', _sort)
# elif 'cid' == sql_order['name']:
# s.order_by('h.cid', _sort)
# elif 'state' == sql_order['name']:
# s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.recorder
def get_records(handler, sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
"""
获取会话列表
会话审计列表的显示策略（下列的`审计`操作指为会话做标记、置为保留状态、写备注等）：
1. 运维权限：可以查看自己的会话，但不能审计；
2. 运维授权权限：可以查看所有会话，但不能审计；
3. 审计权限：可以查看被授权的主机相关的会话，且可以审计；
4. 审计授权权限：可以查看所有会话，且可以审计。
"""
allow_uid = 0
allow_hids = list()
allow_all = False
user = handler.get_current_user()
if (user['privilege'] & TP_PRIVILEGE_OPS_AUZ) != 0 or (user['privilege'] & TP_PRIVILEGE_AUDIT_AUZ) != 0:
allow_all = True
if not allow_all:
if (user['privilege'] & TP_PRIVILEGE_OPS) != 0:
allow_uid = user.id
if (user['privilege'] & TP_PRIVILEGE_AUDIT) != 0:
s = SQL(get_db())
s.select_from('audit_map', ['u_id', 'h_id', 'p_state', 'policy_auth_type', 'u_state', 'gu_state'], alt_name='a')
s.where(
'a.u_id={user_id} AND '
'a.p_state={enable_state} AND'
'('
'((a.policy_auth_type={U2H} OR a.policy_auth_type={U2HG}) AND a.u_state={enable_state}) OR '
'((a.policy_auth_type={UG2H} OR a.policy_auth_type={UG2HG}) AND a.u_state={enable_state} AND a.gu_state={enable_state})'
')'.format(enable_state=TP_STATE_NORMAL, user_id=user.id, U2H=TP_POLICY_AUTH_USER_HOST, U2HG=TP_POLICY_AUTH_USER_gHOST, UG2H=TP_POLICY_AUTH_gUSER_HOST, UG2HG=TP_POLICY_AUTH_gUSER_gHOST))
err = s.query()
if err != TPE_OK:
return err, 0, []
for h in s.recorder:
if h.h_id not in allow_hids:
allow_hids.append(h.h_id)
if len(allow_hids) == 0:
return TPE_OK, 0, []
if allow_uid == 0 and len(allow_hids) == 0:
return TPE_FAILED, 0, []
s = SQL(get_db())
s.select_from('record', ['id', 'sid', 'user_id', 'host_id', 'acc_id', 'state', 'user_username', 'user_surname', 'host_ip', 'conn_ip', 'conn_port', 'client_ip', 'acc_username', 'protocol_type', 'protocol_sub_type', 'time_begin', 'time_end'], alt_name='r')
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'state':
_where.append('r.state IN ({})'.format(','.join([str(state) for state in sql_restrict[k]])))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'state':
_where.append('r.state NOT IN ({})'.format(','.join([str(state) for state in sql_exclude[k]])))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'state':
_where.append('r.state={}'.format(sql_filter[k]))
# elif k == 'search_record':
# _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))
if not allow_all:
if allow_uid != 0:
_where.append('r.user_id={uid}'.format(uid=allow_uid))
if len(allow_hids) > 0:
hids = [str(h) for h in allow_hids]
_where.append('r.host_id IN ({hids})'.format(hids=','.join(hids)))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'id' == sql_order['name']:
s.order_by('r.id', _sort)
elif 'time_begin' == sql_order['name']:
s.order_by('r.time_begin', _sort)
elif 'sid' == sql_order['name']:
s.order_by('r.sid', _sort)
# elif 'cid' == sql_order['name']:
# s.order_by('h.cid', _sort)
# elif 'state' == sql_order['name']:
# s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.recorder
def get_all_remotes(handler, sql_filter, sql_order, sql_limit):
s = SQL(get_db())
s.select_from('host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'state':
_where.append('h.state={}'.format(sql_filter[k]))
elif k == 'search':
_where.append('(h.name LIKE "%{k}%" OR h.ip LIKE "%{k}%" OR h.router_ip LIKE "%{k}%")'.format(k=sql_filter[k]))
elif k == 'host_group':
shg = SQL(get_db())
shg.select_from('group_map', ['mid'], alt_name='g')
shg.where('g.type={} AND g.gid={}'.format(TP_GROUP_HOST, sql_filter[k]))
err = shg.query()
if err != TPE_OK:
return err, 0, 1, []
if len(shg.recorder) == 0:
return TPE_OK, 0, 1, []
h_list = ','.join([str(i['mid']) for i in shg.recorder])
_where.append('h.id IN ({})'.format(h_list))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'id' == sql_order['name']:
s.order_by('h.id', _sort)
elif 'ip' == sql_order['name']:
s.order_by('h.ip', _sort)
elif 'name' == sql_order['name']:
s.order_by('h.name', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.page_index, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
if err != TPE_OK:
return err, 0, 1, []
ret = s.recorder
for h in ret:
h['h_id'] = h.id
h['h_state'] = TP_STATE_NORMAL
h['gh_state'] = TP_STATE_NORMAL
h['h_name'] = h.name
del h['id']
del h['name']
h['accounts_'] = []
sa = SQL(get_db())
sa.select_from('acc', ['id', 'protocol_type', 'protocol_port', 'username'], alt_name='a')
sa.where('a.host_id={}'.format(h.h_id))
sa.order_by('a.username', True)
err = sa.query()
if err != TPE_OK:
continue
for a in sa.recorder:
h['accounts_'].append({
'a_name': a.username,
'id': a.id,
'a_id': a.id,
'policy_auth_type': TP_POLICY_AUTH_USER_ACC,
'uni_id': 'none',
'a_state': TP_STATE_NORMAL,
'ga_state': TP_STATE_NORMAL,
'protocol_type': a.protocol_type,
'h_id': h.h_id,
'policy_': {
'flag_ssh': TP_FLAG_ALL,
'flag_rdp': TP_FLAG_ALL
}
})
# print(json.dumps(s.recorder, indent=' '))
return err, s.total_count, s.page_index, s.recorder
def get_group_with_member(sql_filter, sql_order, sql_limit):
"""
获取用户组列表，以及每个组的总成员数以及不超过5个的成员
"""
db = get_db()
# 首先获取要查询的组的信息
sg = SQL(db)
sg.select_from('group', ['id', 'name', 'state', 'desc'], alt_name='g')
_where = list()
_where.append('g.type={}'.format(TP_GROUP_ACCOUNT))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(g.name LIKE "%{}%" OR g.desc LIKE "%{}%")'.format(
sql_filter[k], sql_filter[k]))
elif k == 'state':
_where.append(
'(g.state={filter})'.format(filter=sql_filter[k]))
if len(_where) > 0:
sg.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
sg.order_by('g.name', _sort)
elif 'state' == sql_order['name']:
sg.order_by('g.state', _sort)
else:
log.e('unknown order field.\n')
return TPE_PARAM, sg.total_count, 0, sg.recorder
if len(sql_limit) > 0:
sg.limit(sql_limit['page_index'], sql_limit['per_page'])
err = sg.query()
if err != TPE_OK or len(sg.recorder) == 0:
return err, sg.total_count, 0, sg.recorder
for g in sg.recorder:
g['member_count'] = 0
g['members'] = []
g['_mid'] = [] # 临时使用，构建此组的前5个成员的id
# 对于本次要返回的用户组，取其中每一个组内成员的基本信息（id/用户名/真实名称等）
groups = [g['id'] for g in sg.recorder]
sgm = SQL(get_db())
sgm.select_from('group_map', ['gid', 'mid'], alt_name='gm')
# sgm.limit(0, 5)
_where = list()
# _where.append('({})'.format(' OR '.join(['gm.gid={}'.format(gid) for gid in groups])))
_where.append('gm.type={}'.format(TP_GROUP_ACCOUNT))
_where.append('gm.gid IN ({})'.format(','.join(
[str(gid) for gid in groups])))
str_where = '( {} )'.format(' AND '.join(_where))
sgm.where(str_where)
err = sgm.query()
if err != TPE_OK or len(sgm.recorder) == 0:
return err, sg.total_count, 0, sg.recorder
for g in sg.recorder:
for gm in sgm.recorder:
if gm['gid'] == g['id']:
g['member_count'] += 1
if len(g['_mid']) < 5:
g['_mid'].append(gm['mid'])
# 将得到的账号id合并到列表中并去重，然后获取这些账号的信息
users = []
for g in sg.recorder:
users.extend(g['_mid'])
users = list(set(users))
su = SQL(get_db())
# su.select_from('acc', ['id', 'host_ip', 'router_ip', 'router_port', 'username', 'protocol_type'], alt_name='a')
su.select_from('acc', ['id', 'host_id', 'username', 'protocol_type'],
alt_name='a')
su.where('a.id IN ({})'.format(','.join([str(uid) for uid in users])))
su.order_by('a.username')
err = su.query()
if err != TPE_OK or len(su.recorder) == 0:
return err, sg.total_count, 0, sg.recorder
# 得到主机id列表，然后查询相关主机的详细信息
host_ids = []
for _acc in su.recorder:
if _acc.host_id not in host_ids:
host_ids.append(_acc.host_id)
s_host = SQL(db)
s_host.select_from(
'host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'],
alt_name='h')
str_host_ids = ','.join([str(i) for i in host_ids])
s_host.where('h.id IN ({ids})'.format(ids=str_host_ids))
err = s_host.query()
if err != TPE_OK:
return err, sg.total_count, 0, sg.recorder
hosts = {}
for _host in s_host.recorder:
if _host.id not in hosts:
hosts[_host.id] = _host
for _acc in su.recorder:
_acc['_host'] = hosts[_acc.host_id]
# 现在可以将具体的用户信息追加到组信息中了
for g in sg.recorder:
for u in su.recorder:
for m in g['_mid']:
if u['id'] == m:
g['members'].append(u)
return err, sg.total_count, sg.page_index, sg.recorder
def get_hosts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
db = get_db()
_tp = db.table_prefix
_ph = db.place_holder
s = SQL(get_db())
s.select_from('host', ['id', 'type', 'os_type', 'os_ver', 'name', 'ip', 'router_ip', 'router_port', 'state', 'acc_count', 'cid', 'desc'], alt_name='h')
str_where = ''
_where = list()
_sql_v = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_where.append('h.id IN (SELECT `mid` FROM `{tp}group_map` WHERE `type`={ph} AND gid={ph})'.format(tp=_tp, ph=_ph))
_sql_v.append(TP_GROUP_HOST)
_sql_v.append(sql_restrict[k])
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append('h.id NOT IN (SELECT `mid` FROM `{tp}group_map` WHERE `gid`={ph} AND `type`={ph})'.format(tp=_tp, ph=_ph))
_sql_v.append(sql_exclude[k])
_sql_v.append(TP_GROUP_HOST)
elif k == 'ops_policy_id':
_where.append('h.id NOT IN (SELECT `rid` FROM `{tp}ops_auz` WHERE `policy_id`={ph} AND `rtype`={ph})'.format(tp=_tp, ph=_ph))
_sql_v.append(sql_exclude[k])
_sql_v.append(TP_HOST)
elif k == 'auditee_policy_id':
_where.append('h.id NOT IN (SELECT `rid` FROM `{tp}audit_auz` WHERE `policy_id`={ph} AND `type`={ph} AND `rtype`={ph})'.format(tp=_tp, ph=_ph))
_sql_v.append(sql_exclude[k])
_sql_v.append(TP_POLICY_ASSET)
_sql_v.append(TP_HOST)
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'state':
_where.append('h.state={ph}'.format(ph=_ph))
_sql_v.append(sql_filter[k])
elif k == 'search':
# _where.append('(h.name LIKE "%{filter}%" OR h.ip LIKE "%{filter}%" OR h.router_ip LIKE "%{filter}%" OR h.desc LIKE "%{filter}%" OR h.cid LIKE "%{filter}%")'.format(filter=sql_filter[k]))
_where.append('(h.name LIKE {ph} OR h.ip LIKE {ph} OR h.router_ip LIKE {ph} OR h.desc LIKE {ph} OR h.cid LIKE {ph})'.format(ph=_ph))
_f = '%{filter}%'.format(filter=sql_filter[k])
_sql_v.extend([_f, ] * 5)
elif k == 'host_group':
shg = SQL(db)
shg.select_from('group_map', ['mid'], alt_name='g')
shg.where('g.type={ph} AND g.gid={ph}'.format(ph=_ph))
err = shg.query((TP_GROUP_HOST, sql_filter[k]))
if err != TPE_OK:
return err, 0, 1, []
if len(shg.recorder) == 0:
return TPE_OK, 0, 1, []
h_list = ','.join([str(i['mid']) for i in shg.recorder])
_where.append('h.id IN ({})'.format(h_list))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'ip' == sql_order['name']:
s.order_by('h.ip', _sort)
elif 'name' == sql_order['name']:
s.order_by('h.name', _sort)
elif 'os_type' == sql_order['name']:
s.order_by('h.os_type', _sort)
elif 'cid' == sql_order['name']:
s.order_by('h.cid', _sort)
elif 'state' == sql_order['name']:
s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.page_index, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query(_sql_v)
return err, s.total_count, s.page_index, s.recorder
