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_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_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_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_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_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_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_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_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'], 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_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_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_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