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_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 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 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 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_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_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_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_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 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
