def get_policies(sql_filter, sql_order, sql_limit): dbtp = get_db().table_prefix s = SQL(get_db()) s.select_from('ops_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 sql_order is not None: # _sort = False if not sql_order['asc'] else True # if 'rank' == sql_order['name']: # s.order_by('p.rank', _sort) # elif 'name' == sql_order['name']: # s.order_by('p.name', _sort) # elif 'state' == sql_order['name']: # s.order_by('p.state', _sort) # else: # log.e('unknown order field: {}\n'.format(sql_order['name'])) # return TPE_PARAM, s.total_count, 0, 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 rank_reorder(handler, pid, new_rank, start_rank, end_rank, direct): db = get_db() # 调节顺序: # 由pid获取被移动的策略,得到其rank,即,p_rank # p_rank > new_rank,向前移动 # 所有 new_rank <= rank < p_rank 的条目,其rank+1 # p_rank < new_rank,向后移动 # 所有 new_rank >= rank > p_rank 的条目,其rank-1 # 最后令pid条目的rank为new_rank # 1. 判断此账号是否已经存在 s = SQL(db) err = s.select_from('audit_policy', ['id', 'name', 'rank']).where( 'audit_policy.id={}'.format(pid)).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS p_name = s.recorder[0]['name'] p_rank = s.recorder[0]['rank'] sql = 'UPDATE `{dbtp}audit_policy` SET rank=rank{direct} WHERE (rank>={start_rank} AND rank<={end_rank});' \ ''.format(dbtp=db.table_prefix, direct=direct, start_rank=start_rank, end_rank=end_rank) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE sql = 'UPDATE `{dbtp}audit_policy` SET rank={new_rank} WHERE id={pid};' \ ''.format(dbtp=db.table_prefix, new_rank=new_rank, pid=pid) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "调整审计授权策略顺序:{},从{}到{}".format(p_name, p_rank, new_rank)) # return TPE_OK return policy.rebuild_audit_auz_map()
def set_flags(self, policy_id, flag_record, flag_rdp, flag_ssh): db = get_db() # 1. 判断此账号是否已经存在 s = SQL(db) err = s.select_from('ops_policy', ['id']).where( 'ops_policy.id={}'.format(policy_id)).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS sql = 'UPDATE `{}ops_policy` SET flag_record={flag_record}, flag_rdp={flag_rdp}, flag_ssh={flag_ssh} WHERE id={p_id};' \ ''.format(db.table_prefix, flag_record=flag_record, flag_rdp=flag_rdp, flag_ssh=flag_ssh, p_id=policy_id ) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE return TPE_OK
def update_policy(handler, args): db = get_db() # 1. 判断此账号是否已经存在 s = SQL(db) err = s.reset().select_from('audit_policy', ['id']).where( 'audit_policy.id={}'.format(args['id'])).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS sql = 'UPDATE `{}audit_policy` SET `name`="{name}", `desc`="{desc}" WHERE `id`={p_id};' \ ''.format(db.table_prefix, name=args['name'], desc=args['desc'], p_id=args['id'] ) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE return TPE_OK
def create_user(handler, args): """ 创建一个用户账号 """ db = get_db() _time_now = tp_timestamp_utc_now() operator = handler.get_current_user() # 1. 判断此账号是否已经存在了 s = SQL(db) err = s.reset().select_from('user', ['id']).where( 'user.username="******"'.format(args['username'])).query() if err != TPE_OK: return err, 0 if len(s.recorder) > 0: return TPE_EXISTS, 0 _password = tp_password_generate_secret(args['password']) sql = 'INSERT INTO `{}user` (`type`, `auth_type`, `password`, `username`, `surname`, `role_id`, `state`, `email`, `creator_id`, `create_time`, `last_login`, `last_chpass`, `desc`) VALUES ' \ '(1, {auth_type}, "{password}", "{username}", "{surname}", {role}, {state}, "{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \ ''.format(db.table_prefix, auth_type=args['auth_type'], password=_password, username=args['username'], surname=args['surname'], role=args['role'], state=TP_STATE_NORMAL, email=args['email'], creator_id=operator['id'], create_time=_time_now, last_login=0, last_chpass=0, desc=args['desc']) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE, 0 _id = db.last_insert_id() syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "创建用户:{}".format(args['username'])) # calc count of users. err, cnt = s.reset().count('user') if err == TPE_OK: tp_stats().user_counter_change(cnt) return TPE_OK, _id
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 create_policy(handler, args): """ 创建一个授权策略 """ db = get_db() _time_now = tp_timestamp_utc_now() # 1. 判断此账号是否已经存在了 s = SQL(db) err = s.reset().select_from('audit_policy', ['id']).where( 'audit_policy.name="{}"'.format(args['name'])).query() if err != TPE_OK: return err, 0 if len(s.recorder) > 0: return TPE_EXISTS, 0 # 2. get total count sql = 'SELECT COUNT(*) FROM {}audit_policy'.format(db.table_prefix) db_ret = db.query(sql) if not db_ret or len(db_ret) == 0: return TPE_DATABASE, 0 rank = db_ret[0][0] + 1 sql = 'INSERT INTO `{}audit_policy` (`rank`, `name`, `desc`, `creator_id`, `create_time`) VALUES ' \ '({rank}, "{name}", "{desc}", {creator_id}, {create_time});' \ ''.format(db.table_prefix, rank=rank, name=args['name'], desc=args['desc'], creator_id=handler.get_current_user()['id'], create_time=_time_now) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE, 0 _id = db.last_insert_id() syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "创建审计授权策略:{}".format(args['name'])) return TPE_OK, _id
def add_members(handler, policy_id, policy_type, ref_type, members): # step 1: select exists rid. s = SQL(get_db()) s.select_from('audit_auz', ['rid'], alt_name='p') _where = list() _where.append('p.policy_id={}'.format(policy_id)) _where.append('p.type={}'.format(policy_type)) _where.append('p.rtype={}'.format(ref_type)) s.where('( {} )'.format(' AND '.join(_where))) err = s.query() if err != TPE_OK: return err exists_ids = [r['rid'] for r in s.recorder] operator = handler.get_current_user() db = get_db() _time_now = tp_timestamp_utc_now() sql = [] # for uid in members: # sql.append('INSERT INTO `{}group_map` (type, gid, mid) VALUES ({}, {}, {});'.format(db.table_prefix, gtype, gid, uid)) # print(args['members']) for m in members: if m['id'] in exists_ids: continue str_sql = 'INSERT INTO `{}audit_auz` (policy_id, type, rtype, rid, `name`, creator_id, create_time) VALUES ' \ '({pid}, {t}, {rtype}, {rid}, "{name}", {creator_id}, {create_time});' \ ''.format(db.table_prefix, pid=policy_id, t=policy_type, rtype=ref_type, rid=m['id'], name=m['name'], creator_id=operator['id'], create_time=_time_now) sql.append(str_sql) if db.transaction(sql): # return TPE_OK return policy.rebuild_audit_auz_map() else: return TPE_DATABASE
def add_members(handler, policy_id, policy_type, ref_type, members): # step 1: select exists rid. s = SQL(get_db()) s.select_from('audit_auz', ['rid'], alt_name='p') _where = list() _where.append('p.policy_id={}'.format(policy_id)) _where.append('p.type={}'.format(policy_type)) _where.append('p.rtype={}'.format(ref_type)) s.where('( {} )'.format(' AND '.join(_where))) err = s.query() if err != TPE_OK: return err exists_ids = [r['rid'] for r in s.recorder] operator = handler.get_current_user() db = get_db() _time_now = tp_timestamp_sec() sql = [] # for uid in members: # sql.append('INSERT INTO `{}group_map` (type, gid, mid) VALUES ({}, {}, {});'.format(db.table_prefix, gtype, gid, uid)) # print(args['members']) for m in members: if m['id'] in exists_ids: continue sql_s = 'INSERT INTO `{tp}audit_auz` (`policy_id`,`type`,`rtype`,`rid`,`name`,`creator_id`,`create_time`) VALUES ' \ '({ph},{ph},{ph},{ph},{ph},{ph},{ph});' \ ''.format(tp=db.table_prefix, ph=db.place_holder) sql_v = (policy_id, policy_type, ref_type, m['id'], m['name'], operator['id'], _time_now) sql.append({'s': sql_s, 'v': sql_v}) if db.transaction(sql): # return TPE_OK return policy.rebuild_audit_auz_map() else: return TPE_DATABASE
def set_password(handler, user_id, password): db = get_db() operator = handler.get_current_user() # print('----------', operator) # 1. get user info (user name) s = SQL(db) err = s.reset().select_from('user', ['username', 'surname']).where( 'user.id={}'.format(user_id)).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS name = s.recorder[0]['username'] surname = s.recorder[0]['surname'] if len(surname) == 0: surname = name sql = 'UPDATE `{}user` SET password="******" WHERE id={user_id};' \ ''.format(db.table_prefix, password=password, user_id=user_id) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE if operator['id'] == 0: syslog.sys_log({ 'username': name, 'surname': surname }, handler.request.remote_ip, TPE_OK, "用户 {} 通过邮件方式重置了密码".format(name)) else: syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "为用户 {} 手动重置了密码".format(name)) return TPE_OK
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个的成员 """ 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 remove_accounts(handler, host_id, acc_ids): """ 删除远程账号 """ db = get_db() acc_count = len(acc_ids) acc_ids = ','.join([str(uid) for uid in acc_ids]) s = SQL(db) # 1. 判断是否存在 s.select_from('host', ['name', 'ip', 'router_ip', 'router_port', 'acc_count'], alt_name='a') s.where('a.id={h_id}'.format(h_id=host_id, ids=acc_ids)) err = s.query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS _h_name = s.recorder[0].name _h_ip = s.recorder[0].ip _h_router_ip = s.recorder[0].router_ip _h_router_port = s.recorder[0].router_port s.reset().select_from('acc', ['username'], alt_name='a') s.where('a.host_id={h_id} AND a.id IN ({ids}) '.format(h_id=host_id, ids=acc_ids)) err = s.query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS acc_names = [] for a in s.recorder: acc_name = '{}@{}'.format(a.username, _h_ip) if len(_h_router_ip) > 0: acc_name += '(由{}:{}路由)'.format(_h_router_ip, _h_router_port) acc_names.append(acc_name) sql_list = [] sql = 'DELETE FROM `{}acc` WHERE host_id={} AND id IN ({});'.format( db.table_prefix, host_id, acc_ids) sql_list.append(sql) sql = 'DELETE FROM `{}group_map` WHERE type={} AND mid IN ({});'.format( db.table_prefix, TP_GROUP_ACCOUNT, acc_ids) sql_list.append(sql) # 更新主机相关账号数量 sql = 'UPDATE `{}host` SET acc_count=acc_count-{acc_count} WHERE id={host_id};'.format( db.table_prefix, acc_count=acc_count, host_id=host_id) sql_list.append(sql) sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({rid});'.format( db.table_prefix, rtype=TP_ACCOUNT, rid=acc_ids) sql_list.append(sql) sql = 'DELETE FROM `{}ops_map` WHERE a_id IN ({acc_id});'.format( db.table_prefix, acc_id=acc_ids) sql_list.append(sql) if not db.transaction(sql_list): return TPE_DATABASE # s.reset().select_from('host', ['acc_count'], alt_name='a') # s.where('a.id={h_id}'.format(h_id=host_id, ids=acc_ids)) # err = s.query() # if err != TPE_OK: # return err # if len(s.recorder) == 0: # return TPE_NOT_EXISTS syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除账号:{}".format(','.join(acc_names))) tp_stats().acc_counter_change(-1) return TPE_OK
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 rebuild_ops_auz_map(): _users = {} _hosts = {} _accs = {} _gusers = {} _ghosts = {} _gaccs = {} _groups = {} _policies = {} _p_users = {} _p_assets = {} _map = [] db = get_db() dbtp = db.table_prefix db.exec('DELETE FROM {}ops_map'.format(dbtp)) s = SQL(get_db()) # 加载所有策略 err = s.reset().select_from('ops_policy', ['id', 'rank', 'state'], alt_name='p').query() if err != TPE_OK: return err if 0 == len(s.recorder): return TPE_OK for i in s.recorder: _policies[i.id] = i # 加载所有的用户 err = s.reset().select_from('user', ['id', 'username', 'surname', 'state'], alt_name='u').query() if err != TPE_OK: return err if 0 == len(s.recorder): return TPE_OK for i in s.recorder: _users[i.id] = i # 加载所有的主机 err = s.reset().select_from( 'host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h').query() if err != TPE_OK: return err if 0 == len(s.recorder): return TPE_OK for i in s.recorder: _hosts[i.id] = i # 加载所有的账号 err = s.reset().select_from('acc', [ 'id', 'host_id', 'username', 'protocol_type', 'protocol_port', 'auth_type', 'state' ], alt_name='a').query() if err != TPE_OK: return err if 0 == len(s.recorder): return TPE_OK for i in s.recorder: _accs[i.id] = i # 加载所有的组 err = s.reset().select_from('group', ['id', 'type', 'state'], alt_name='g').query() if err != TPE_OK: return err for i in s.recorder: _groups[i.id] = i if i.type == TP_GROUP_USER: _gusers[i.id] = [] elif i.type == TP_GROUP_HOST: _ghosts[i.id] = [] elif i.type == TP_GROUP_ACCOUNT: _gaccs[i.id] = [] # 加载所有的组 err = s.reset().select_from('group_map', ['id', 'type', 'gid', 'mid'], alt_name='g').query() if err != TPE_OK: return err for g in s.recorder: if g.type == TP_GROUP_USER: # if g.gid not in _gusers: # _gusers[g.gid] = [] _gusers[g.gid].append(_users[g.mid]) elif g.type == TP_GROUP_HOST: # if g.gid not in _ghosts: # _ghosts[g.gid] = [] _ghosts[g.gid].append(_hosts[g.mid]) elif g.type == TP_GROUP_ACCOUNT: # if g.gid not in _gaccs: # _gaccs[g.gid] = [] _gaccs[g.gid].append(_accs[g.mid]) # 加载所有策略明细 err = s.reset().select_from('ops_auz', ['id', 'policy_id', 'type', 'rtype', 'rid'], alt_name='o').query() if err != TPE_OK: return err if 0 == len(s.recorder): return TPE_OK # 分解各个策略中操作者和被操作资产的信息 for i in s.recorder: if i.type == TP_POLICY_OPERATOR: if i.policy_id not in _p_users: _p_users[i.policy_id] = [] if i.rtype == TP_USER: u = _users[i.rid] _p_users[i.policy_id].append({ 'u_id': i.rid, 'u_state': u.state, 'gu_id': 0, 'gu_state': 0, 'u_name': u.username, 'u_surname': u.surname, 'auth_from_': 'USER' }) elif i.rtype == TP_GROUP_USER: for u in _gusers[i.rid]: _p_users[i.policy_id].append({ 'u_id': u.id, 'u_state': u.state, 'gu_id': i.rid, 'gu_state': _groups[i.rid].state, 'u_name': u.username, 'u_surname': u.surname, 'auth_from_': 'gUSER' }) else: log.e('invalid operator type.\n') return TPE_FAILED elif i.type == TP_POLICY_ASSET: if i.policy_id not in _p_assets: _p_assets[i.policy_id] = [] if i.rtype == TP_ACCOUNT: a = _accs[i.rid] h = _hosts[a.host_id] _p_assets[i.policy_id].append({ 'a_id': i.rid, 'a_state': a.state, 'ga_id': 0, 'ga_state': 0, 'h_id': h.id, 'h_state': h.state, 'gh_id': 0, 'gh_state': 0, 'a_name': a.username, 'protocol_type': a.protocol_type, 'protocol_port': a.protocol_port, 'h_name': h.name, 'ip': h.ip, 'router_ip': h.router_ip, 'router_port': h.router_port, 'auth_to_': 'ACC' }) elif i.rtype == TP_GROUP_ACCOUNT: for a in _gaccs[i.rid]: h = _hosts[a.host_id] _p_assets[i.policy_id].append({ 'a_id': a.id, 'a_state': a.state, 'ga_id': i.rid, 'ga_state': _groups[i.rid].state, 'h_id': h.id, 'h_state': h.state, 'gh_id': 0, 'gh_state': 0, 'a_name': a.username, 'protocol_type': a.protocol_type, 'protocol_port': a.protocol_port, 'h_name': h.name, 'ip': h.ip, 'router_ip': h.router_ip, 'router_port': h.router_port, 'auth_to_': 'gACC' }) elif i.rtype == TP_HOST: for aid in _accs: if _accs[aid].host_id == i.rid: a = _accs[aid] h = _hosts[i.rid] _p_assets[i.policy_id].append({ 'a_id': aid, 'a_state': a.state, 'ga_id': 0, 'ga_state': 0, 'h_id': h.id, 'h_state': h.state, 'gh_id': 0, 'gh_state': 0, 'a_name': a.username, 'protocol_type': a.protocol_type, 'protocol_port': a.protocol_port, 'h_name': h.name, 'ip': h.ip, 'router_ip': h.router_ip, 'router_port': h.router_port, 'auth_to_': 'HOST' }) elif i.rtype == TP_GROUP_HOST: for h in _ghosts[i.rid]: for aid in _accs: if _accs[aid].host_id == h.id: a = _accs[aid] _p_assets[i.policy_id].append({ 'a_id': aid, 'a_state': a.state, 'ga_id': 0, 'ga_state': 0, 'h_id': h.id, 'h_state': h.state, 'gh_id': i.rid, 'gh_state': _groups[i.rid].state, 'a_name': a.username, 'protocol_type': a.protocol_type, 'protocol_port': a.protocol_port, 'h_name': h.name, 'ip': h.ip, 'router_ip': h.router_ip, 'router_port': h.router_port, 'auth_to_': 'gHOST' }) else: log.e('invalid asset type.\n') return TPE_FAILED else: return TPE_FAILED # 3. 建立所有一一对应的映射关系 for pid in _policies: if pid not in _p_users: continue for u in _p_users[pid]: if pid not in _p_assets: continue for a in _p_assets[pid]: x = AttrDict() x.update({ 'p_id': pid, 'p_rank': _policies[pid].rank, 'p_state': _policies[pid].state }) x.update(u) x.update(a) x.uni_id = '{}-{}-{}-{}-{}-{}-{}'.format( x.p_id, x.gu_id, x.u_id, x.gh_id, x.h_id, x.ga_id, x.a_id) x.ua_id = 'u{}-a{}'.format(x.u_id, x.a_id) x.policy_auth_type = TP_POLICY_AUTH_UNKNOWN if u['auth_from_'] == 'USER' and a['auth_to_'] == 'ACC': x.policy_auth_type = TP_POLICY_AUTH_USER_ACC elif u['auth_from_'] == 'USER' and a['auth_to_'] == 'gACC': x.policy_auth_type = TP_POLICY_AUTH_USER_gACC elif u['auth_from_'] == 'USER' and a['auth_to_'] == 'HOST': x.policy_auth_type = TP_POLICY_AUTH_USER_HOST elif u['auth_from_'] == 'USER' and a['auth_to_'] == 'gHOST': x.policy_auth_type = TP_POLICY_AUTH_USER_gHOST elif u['auth_from_'] == 'gUSER' and a['auth_to_'] == 'ACC': x.policy_auth_type = TP_POLICY_AUTH_gUSER_ACC elif u['auth_from_'] == 'gUSER' and a['auth_to_'] == 'gACC': x.policy_auth_type = TP_POLICY_AUTH_gUSER_gACC elif u['auth_from_'] == 'gUSER' and a['auth_to_'] == 'HOST': x.policy_auth_type = TP_POLICY_AUTH_gUSER_HOST elif u['auth_from_'] == 'gUSER' and a['auth_to_'] == 'gHOST': x.policy_auth_type = TP_POLICY_AUTH_gUSER_gHOST _map.append(x) if len(_map) == 0: return TPE_OK values = [] for i in _map: v = '("{uni_id}","{ua_id}",{p_id},{p_rank},{p_state},{policy_auth_type},{u_id},{u_state},{gu_id},{gu_state},{h_id},{h_state},{gh_id},{gh_state},{a_id},{a_state},{ga_id},{ga_state},' \ '"{u_name}","{u_surname}","{h_name}","{ip}","{router_ip}",{router_port},"{a_name}",{protocol_type},{protocol_port})' \ ''.format(uni_id=i.uni_id, ua_id=i.ua_id, p_id=i.p_id, p_rank=i.p_rank, p_state=i.p_state, policy_auth_type=i.policy_auth_type, u_id=i.u_id, u_state=i.u_state, gu_id=i.gu_id, gu_state=i.gu_state, h_id=i.h_id, h_state=i.h_state, gh_id=i.gh_id, gh_state=i.gh_state, a_id=i.a_id, a_state=i.a_state, ga_id=i.ga_id, ga_state=i.ga_state, u_name=i.u_name, u_surname=i.u_surname, h_name=i.h_name, ip=i.ip, router_ip=i.router_ip, router_port=i.router_port, a_name=i.a_name, protocol_type=i.protocol_type, protocol_port=i.protocol_port) values.append(v) sql = 'INSERT INTO `{dbtp}ops_map` (uni_id,ua_id,p_id,p_rank,p_state,policy_auth_type,u_id,u_state,gu_id,gu_state,h_id,h_state,gh_id,gh_state,a_id,a_state,ga_id,ga_state,' \ 'u_name,u_surname,h_name,ip,router_ip,router_port,a_name,protocol_type,protocol_port) VALUES \n{values};' \ ''.format(dbtp=dbtp, values=',\n'.join(values)) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE return TPE_OK
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_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 cleanup_storage(handler): # storage config sto = tp_cfg().sys.storage db = get_db() _now = tp_timestamp_sec() msg = [] have_error = False s = SQL(db) chk_time = _now - sto.keep_log * 24 * 60 * 60 if sto.keep_log > 0: # find out all sys-log to be remove s.select_from('syslog', ['id'], alt_name='s') s.where('s.log_time<{chk_time}'.format(chk_time=chk_time)) err = s.query() if err != TPE_OK: have_error = True msg.append('清理系统日志时发生错误:无法获取系统日志信息!') # return err, msg else: removed_log = len(s.recorder) if 0 == removed_log: msg.append('没有满足条件的系统日志需要清除!') else: s.reset().delete_from('syslog').where( 'log_time<{chk_time}'.format(chk_time=chk_time)) err = s.exec() if err != TPE_OK: have_error = True msg.append('清理系统日志时发生错误:无法清除指定的系统日志!') else: msg.append('{} 条系统日志已清除!'.format(removed_log)) if sto.keep_record > 0: core_cfg = tp_cfg().core if not core_cfg.detected: have_error = True msg.append('清除指定会话录像失败:未能检测到核心服务!') else: replay_path = core_cfg.replay_path if not os.path.exists(replay_path): have_error = True msg.append('清除指定会话录像失败:会话录像路径不存在({})!'.format(replay_path)) else: # find out all record to be remove s.reset().select_from('record', ['id', 'protocol_type'], alt_name='r') s.where('r.time_begin<{chk_time}'.format(chk_time=chk_time)) err = s.query() if err != TPE_OK: have_error = True msg.append('清除指定会话录像失败:无法获取会话录像信息!') elif len(s.recorder) == 0: msg.append('没有满足条件的会话录像需要清除!') else: record_removed = 0 for r in s.recorder: if r.protocol_type == TP_PROTOCOL_TYPE_RDP: path_remove = os.path.join(replay_path, 'rdp', '{:09d}'.format(r.id)) elif r.protocol_type == TP_PROTOCOL_TYPE_SSH: path_remove = os.path.join(replay_path, 'ssh', '{:09d}'.format(r.id)) elif r.protocol_type == TP_PROTOCOL_TYPE_TELNET: path_remove = os.path.join(replay_path, 'telnet', '{:09d}'.format(r.id)) else: have_error = True msg.append('会话录像记录编号 {},未知远程访问协议!'.format(r.id)) continue if os.path.exists(path_remove): # print('remove path', path_remove) try: shutil.rmtree(path_remove) except: have_error = True msg.append('会话录像记录 {} 清除失败,无法删除目录 {}!'.format( r.id, path_remove)) ss = SQL(db) ss.delete_from('record').where( 'id={rid}'.format(rid=r.id)) ss.exec() record_removed += 1 msg.append('{} 条会话录像数据已清除!'.format(record_removed)) if have_error: return TPE_FAILED, msg else: return TPE_OK, msg
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 create_users(handler, user_list, success, failed): """ 批量创建用户 """ db = get_db() _time_now = tp_timestamp_sec() operator = handler.get_current_user() name_list = list() s = SQL(db) for i in range(len(user_list)): user = user_list[i] if 'type' not in user: user['type'] = TP_USER_TYPE_LOCAL if 'ldap_dn' not in user: user['ldap_dn'] = '' err = s.reset().select_from('user', ['id']).where( 'user.username="******"'.format(user['username'])).query() if err != TPE_OK: failed.append({'line': user['_line'], 'error': '数据库查询失败'}) if len(s.recorder) > 0: failed.append({ 'line': user['_line'], 'error': '账号 `{}` 已经存在'.format(user['username']) }) continue if user['type'] == TP_USER_TYPE_LOCAL: _password = tp_password_generate_secret(user['password']) else: _password = '' sql = 'INSERT INTO `{}user` (' \ '`role_id`, `username`, `surname`, `type`, `ldap_dn`, `auth_type`, `password`, ' \ '`state`, `email`, `creator_id`, `create_time`, `last_login`, `last_chpass`, `desc`' \ ') VALUES (' \ '0, "{username}", "{surname}", {user_type}, "{ldap_dn}", 0, "{password}", ' \ '{state}, "{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \ ''.format(db.table_prefix, username=user['username'], surname=user['surname'], user_type=user['type'], ldap_dn=user['ldap_dn'], password=_password, state=TP_STATE_NORMAL, email=user['email'], creator_id=operator['id'], create_time=_time_now, last_login=0, last_chpass=_time_now, desc=user['desc']) db_ret = db.exec(sql) if not db_ret: failed.append({'line': user['_line'], 'error': '写入数据库时发生错误'}) continue success.append(user['username']) name_list.append(user['username']) user['_id'] = db.last_insert_id() if len(name_list) > 0: syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "批量导入方式创建用户:{}".format(','.join(name_list))) # tp_stats().user_counter_change(len(name_list)) # calc count of users. err, cnt = s.reset().count('user') if err == TPE_OK: tp_stats().user_counter_change(cnt)
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 remove_users(handler, users): db = get_db() s = SQL(db) str_users = ','.join([str(i) for i in users]) # 1. 获取用户名称,用于记录系统日志 where = 'u.id IN ({})'.format(str_users) err = s.select_from('user', ['username'], alt_name='u').where(where).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS str_names = ','.join([n['username'] for n in s.recorder]) sql_list = [] # 将用户从所在组中移除 sql_s = 'DELETE FROM `{tp}group_map` WHERE `type`={ph} AND `mid` IN ({ids});' \ ''.format(tp=db.table_prefix, ph=db.place_holder, ids=str_users) sql_v = (TP_GROUP_USER, ) sql_list.append({'s': sql_s, 'v': sql_v}) # 删除用户 sql_s = 'DELETE FROM `{tp}user` WHERE `id` IN ({ids});'.format( tp=db.table_prefix, ids=str_users) sql_list.append({'s': sql_s, 'v': None}) # 将用户从运维授权中移除 sql_s = 'DELETE FROM `{tp}ops_auz` WHERE `rtype`={ph} AND `rid` IN ({ids});' \ ''.format(tp=db.table_prefix, ph=db.place_holder, ids=str_users) sql_v = (TP_USER, ) sql_list.append({'s': sql_s, 'v': sql_v}) sql_s = 'DELETE FROM `{tp}ops_map` WHERE `u_id` IN ({ids});'.format( tp=db.table_prefix, ids=str_users) sql_list.append({'s': sql_s, 'v': None}) # 将用户从审计授权中移除 sql_s = 'DELETE FROM `{tp}audit_auz` WHERE `rtype`={ph} AND `rid` IN ({ids});' \ ''.format(tp=db.table_prefix, ph=db.place_holder, ids=str_users) sql_v = (TP_USER, ) sql_list.append({'s': sql_s, 'v': sql_v}) sql_s = 'DELETE FROM `{tp}audit_map` WHERE `u_id` IN ({ids});'.format( tp=db.table_prefix, ids=str_users) sql_list.append({'s': sql_s, 'v': None}) if not db.transaction(sql_list): return TPE_DATABASE syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除用户:{}".format(str_names)) # calc count of users. err, cnt = s.reset().count('user') if err == TPE_OK: tp_stats().user_counter_change(cnt) return TPE_OK
def get_remotes(handler, sql_filter, sql_order, sql_limit): """ 获取当前登录用户的可以远程登录的主机(及账号) 远程连接列表的显示策略: 1. 运维权限:可以使用被授权的远程账号进行远程连接; 2. 运维授权权限:可以使用所有的远程账号进行远程连接。 步骤: 1. 查询满足条件的项(用户->账号),按授权策略顺序排序 2. 在此基础上选出非重复的(用户->账号)关系项 3. 继续在上一步基础上选出非重复的主机项 4. 为每一个主机查询满足条件的账号项 """ operator = handler.get_current_user() if (operator['privilege'] & TP_PRIVILEGE_OPS_AUZ) != 0: return get_all_remotes(handler, sql_filter, sql_order, sql_limit) db = get_db() ###################################################### # step 1. ###################################################### s1 = [] s1.append('SELECT * FROM {}ops_map'.format(db.table_prefix)) s1_where = [] s1_where.append('u_id={}'.format(operator.id)) s1_where.append('p_state={state}'.format(state=TP_STATE_NORMAL)) s1.append('WHERE ({})'.format(') AND ('.join(s1_where))) s1.append('ORDER BY p_rank DESC') sql_1 = ' '.join(s1) ###################################################### # step 2. ###################################################### sql_2 = 'SELECT * FROM ({}) AS s1 GROUP BY ua_id'.format(sql_1) _f = ['id', 'p_id', 'h_id', 'h_state', 'gh_state', 'h_name', 'ip', 'router_ip', 'router_port'] ###################################################### # step 3. ###################################################### _where = list() if len(sql_filter) > 0: for k in sql_filter: # if k == 'state': # _where.append('h.state={}'.format(sql_filter[k])) # el if k == 'search': ss = SQL(get_db()) ss.select_from('host', ['id'], alt_name='h') ss.where('(h.name LIKE "%{k}%" OR h.ip LIKE "%{k}%" OR h.router_ip LIKE "%{k}%")'.format(k=sql_filter[k])) err = ss.query() if err != TPE_OK: return err, 0, 1, [] if len(ss.recorder) == 0: return TPE_OK, 0, 1, [] h_list = ','.join([str(i['id']) for i in ss.recorder]) _where.append('(h_id IN ({}))'.format(h_list)) 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_NOT_EXISTS, 0, 1, [] h_list = ','.join([str(i['mid']) for i in shg.recorder]) _where.append('(h_id IN ({}))'.format(h_list)) str_where = '' if len(_where) > 0: str_where = 'WHERE ( {} )'.format(' AND '.join(_where)) sql_counter = [] sql_counter.append('SELECT COUNT(*)') sql_counter.append('FROM') sql_counter.append('({}) AS s3'.format(sql_2)) sql_counter.append(str_where) sql_counter.append('GROUP BY h_id') sql_counter.append(';') db_ret = db.query(' '.join(sql_counter)) if db_ret is None or len(db_ret) == 0: return TPE_OK, 0, 1, [] total = len(db_ret) if total == 0: return TPE_OK, 0, 1, [] if total < sql_limit['page_index'] * sql_limit['per_page']: sql_limit['page_index'] = 0 sql = [] sql.append('SELECT {}'.format(','.join(_f))) sql.append('FROM') sql.append('({}) AS s2'.format(sql_2)) sql.append(str_where) sql.append('GROUP BY h_id') sql.append('ORDER BY ip') sql.append('LIMIT {},{}'.format(sql_limit['page_index'] * sql_limit['per_page'], sql_limit['per_page'])) sql.append(';') ret_recorder = [] # 用于构建最终返回的数据 h_ids = [] # 涉及到的主机的ID列表 db_ret = db.query(' '.join(sql)) if db_ret is None: return TPE_OK, 0, 1, [] for db_item in db_ret: item = AttrDict() for i in range(len(_f)): item[_f[i]] = db_item[i] item.accounts_ = [] ret_recorder.append(item) h_ids.append(item.h_id) ###################################################### # step 4. ###################################################### host_ids = [str(i) for i in h_ids] s4 = [] s4.append('SELECT * FROM {}ops_map'.format(db.table_prefix)) s4_where = [] s4_where.append('u_id={}'.format(operator.id)) s4_where.append('p_state={state}'.format(state=TP_STATE_NORMAL)) s4_where.append('h_id IN ({})'.format(','.join(host_ids))) s4.append('WHERE ({})'.format(') AND ('.join(s4_where))) s4.append('ORDER BY p_rank DESC') sql_4 = ' '.join(s4) sql = [] _f = ['id', 'uni_id', 'policy_auth_type', 'p_id', 'h_id', 'a_id', 'a_state', 'ga_state', 'a_name', 'protocol_type'] sql.append('SELECT {}'.format(','.join(_f))) sql.append('FROM') sql.append('({}) AS s4'.format(sql_4)) sql.append('GROUP BY ua_id') sql.append(';') db_ret = db.query(' '.join(sql)) if db_ret is None: return TPE_OK, 0, 1, [] p_ids = [] # 涉及到的策略的ID列表 for db_item in db_ret: item = AttrDict() for i in range(len(_f)): item[_f[i]] = db_item[i] if item.p_id not in p_ids: p_ids.append(item.p_id) for j in range(len(ret_recorder)): if ret_recorder[j].h_id == item.h_id: ret_recorder[j].accounts_.append(item) # 查询所有相关的授权策略的详细信息 # print('p-ids:', p_ids) policy_ids = [str(i) for i in p_ids] _f = ['id', 'flag_rdp', 'flag_ssh'] sql = [] sql.append('SELECT {}'.format(','.join(_f))) sql.append('FROM {}ops_policy'.format(db.table_prefix)) sql.append('WHERE id IN ({})'.format(','.join(policy_ids))) sql.append(';') db_ret = db.query(' '.join(sql)) # print('', db_ret) for db_item in db_ret: item = AttrDict() for i in range(len(_f)): item[_f[i]] = db_item[i] for i in range(len(ret_recorder)): for j in range(len(ret_recorder[i].accounts_)): if ret_recorder[i].accounts_[j].p_id == item.id: ret_recorder[i].accounts_[j].policy_ = item # print(json.dumps(ret_recorder, indent=' ')) return TPE_OK, total, sql_limit['page_index'], ret_recorder
def get_role_list(): s = SQL(get_db()) s.select_from('role', ['id', 'name', 'privilege'], alt_name='r') err = s.query() return err, s.recorder
def remove_hosts(handler, hosts): db = get_db() host_ids = ','.join([str(i) for i in hosts]) sql_list = [] # step 1. 处理主机对应的账号 # 1.1 获取账号列表 s = SQL(db) s.select_from('acc', ['id', 'host_ip', 'router_ip', 'router_port', 'username'], alt_name='a') s.where('a.host_id IN ({})'.format(host_ids)) err = s.query() if err != TPE_OK: return err accs = [] acc_names = [] for acc in s.recorder: if str(acc['id']) not in accs: accs.append(str(acc['id'])) acc_name = '{}@{}'.format(acc['username'], acc['host_ip']) if len(acc['router_ip']) > 0: acc_name += '(由{}:{}路由)'.format(acc['router_ip'], acc['router_port']) if acc_name not in acc_names: acc_names.append(acc_name) acc_ids = ','.join([i for i in accs]) if len(accs) > 0: # 1.2 将账号从所在组中移除 where = 'mid IN ({})'.format(acc_ids) sql = 'DELETE FROM `{}group_map` WHERE (type={} AND {});'.format( db.table_prefix, TP_GROUP_ACCOUNT, where) sql_list.append(sql) # if not db.exec(sql): # return TPE_DATABASE # 1.3 将账号删除 where = 'id IN ({})'.format(acc_ids) sql = 'DELETE FROM `{}acc` WHERE {};'.format(db.table_prefix, where) sql_list.append(sql) # if not db.exec(sql): # return TPE_DATABASE sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({rid});'.format( db.table_prefix, rtype=TP_ACCOUNT, rid=acc_ids) sql_list.append(sql) sql = 'DELETE FROM `{}ops_map` WHERE a_id IN ({acc_ids});'.format( db.table_prefix, acc_ids=acc_ids) sql_list.append(sql) # step 2. 处理主机 s = SQL(db) s.select_from('host', ['ip', 'router_ip', 'router_port'], alt_name='h') s.where('h.id IN ({})'.format(host_ids)) err = s.query() if err != TPE_OK: return err host_names = [] for h in s.recorder: h_name = h['ip'] if len(h['router_ip']) > 0: h_name += '(由{}:{}路由)'.format(h['router_ip'], h['router_port']) if h_name not in host_names: host_names.append(h_name) # 2.1 将主机从所在组中移除 where = 'mid IN ({})'.format(host_ids) sql = 'DELETE FROM `{}group_map` WHERE (type={} AND {});'.format( db.table_prefix, TP_GROUP_HOST, where) sql_list.append(sql) # 2.2 将主机删除 where = 'id IN ({})'.format(host_ids) sql = 'DELETE FROM `{}host` WHERE {};'.format(db.table_prefix, where) sql_list.append(sql) sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({rid});'.format( db.table_prefix, rtype=TP_HOST, rid=host_ids) sql_list.append(sql) sql = 'DELETE FROM `{}ops_map` WHERE h_id IN ({host_ids});'.format( db.table_prefix, host_ids=host_ids) sql_list.append(sql) if not db.transaction(sql_list): return TPE_DATABASE if len(acc_names) > 0: syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除账号:{}".format(','.join(acc_names))) tp_stats().acc_counter_change(0 - len(acc_names)) if len(host_names) > 0: syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除主机:{}".format(','.join(host_names))) tp_stats().host_counter_change(0 - len(host_names)) return TPE_OK
def remove(handler, gtype, glist): if gtype not in TP_GROUP_TYPES: return TPE_PARAM group_ids = ','.join([str(i) for i in glist]) # 1. 获取组的名称,用于记录系统日志 where = 'g.type={gtype} AND g.id IN ({gids})'.format(gtype=gtype, gids=group_ids) db = get_db() s = SQL(db) err = s.select_from('group', ['name'], alt_name='g').where(where).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS name_list = [n['name'] for n in s.recorder] sql_list = [] # 删除组与成员的映射关系 sql = 'DELETE FROM `{tpdp}group_map` WHERE `type`={t} AND `gid` IN ({ids});'.format( tpdp=db.table_prefix, t=gtype, ids=group_ids) sql_list.append(sql) # where = 'type={} AND gid IN ({})'.format(gtype, ','.join(group_list)) # err = s.reset().delete_from('group_map').where(where).exec() # if err != TPE_OK: # return err # 删除组 sql = 'DELETE FROM `{tpdp}group` WHERE `type`={t} AND `id` IN ({ids});'.format( tpdp=db.table_prefix, t=gtype, ids=group_ids) sql_list.append(sql) # where = 'type={gtype} AND id IN ({gids})'.format(gtype=gtype, gids=','.join(group_list)) # err = s.reset().delete_from('group').where(where).exec() # if err != TPE_OK: # return err if gtype == TP_GROUP_USER: gname = 'gu' elif gtype == TP_GROUP_HOST: gname = 'gh' elif gtype == TP_GROUP_ACCOUNT: gname = 'ga' else: return TPE_PARAM # 将组从运维授权中移除 sql = 'DELETE FROM `{}ops_auz` WHERE `rtype`={rtype} AND `rid` IN ({ids});'.format( db.table_prefix, rtype=gtype, ids=group_ids) sql_list.append(sql) sql = 'DELETE FROM `{}ops_map` WHERE `{gname}_id` IN ({ids});'.format( db.table_prefix, gname=gname, ids=group_ids) sql_list.append(sql) # 将组从审计授权中移除 sql = 'DELETE FROM `{}audit_auz` WHERE `rtype`={rtype} AND `rid` IN ({ids});'.format( db.table_prefix, rtype=gtype, ids=group_ids) sql_list.append(sql) # 注意,审计授权映射表中,没有远程账号相关信息,所以如果是远程账号组,则忽略 if gtype != TP_GROUP_ACCOUNT: sql = 'DELETE FROM `{}audit_map` WHERE `{gname}_id` IN ({ids});'.format( db.table_prefix, gname=gname, ids=group_ids) sql_list.append(sql) if not db.transaction(sql_list): return TPE_DATABASE # 记录系统日志 syslog.sys_log( handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除{gtype}:{gname}".format(gtype=TP_GROUP_TYPES[gtype], gname=','.join(name_list))) return TPE_OK
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