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 update_oath_secret(handler, user_id, oath_secret): db = get_db() s = SQL(db) err = s.select_from('user', ['username', 'surname'], alt_name='u').where( 'u.id={user_id}'.format(user_id=user_id)).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS username = s.recorder[0].username surname = s.recorder[0].surname sql = 'UPDATE `{dbtp}user` SET oath_secret="{secret}" WHERE id={user_id}' \ ''.format(dbtp=db.table_prefix, secret=oath_secret, user_id=user_id) if db.exec(sql): if len(oath_secret) > 0: syslog.sys_log({ 'username': username, 'surname': surname }, handler.request.remote_ip, TPE_OK, "用户 {} 更新了身份认证器绑定信息".format(username)) else: syslog.sys_log({ 'username': username, 'surname': surname }, handler.request.remote_ip, TPE_OK, "用户 {} 清除了身份认证器绑定信息".format(username)) return TPE_OK else: return TPE_DATABASE
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_by_username(username): s = SQL(get_db()) s.select_from('user', [ 'id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn', 'password', 'oath_secret', 'role_id', 'state', 'fail_count', 'lock_time', 'email', 'create_time', 'last_login', 'last_ip', 'last_chpass', 'mobile', 'qq', 'wechat', 'valid_from', 'valid_to', 'desc' ], alt_name='u') s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'}) s.where('u.username="******"'.format(username)) err = s.query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS, {} if s.recorder[0]['privilege'] is None: s.recorder[0]['privilege'] = 0 return TPE_OK, s.recorder[0]
def get_users_by_type(_type): s = SQL(get_db()) err = s.select_from('user', ['id', 'type', 'ldap_dn'], alt_name='u').where('u.type={}'.format(_type)).query() if err != TPE_OK: return None if len(s.recorder) == 0: return None return s.recorder
def create_users(handler, user_list, success, failed): """ 批量创建用户 """ db = get_db() _time_now = tp_timestamp_utc_now() 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_all_hosts_for_check_state(): """查询所有主机""" s = SQL(get_db()) s.select_from('host', ['ip', 'router_ip'], alt_name='h') err = s.query() if err != TPE_OK: return None return s.recorder
def get_user_info(user_id): """ 获取一个指定的用户的详细信息,包括关联的角色的详细信息、所属组的详细信息等等 """ s = SQL(get_db()) s.select_from('user', [ 'id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn', 'password', 'oath_secret', 'role_id', 'state', 'fail_count', 'lock_time', 'email', 'create_time', 'last_login', 'last_ip', 'last_chpass', 'mobile', 'qq', 'wechat', 'desc' ], alt_name='u') s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'}) s.where('u.id="{}"'.format(user_id)) err = s.query() if err != TPE_OK: return err, {} if len(s.recorder) == 0: return TPE_NOT_EXISTS, {} return TPE_OK, s.recorder[0]
def create_user(handler, user): """ 创建一个用户账号 """ db = get_db() _time_now = tp_timestamp_sec() operator = handler.get_current_user() if 'type' not in user: user['type'] = TP_USER_TYPE_LOCAL if 'ldap_dn' not in user: user['ldap_dn'] = '' # 1. 判断此账号是否已经存在了 s = SQL(db) err = s.reset().select_from('user', ['id']).where( 'user.username="******"'.format(user['username'])).query() if err != TPE_OK: return err, 0 if len(s.recorder) > 0: return TPE_EXISTS, 0 # _password = tp_password_generate_secret(user['password']) 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`, `valid_from`, `valid_to`, `desc`' \ ') VALUES (' \ '{role}, "{username}", "{surname}", {user_type}, "{ldap_dn}", {auth_type}, "{password}", {state}, ' \ '"{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, {valid_from}, '\ '{valid_to}, "{desc}");' \ ''.format(db.table_prefix, role=user['role'], username=user['username'], surname=user['surname'], user_type=user['type'], ldap_dn=user['ldap_dn'], auth_type=user['auth_type'], password=_password, state=TP_STATE_NORMAL, email=user['email'], creator_id=operator['id'], create_time=_time_now, last_login=0, last_chpass=_time_now, valid_from=user['valid_from'], valid_to=user['valid_to'], desc=user['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(user['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 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 = 'DELETE FROM `{tpdp}group_map` WHERE type={t} AND mid IN ({ids});' \ ''.format(tpdp=db.table_prefix, t=TP_GROUP_USER, ids=str_users) sql_list.append(sql) # 删除用户 sql = 'DELETE FROM `{tpdp}user` WHERE id IN ({ids});'.format( tpdp=db.table_prefix, ids=str_users) sql_list.append(sql) # 将用户从运维授权中移除 sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({ids});' \ ''.format(db.table_prefix, rtype=TP_USER, ids=str_users) sql_list.append(sql) sql = 'DELETE FROM `{}ops_map` WHERE u_id IN ({ids});'.format( db.table_prefix, ids=str_users) sql_list.append(sql) # 将用户从审计授权中移除 sql = 'DELETE FROM `{}audit_auz` WHERE rtype={rtype} AND rid IN ({ids});' \ ''.format(db.table_prefix, rtype=TP_USER, ids=str_users) sql_list.append(sql) sql = 'DELETE FROM `{}audit_map` WHERE u_id IN ({ids});'.format( db.table_prefix, ids=str_users) 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, "删除用户:{}".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 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('ops_policy', ['id', 'name', 'rank']).where( 'ops_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'] # if p_rank > new_rank: # compare = '>' # if insert_before: # compare = '>=' # sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank+1 WHERE (rank{compare}{new_rank} AND rank<{p_rank});' \ # ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank) # else: # compare = '<' # if insert_before: # compare = '<=' # sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank-1 WHERE (rank{compare}{new_rank} AND rank>{p_rank});' \ # ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank) sql = 'UPDATE `{dbtp}ops_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}ops_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 policy.rebuild_ops_auz_map()
def remove_members(handler, policy_id, policy_type, ids): s = SQL(get_db()) auz_ids = [str(i) for i in ids] # 将用户从所在组中移除 where = 'policy_id={} AND type={} AND id IN ({})'.format(policy_id, policy_type, ','.join(auz_ids)) err = s.reset().delete_from('ops_auz').where(where).exec() if err != TPE_OK: return err #return TPE_OK return policy.rebuild_ops_auz_map()
def create_user(handler, user): """ 创建一个用户账号 """ db = get_db() _time_now = tp_timestamp_utc_now() operator = handler.get_current_user() if 'type' not in user: user['type'] = TP_USER_TYPE_LOCAL if 'ldap_dn' not in user: user['ldap_dn'] = '' # 1. 判断此账号是否已经存在了 s = SQL(db) err = s.reset().select_from('user', ['id']).where('user.username="******"'.format(user['username'])).query() if err != TPE_OK: return err, 0 if len(s.recorder) > 0: return TPE_EXISTS, 0 # _password = tp_password_generate_secret(user['password']) 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 (' \ '{role}, "{username}", "{surname}", {user_type}, "{ldap_dn}", {auth_type}, "{password}", {state}, ' \ '"{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \ ''.format(db.table_prefix, role=user['role'], username=user['username'], surname=user['surname'], user_type=user['type'], ldap_dn=user['ldap_dn'], auth_type=user['auth_type'], 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: return TPE_DATABASE, 0 _id = db.last_insert_id() syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "创建用户:{}".format(user['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 create_users(handler, user_list, success, failed): """ 批量创建用户 """ db = get_db() _time_now = tp_timestamp_utc_now() operator = handler.get_current_user() name_list = list() s = SQL(db) for i in range(len(user_list)): user = user_list[i] 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 _password = tp_password_generate_secret(user['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, 0, "{password}", "{username}", "{surname}", 0, {state}, "{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \ ''.format(db.table_prefix, username=user['username'], surname=user['surname'], password=_password, state=TP_STATE_NORMAL, email=user['email'], creator_id=operator['id'], create_time=_time_now, last_login=0, last_chpass=0, 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_auth(auth_id): db = get_db() s = SQL(db) err = s.select_from('ops_map', ['id', 'p_id', 'h_id', 'u_id', 'a_id']).where('ops_map.uni_id="{}"'.format(auth_id)).query() if err != TPE_OK: return None, err if len(s.recorder) == 0: return None, TPE_NOT_EXISTS # if len(s.recorder) != 1: # return None, TPE_FAILED # log.v(s.recorder[0]) return s.recorder[0], TPE_OK
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('ops_policy', ['id', 'name', 'rank']).where('ops_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'] # if p_rank > new_rank: # compare = '>' # if insert_before: # compare = '>=' # sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank+1 WHERE (rank{compare}{new_rank} AND rank<{p_rank});' \ # ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank) # else: # compare = '<' # if insert_before: # compare = '<=' # sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank-1 WHERE (rank{compare}{new_rank} AND rank>{p_rank});' \ # ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank) sql = 'UPDATE `{dbtp}ops_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}ops_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 policy.rebuild_ops_auz_map()
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 = 'DELETE FROM `{tpdp}group_map` WHERE type={t} AND mid IN ({ids});' \ ''.format(tpdp=db.table_prefix, t=TP_GROUP_USER, ids=str_users) sql_list.append(sql) # 删除用户 sql = 'DELETE FROM `{tpdp}user` WHERE id IN ({ids});'.format(tpdp=db.table_prefix, ids=str_users) sql_list.append(sql) # 将用户从运维授权中移除 sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({ids});' \ ''.format(db.table_prefix, rtype=TP_USER, ids=str_users) sql_list.append(sql) sql = 'DELETE FROM `{}ops_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users) sql_list.append(sql) # 将用户从审计授权中移除 sql = 'DELETE FROM `{}audit_auz` WHERE rtype={rtype} AND rid IN ({ids});' \ ''.format(db.table_prefix, rtype=TP_USER, ids=str_users) sql_list.append(sql) sql = 'DELETE FROM `{}audit_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users) 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, "删除用户:{}".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 set_password(handler, mode, 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 _time_now = tp_timestamp_sec() sql = 'UPDATE `{}user` SET `password`="{password}", `last_chpass`={last_chpass} WHERE `id`={user_id};' \ ''.format(db.table_prefix, password=password, last_chpass=_time_now, user_id=user_id) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE if mode in [3, 4, 5, 6]: if mode == 6: syslog.sys_log({ 'username': name, 'surname': surname }, handler.request.remote_ip, TPE_OK, "用户 {} 修改了过期的密码".format(name)) else: 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 remove_role(handler, role_id): db = get_db() s = SQL(db) # 1. 判断是否存在 s.select_from('role', ['name'], alt_name='r') s.where('r.id={rid}'.format(rid=role_id)) err = s.query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS role_name = s.recorder[0].name sql_list = list() sql = 'DELETE FROM `{tp}role` WHERE `id`={ph};'.format(tp=db.table_prefix, ph=db.place_holder) sql_list.append({'s': sql, 'v': (role_id, )}) # 更新此角色相关的用户信息 sql = 'UPDATE `{tp}user` SET `role_id`=0 WHERE `role_id`={ph};'.format( tp=db.table_prefix, ph=db.place_holder) sql_list.append({'s': sql, 'v': (role_id, )}) if not db.transaction(sql_list): return TPE_DATABASE syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除角色:{}".format(role_name)) return TPE_OK
def add_members(handler, policy_id, policy_type, ref_type, members): # step 1: select exists rid. s = SQL(get_db()) s.select_from('ops_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 m in members: if m['id'] in exists_ids: continue sql_s = 'INSERT INTO `{tp}ops_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_ops_auz_map() else: return TPE_DATABASE
def get_list(gtype): s = SQL(get_db()) s.select_from('group', ['id', 'name'], alt_name='g') s.where('g.type={}'.format(gtype)) err = s.query() return err, s.recorder
def get_host_accounts(host_id): # 获取指定主机的所有账号 s = SQL(get_db()) # s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a') s.select_from('acc', ['id', 'state', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt'], alt_name='a') s.where('a.host_id={}'.format(host_id)) s.order_by('a.username', True) err = s.query() return err, s.recorder
def get_host_accounts(host_id): # 获取指定主机的所有账号 s = SQL(get_db()) # s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a') s.select_from('acc', [ 'id', 'state', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt' ], alt_name='a') s.where('a.host_id={}'.format(host_id)) s.order_by('a.username', True) err = s.query() return err, s.recorder
def generate_reset_password_token(handler, user_id): db = get_db() operator = handler.get_current_user() s = SQL(db) _time_now = tp_timestamp_utc_now() # 0. query user's email by user_id err = s.select_from('user', ['email'], alt_name='u').where('u.id={user_id}'.format(user_id=user_id)).query() if err != TPE_OK: return err, None, None if len(s.recorder) == 0: return TPE_DATABASE, None, None email = s.recorder[0].email # 1. clean all timed out tokens. s.reset().delete_from('user_rpt').where('create_time<{}'.format(_time_now - 24 * 60 * 60)).exec() # 2. find out if this user already have a token. err = s.reset().select_from('user_rpt', ['id'], alt_name='u').where('u.user_id={}'.format(user_id)).query() if err != TPE_OK: return err, None, None token = tp_generate_random(16) if len(s.recorder) == 0: sql = 'INSERT INTO `{dbtp}user_rpt` (user_id, token, create_time) VALUES ' \ '({user_id}, "{token}", {create_time});' \ ''.format(dbtp=db.table_prefix, user_id=user_id, token=token, create_time=_time_now) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE, None, None else: sql = 'UPDATE `{dbtp}user_rpt` SET token="{token}", create_time={create_time} WHERE user_id={user_id};' \ ''.format(dbtp=db.table_prefix, token=token, create_time=_time_now, user_id=user_id) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE, None, None # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "为用户 {} 手动重置了密码".format(name)) return TPE_OK, email, token
def update_policy(handler, args): db = get_db() # 1. 判断此账号是否已经存在 s = SQL(db) err = s.reset().select_from('ops_policy', ['id']).where('ops_policy.id={}'.format(args['id'])).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS sql = 'UPDATE `{}ops_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 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 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_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 remove_role(handler, role_id): db = get_db() s = SQL(db) # 1. 判断是否存在 s.select_from('role', ['name'], alt_name='r') s.where('r.id={rid}'.format(rid=role_id)) err = s.query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS role_name = s.recorder[0].name sql_list = [] sql = 'DELETE FROM `{}role` WHERE id={};'.format(db.table_prefix, role_id) sql_list.append(sql) # 更新此角色相关的用户信息 sql = 'UPDATE `{}user` SET role_id=0 WHERE role_id={rid};'.format(db.table_prefix, rid=role_id) 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, "删除角色:{}".format(role_name)) return TPE_OK
def update_oath_secret(handler, user_id, oath_secret): db = get_db() s = SQL(db) err = s.select_from('user', ['username', 'surname'], alt_name='u').where( 'u.id={user_id}'.format(user_id=user_id)).query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS username = s.recorder[0].username surname = s.recorder[0].surname sql = 'UPDATE `{dbtp}user` SET oath_secret="{secret}" WHERE id={user_id}' \ ''.format(dbtp=db.table_prefix, secret=oath_secret, user_id=user_id) if db.exec(sql): syslog.sys_log({'username': username, 'surname': surname}, handler.request.remote_ip, TPE_OK, "用户 {} 绑定了身份认证器".format(username)) return TPE_OK else: return TPE_DATABASE
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 get_host_info(host_id): s = SQL(get_db()) s.select_from('host', ['id', 'name', 'type', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h') s.where('h.id={}'.format(host_id)) err = s.query() if err != TPE_OK: return err, None if len(s.recorder) != 1: return TPE_DATABASE, None return TPE_OK, s.recorder[0]
def get_by_id(gtype, gid): # 获取要查询的组的信息 s = SQL(get_db()) s.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g') s.where('g.type={} AND g.id={}'.format(gtype, gid)) err = s.query() if err != TPE_OK: return err, {} if len(s.recorder) == 0: return TPE_NOT_EXISTS, {} return TPE_OK, s.recorder[0]
def get_by_id(pid): s = SQL(get_db()) s.select_from('ops_policy', ['id', 'name', 'desc', 'flag_record', 'flag_rdp', 'flag_ssh', 'flag_telnet'], alt_name='p') s.where('p.id={}'.format(pid)) err = s.query() if err != TPE_OK: return err, {} # if len(s.recorder) == 0: # return TPE_NOT_EXISTS, {} return TPE_OK, s.recorder[0]
def get_by_id(pid): s = SQL(get_db()) s.select_from('audit_policy', ['id', 'name', 'desc'], alt_name='p') s.where('p.id={}'.format(pid)) err = s.query() if err != TPE_OK: return err, {} if len(s.recorder) == 0: return TPE_NOT_EXISTS, {} return TPE_OK, s.recorder[0]
def get_by_username(username): s = SQL(get_db()) s.select_from('user', ['id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn', 'password', 'oath_secret', 'role_id', 'state', 'fail_count', 'lock_time', 'email', 'create_time', 'last_login', 'last_ip', 'last_chpass', 'mobile', 'qq', 'wechat', 'desc'], alt_name='u') s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'}) s.where('u.username="******"'.format(username)) err = s.query() if err != TPE_OK: return err if len(s.recorder) == 0: return TPE_NOT_EXISTS, {} if s.recorder[0]['privilege'] is None: s.recorder[0]['privilege'] = 0 return TPE_OK, s.recorder[0]
def create_policy(handler, args): """ 创建一个授权策略 """ db = get_db() _time_now = tp_timestamp_utc_now() # 1. 判断此账号是否已经存在了 s = SQL(db) err = s.reset().select_from('ops_policy', ['id']).where('ops_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 {}ops_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 `{}ops_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 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_host_info(host_id): s = SQL(get_db()) s.select_from('host', ['id', 'type', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h') s.where('h.id={}'.format(host_id)) err = s.query() if err != TPE_OK: return err, None if len(s.recorder) != 1: return TPE_DATABASE, None return TPE_OK, s.recorder[0]
def get_user_info(user_id): """ 获取一个指定的用户的详细信息,包括关联的角色的详细信息、所属组的详细信息等等 """ s = SQL(get_db()) s.select_from('user', ['id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn', 'password', 'oath_secret', 'role_id', 'state', 'fail_count', 'lock_time', 'email', 'create_time', 'last_login', 'last_ip', 'last_chpass', 'mobile', 'qq', 'wechat', 'desc'], alt_name='u') s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'}) s.where('u.id="{}"'.format(user_id)) err = s.query() if err != TPE_OK: return err, {} if len(s.recorder) == 0: return TPE_NOT_EXISTS, {} return TPE_OK, s.recorder[0]
def generate_reset_password_token(handler, user_id): db = get_db() operator = handler.get_current_user() s = SQL(db) _time_now = tp_timestamp_sec() # 0. query user's email by user_id err = s.select_from('user', ['email'], alt_name='u').where( 'u.id={user_id}'.format(user_id=user_id)).query() if err != TPE_OK: return err, None, None if len(s.recorder) == 0: return TPE_DATABASE, None, None email = s.recorder[0].email # 1. clean all timed out tokens. s.reset().delete_from('user_rpt').where( 'create_time<{}'.format(_time_now - 24 * 60 * 60)).exec() # 2. find out if this user already have a token. err = s.reset().select_from('user_rpt', ['id'], alt_name='u').where( 'u.user_id={}'.format(user_id)).query() if err != TPE_OK: return err, None, None token = tp_generate_random(16) if len(s.recorder) == 0: sql = 'INSERT INTO `{dbtp}user_rpt` (user_id, token, create_time) VALUES ' \ '({user_id}, "{token}", {create_time});' \ ''.format(dbtp=db.table_prefix, user_id=user_id, token=token, create_time=_time_now) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE, None, None else: sql = 'UPDATE `{dbtp}user_rpt` SET token="{token}", create_time={create_time} WHERE user_id={user_id};' \ ''.format(dbtp=db.table_prefix, token=token, create_time=_time_now, user_id=user_id) db_ret = db.exec(sql) if not db_ret: return TPE_DATABASE, None, None # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "为用户 {} 手动重置了密码".format(name)) return TPE_OK, email, token
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 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_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_host_groups_for_user(user_id, user_privilege): # get all host-groups for current logged in user. db = get_db() # step 0. return all host-groups if user have all host-group access privilege if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE | TP_PRIVILEGE_ASSET_GROUP)) != 0: s = SQL(get_db()) s.select_from('group', ['id', 'name'], alt_name='g') s.where('g.type={}'.format(TP_GROUP_HOST)) s.order_by('g.name') err = s.query() return err, s.recorder # step 1. get all hosts which could be access by this user. sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format( dbtp=db.table_prefix, dbph=db.place_holder) db_ret = db.query(sql, (user_id, )) if db_ret is None or len(db_ret) == 0: return TPE_NOT_EXISTS, None hosts = [] for db_item in db_ret: hosts.append(str(db_item[0])) if len(hosts) == 0: return TPE_NOT_EXISTS, None # step 2. get groups which include those hosts. sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format( dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts)) db_ret = db.query(sql) if db_ret is None or len(db_ret) == 0: return TPE_NOT_EXISTS, None groups = [] for db_item in db_ret: groups.append(str(db_item[0])) # step 3. get those groups id and name. s = SQL(get_db()) s.select_from('group', ['id', 'name'], alt_name='g') s.where('g.id IN ({})'.format(','.join(groups))) s.order_by('g.name') err = s.query() return err, s.recorder
def get_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_host_groups_for_user(user_id, user_privilege): # get all host-groups for current logged in user. db = get_db() # step 0. return all host-groups if user have all host-group access privilege if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE | TP_PRIVILEGE_ASSET_GROUP)) != 0: s = SQL(get_db()) s.select_from('group', ['id', 'name'], alt_name='g') s.where('g.type={}'.format(TP_GROUP_HOST)) s.order_by('g.name') err = s.query() return err, s.recorder # step 1. get all hosts which could be access by this user. sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format(dbtp=db.table_prefix, dbph=db.place_holder) db_ret = db.query(sql, (user_id, )) if db_ret is None or len(db_ret) == 0: return TPE_NOT_EXISTS, None hosts = [] for db_item in db_ret: hosts.append(str(db_item[0])) if len(hosts) == 0: return TPE_NOT_EXISTS, None # step 2. get groups which include those hosts. sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format(dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts)) db_ret = db.query(sql) if db_ret is None or len(db_ret) == 0: return TPE_NOT_EXISTS, None groups = [] for db_item in db_ret: groups.append(str(db_item[0])) # step 3. get those groups id and name. s = SQL(get_db()) s.select_from('group', ['id', 'name'], alt_name='g') s.where('g.id IN ({})'.format(','.join(groups))) s.order_by('g.name') err = s.query() return err, s.recorder
def cleanup_storage(handler): # storage config sto = tp_cfg().sys.storage db = get_db() _now = tp_timestamp_utc_now() 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_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 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