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_policies(sql_filter, sql_order, sql_limit):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'],
alt_name='p')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.
format(filter=sql_filter[k]))
if k == 'state':
_where.append('p.state={}'.format(sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, s.total_count, 0, s.recorder
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
s.order_by('p.rank', True)
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_policies(sql_filter, sql_order, sql_limit):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'], alt_name='p')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
if k == 'state':
_where.append('p.state={}'.format(sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, s.total_count, 0, s.recorder
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
s.order_by('p.rank', True)
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def 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 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 get_account_info(acc_id):
s = SQL(get_db())
# s.select_from('acc', ['id', 'password', 'pri_key', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username'], alt_name='a')
s.select_from('acc', [
'id', 'password', 'pri_key', 'state', 'host_id', 'protocol_type',
'protocol_port', 'auth_type', 'username', 'username_prompt',
'password_prompt'
],
alt_name='a')
s.where('a.id={}'.format(acc_id))
err = s.query()
if err != TPE_OK:
return err, None
if len(s.recorder) != 1:
return TPE_DATABASE, None
sh = SQL(get_db())
sh.select_from('host',
['id', 'name', 'ip', 'router_ip', 'router_port', 'state'],
alt_name='h')
sh.where('h.id={}'.format(s.recorder[0].host_id))
err = sh.query()
if err != TPE_OK:
return err, None
if len(s.recorder) != 1:
return TPE_DATABASE, None
s.recorder[0]['_host'] = sh.recorder[0]
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 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_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_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_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(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_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_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_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_host_groups_for_user(user_id, user_privilege):
# get all host-groups for current logged in user.
db = get_db()
# step 0. return all host-groups if user have all host-group access privilege
if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE
| TP_PRIVILEGE_ASSET_GROUP)) != 0:
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(TP_GROUP_HOST))
s.order_by('g.name')
err = s.query()
return err, s.recorder
# step 1. get all hosts which could be access by this user.
sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format(
dbtp=db.table_prefix, dbph=db.place_holder)
db_ret = db.query(sql, (user_id, ))
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
hosts = []
for db_item in db_ret:
hosts.append(str(db_item[0]))
if len(hosts) == 0:
return TPE_NOT_EXISTS, None
# step 2. get groups which include those hosts.
sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format(
dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts))
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
groups = []
for db_item in db_ret:
groups.append(str(db_item[0]))
# step 3. get those groups id and name.
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.id IN ({})'.format(','.join(groups)))
s.order_by('g.name')
err = s.query()
return err, s.recorder
def get_host_accounts(host_id):
# 获取指定主机的所有账号
s = SQL(get_db())
# s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a')
s.select_from('acc', [
'id', 'state', 'protocol_type', 'protocol_port', 'auth_type',
'username', 'username_prompt', 'password_prompt'
],
alt_name='a')
s.where('a.host_id={}'.format(host_id))
s.order_by('a.username', True)
err = s.query()
return err, s.recorder
def 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_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_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_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_logs(sql_filter, sql_order, sql_limit):
s = SQL(get_db())
s.select_from('syslog', [
'id', 'user_name', 'user_surname', 'client_ip', 'code', 'log_time',
'message'
],
alt_name='l')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'log_user_name':
_where.append('l.user_name="{}"'.format(sql_filter[k]))
# elif k == 'search_record':
# _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'log_time' == sql_order['name']:
s.order_by('l.log_time', _sort)
# elif 'name' == sql_order['name']:
# s.order_by('h.name', _sort)
# elif 'os_type' == sql_order['name']:
# s.order_by('h.os_type', _sort)
# elif 'cid' == sql_order['name']:
# s.order_by('h.cid', _sort)
# elif 'state' == sql_order['name']:
# s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.recorder
def get_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 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 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 api_v1_get_host(hosts_ip):
ip_list = ','.join(['"{}"'.format(i) for i in hosts_ip])
db = get_db()
_tp = db.table_prefix
_ph = db.place_holder
s_host = SQL(get_db())
s_host.select_from('host', ['id', 'ip', 'os_type', 'name'], alt_name='h')
s_host.where('h.ip IN ({})'.format(ip_list))
err = s_host.query()
if err != TPE_OK:
return err, None
hid_list = list()
for h in s_host.recorder:
hid_list.append(h['id'])
host_list = ','.join([str(h) for h in hid_list])
s_acc = SQL(get_db())
s_acc.select_from('acc', ['id', 'host_id', 'username', 'protocol_type'], alt_name='a')
s_acc.where('a.host_id IN ({})'.format(host_list))
err = s_acc.query()
if err != TPE_OK:
return err, None
ret = dict()
for ip in hosts_ip:
ret[ip] = dict()
for h in s_host.recorder:
if h['ip'] == ip:
ret[ip]['id'] = h['id']
ret[ip]['os_type'] = h['os_type']
ret[ip]['name'] = h['name']
if len(ret[ip]['name']) == 0:
ret[ip]['name'] = ip
ret[ip]['account'] = list()
for a in s_acc.recorder:
if a['host_id'] == h['id']:
ret[ip]['account'].append({'id': a['id'], 'name': a['username'], 'protocol': a['protocol_type']})
return TPE_OK, ret
def get_logs(sql_filter, sql_order, sql_limit):
s = SQL(get_db())
s.select_from('syslog', ['id', 'user_name', 'user_surname', 'client_ip', 'code', 'log_time', 'message'], alt_name='l')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'log_user_name':
_where.append('l.user_name="{}"'.format(sql_filter[k]))
# elif k == 'search_record':
# _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'log_time' == sql_order['name']:
s.order_by('l.log_time', _sort)
# elif 'name' == sql_order['name']:
# s.order_by('h.name', _sort)
# elif 'os_type' == sql_order['name']:
# s.order_by('h.os_type', _sort)
# elif 'cid' == sql_order['name']:
# s.order_by('h.cid', _sort)
# elif 'state' == sql_order['name']:
# s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.recorder
def get_auditees(sql_filter, sql_order, sql_limit):
ss = SQL(get_db())
ss.select_from('audit_auz', ['id', 'policy_id', 'rtype', 'rid', 'name'],
alt_name='p')
_where = list()
_where.append('p.type=1')
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'policy_id':
# _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
_where.append('p.policy_id={}'.format(sql_filter[k]))
elif k == 'search':
_where.append(
'(p.name LIKE "%{filter}%")'.format(filter=sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, 0, 0, {}
if len(_where) > 0:
ss.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
ss.order_by('p.name', _sort)
elif 'rtype' == sql_order['name']:
ss.order_by('p.rtype', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, ss.total_count, 0, ss.recorder
if len(sql_limit) > 0:
ss.limit(sql_limit['page_index'], sql_limit['per_page'])
err = ss.query()
if err != TPE_OK:
return err, 0, 0, {}
# print(ss.recorder)
return TPE_OK, ss.total_count, ss.page_index, ss.recorder
def 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 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 get_asset(sql_filter, sql_order, sql_limit):
ss = SQL(get_db())
ss.select_from('ops_auz', ['id', 'policy_id', 'rtype', 'rid', 'name'], alt_name='p')
_where = list()
_where.append('p.type=1')
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'policy_id':
# _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
_where.append('p.policy_id={}'.format(sql_filter[k]))
elif k == 'search':
_where.append('(p.name LIKE "%{filter}%")'.format(filter=sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, 0, 0, {}
if len(_where) > 0:
ss.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
ss.order_by('p.name', _sort)
elif 'rtype' == sql_order['name']:
ss.order_by('p.rtype', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, ss.total_count, 0, ss.recorder
if len(sql_limit) > 0:
ss.limit(sql_limit['page_index'], sql_limit['per_page'])
err = ss.query()
if err != TPE_OK:
return err, 0, 0, {}
# print(ss.recorder)
return TPE_OK, ss.total_count, ss.page_index, ss.recorder
def get_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 get_account_info(acc_id):
s = SQL(get_db())
# s.select_from('acc', ['id', 'password', 'pri_key', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username'], alt_name='a')
s.select_from('acc', ['id', 'password', 'pri_key', 'state', 'host_id', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt'], alt_name='a')
s.where('a.id={}'.format(acc_id))
err = s.query()
if err != TPE_OK:
return err, None
if len(s.recorder) != 1:
return TPE_DATABASE, None
sh = SQL(get_db())
sh.select_from('host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')
sh.where('h.id={}'.format(s.recorder[0].host_id))
err = sh.query()
if err != TPE_OK:
return err, None
if len(s.recorder) != 1:
return TPE_DATABASE, None
s.recorder[0]['_host'] = sh.recorder[0]
return TPE_OK, s.recorder[0]
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 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 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 set_flags(self, policy_id, flag_record, flag_rdp, flag_ssh):
db = get_db()
# 1. 判断此账号是否已经存在
s = SQL(db)
err = s.select_from('ops_policy', ['id']).where('ops_policy.id={}'.format(policy_id)).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
sql = 'UPDATE `{}ops_policy` SET flag_record={flag_record}, flag_rdp={flag_rdp}, flag_ssh={flag_ssh} WHERE id={p_id};' \
''.format(db.table_prefix,
flag_record=flag_record, flag_rdp=flag_rdp, flag_ssh=flag_ssh, p_id=policy_id
)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE
return TPE_OK
def update_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 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
def remove_accounts(handler, host_id, acc_ids):
"""
删除远程账号
"""
db = get_db()
acc_count = len(acc_ids)
acc_ids = ','.join([str(uid) for uid in acc_ids])
s = SQL(db)
# 1. 判断是否存在
s.select_from('host', ['name', 'ip', 'router_ip', 'router_port', 'acc_count'], alt_name='a')
s.where('a.id={h_id}'.format(h_id=host_id, ids=acc_ids))
err = s.query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
_h_name = s.recorder[0].name
_h_ip = s.recorder[0].ip
_h_router_ip = s.recorder[0].router_ip
_h_router_port = s.recorder[0].router_port
s.reset().select_from('acc', ['username'], alt_name='a')
s.where('a.host_id={h_id} AND a.id IN ({ids}) '.format(h_id=host_id, ids=acc_ids))
err = s.query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
acc_names = []
for a in s.recorder:
acc_name = '{}@{}'.format(a.username, _h_ip)
if len(_h_router_ip) > 0:
acc_name += '（由{}:{}路由）'.format(_h_router_ip, _h_router_port)
acc_names.append(acc_name)
sql_list = []
sql = 'DELETE FROM `{}acc` WHERE host_id={} AND id IN ({});'.format(db.table_prefix, host_id, acc_ids)
sql_list.append(sql)
sql = 'DELETE FROM `{}group_map` WHERE type={} AND mid IN ({});'.format(db.table_prefix, TP_GROUP_ACCOUNT, acc_ids)
sql_list.append(sql)
# 更新主机相关账号数量
sql = 'UPDATE `{}host` SET acc_count=acc_count-{acc_count} WHERE id={host_id};'.format(db.table_prefix, acc_count=acc_count, host_id=host_id)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({rid});'.format(db.table_prefix, rtype=TP_ACCOUNT, rid=acc_ids)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_map` WHERE a_id IN ({acc_id});'.format(db.table_prefix, acc_id=acc_ids)
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
# s.reset().select_from('host', ['acc_count'], alt_name='a')
# s.where('a.id={h_id}'.format(h_id=host_id, ids=acc_ids))
# err = s.query()
# if err != TPE_OK:
# return err
# if len(s.recorder) == 0:
# return TPE_NOT_EXISTS
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除账号：{}".format('，'.join(acc_names)))
tp_stats().acc_counter_change(-1)
return TPE_OK
def remove_hosts(handler, hosts):
db = get_db()
host_ids = ','.join([str(i) for i in hosts])
sql_list = []
# step 1. 处理主机对应的账号
# 1.1 获取账号列表
s = SQL(db)
s.select_from('acc', ['id', 'host_ip', 'router_ip', 'router_port', 'username'], alt_name='a')
s.where('a.host_id IN ({})'.format(host_ids))
err = s.query()
if err != TPE_OK:
return err
accs = []
acc_names = []
for acc in s.recorder:
if str(acc['id']) not in accs:
accs.append(str(acc['id']))
acc_name = '{}@{}'.format(acc['username'], acc['host_ip'])
if len(acc['router_ip']) > 0:
acc_name += '（由{}:{}路由）'.format(acc['router_ip'], acc['router_port'])
if acc_name not in acc_names:
acc_names.append(acc_name)
acc_ids = ','.join([i for i in accs])
if len(accs) > 0:
# 1.2 将账号从所在组中移除
where = 'mid IN ({})'.format(acc_ids)
sql = 'DELETE FROM `{}group_map` WHERE (type={} AND {});'.format(db.table_prefix, TP_GROUP_ACCOUNT, where)
sql_list.append(sql)
# if not db.exec(sql):
# return TPE_DATABASE
# 1.3 将账号删除
where = 'id IN ({})'.format(acc_ids)
sql = 'DELETE FROM `{}acc` WHERE {};'.format(db.table_prefix, where)
sql_list.append(sql)
# if not db.exec(sql):
# return TPE_DATABASE
sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({rid});'.format(db.table_prefix, rtype=TP_ACCOUNT, rid=acc_ids)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_map` WHERE a_id IN ({acc_ids});'.format(db.table_prefix, acc_ids=acc_ids)
sql_list.append(sql)
# step 2. 处理主机
s = SQL(db)
s.select_from('host', ['ip', 'router_ip', 'router_port'], alt_name='h')
s.where('h.id IN ({})'.format(host_ids))
err = s.query()
if err != TPE_OK:
return err
host_names = []
for h in s.recorder:
h_name = h['ip']
if len(h['router_ip']) > 0:
h_name += '（由{}:{}路由）'.format(h['router_ip'], h['router_port'])
if h_name not in host_names:
host_names.append(h_name)
# 2.1 将主机从所在组中移除
where = 'mid IN ({})'.format(host_ids)
sql = 'DELETE FROM `{}group_map` WHERE (type={} AND {});'.format(db.table_prefix, TP_GROUP_HOST, where)
sql_list.append(sql)
# 2.2 将主机删除
where = 'id IN ({})'.format(host_ids)
sql = 'DELETE FROM `{}host` WHERE {};'.format(db.table_prefix, where)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({rid});'.format(db.table_prefix, rtype=TP_HOST, rid=host_ids)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_map` WHERE h_id IN ({host_ids});'.format(db.table_prefix, host_ids=host_ids)
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
if len(acc_names) > 0:
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除账号：{}".format('，'.join(acc_names)))
tp_stats().acc_counter_change(0 - len(acc_names))
if len(host_names) > 0:
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除主机：{}".format('，'.join(host_names)))
tp_stats().host_counter_change(0 - len(host_names))
return TPE_OK
def get_hosts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
s = SQL(get_db())
s.select_from('host', ['id', 'type', 'os_type', 'os_ver', 'name', 'ip', 'router_ip', 'router_port', 'state', 'acc_count', 'cid', 'desc'], alt_name='h')
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_where.append('h.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(get_db().table_prefix, TP_GROUP_HOST, sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append('h.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'.format(get_db().table_prefix, TP_GROUP_HOST, sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append('h.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'.format(dbtp=get_db().table_prefix, pid=sql_exclude[k], rtype=TP_HOST))
elif k == 'auditee_policy_id':
_where.append('h.id NOT IN (SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} AND `type`={ptype} AND rtype={rtype})'.format(dbtp=get_db().table_prefix, pid=sql_exclude[k], ptype=TP_POLICY_ASSET, rtype=TP_HOST))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'state':
_where.append('h.state={}'.format(sql_filter[k]))
elif k == 'search':
_where.append('(h.name LIKE "%{filter}%" OR h.ip LIKE "%{filter}%" OR h.router_ip LIKE "%{filter}%" OR h.desc LIKE "%{filter}%" OR h.cid LIKE "%{filter}%")'.format(filter=sql_filter[k]))
elif k == 'host_group':
shg = SQL(get_db())
shg.select_from('group_map', ['mid'], alt_name='g')
shg.where('g.type={} AND g.gid={}'.format(TP_GROUP_HOST, sql_filter[k]))
err = shg.query()
if err != TPE_OK:
return err, 0, 1, []
if len(shg.recorder) == 0:
return TPE_OK, 0, 1, []
h_list = ','.join([str(i['mid']) for i in shg.recorder])
_where.append('h.id IN ({})'.format(h_list))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'ip' == sql_order['name']:
s.order_by('h.ip', _sort)
elif 'name' == sql_order['name']:
s.order_by('h.name', _sort)
elif 'os_type' == sql_order['name']:
s.order_by('h.os_type', _sort)
elif 'cid' == sql_order['name']:
s.order_by('h.cid', _sort)
elif 'state' == sql_order['name']:
s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.page_index, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get_role_list():
s = SQL(get_db())
s.select_from('role', ['id', 'name', 'privilege'], alt_name='r')
err = s.query()
return err, s.recorder
def get_group_with_member(sql_filter, sql_order, sql_limit):
"""
获取用户组列表，以及每个组的总成员数以及不超过5个的成员
"""
# 首先获取要查询的组的信息
sg = SQL(get_db())
sg.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
_where = list()
_where.append('g.type={}'.format(TP_GROUP_USER))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(g.name LIKE "%{filter}%" OR g.desc LIKE "%{filter}%")'.
format(filter=sql_filter[k]))
elif k == 'state':
_where.append(
'(g.state={filter})'.format(filter=sql_filter[k]))
if len(_where) > 0:
sg.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
sg.order_by('g.name', _sort)
elif 'state' == sql_order['name']:
sg.order_by('g.state', _sort)
else:
log.e('unknown order field.\n')
return TPE_PARAM, sg.total_count, sg.recorder
if len(sql_limit) > 0:
sg.limit(sql_limit['page_index'], sql_limit['per_page'])
err = sg.query()
if err != TPE_OK or len(sg.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
g['member_count'] = 0
g['members'] = []
g['_mid'] = [] # 临时使用，构建此组的前5个成员的id
# 对于本次要返回的用户组，取其中每一个组内成员的基本信息（id/用户名/真实名称等）
groups = [g['id'] for g in sg.recorder]
sgm = SQL(get_db())
sgm.select_from('group_map', ['gid', 'mid'], alt_name='gm')
# sgm.limit(0, 5)
_where = list()
# _where.append('({})'.format(' OR '.join(['gm.gid={}'.format(gid) for gid in groups])))
_where.append('gm.type={}'.format(TP_GROUP_USER))
_where.append('gm.gid IN ({})'.format(','.join(
[str(gid) for gid in groups])))
str_where = '( {} )'.format(' AND '.join(_where))
sgm.where(str_where)
err = sgm.query()
if err != TPE_OK or len(sgm.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
for gm in sgm.recorder:
if gm['gid'] == g['id']:
g['member_count'] += 1
if len(g['_mid']) < 5:
g['_mid'].append(gm['mid'])
# 将得到的用户id合并到列表中并去重，然后获取这些用户的信息
users = []
for g in sg.recorder:
users.extend(g['_mid'])
users = list(set(users))
su = SQL(get_db())
su.select_from('user', ['id', 'username', 'surname', 'email'],
alt_name='u')
su.where('u.id IN ({})'.format(','.join([str(uid) for uid in users])))
su.order_by('u.username')
err = su.query()
if err != TPE_OK or len(su.recorder) == 0:
return err, sg.total_count, sg.recorder
# 现在可以将具体的用户信息追加到组信息中了
for g in sg.recorder:
for u in su.recorder:
for m in g['_mid']:
if u['id'] == m:
g['members'].append(u)
return err, sg.total_count, sg.recorder
def get_group_with_member(sql_filter, sql_order, sql_limit):
"""
获取主机组列表，以及每个组的总成员数以及不超过5个的成员
"""
# 首先获取要查询的组的信息
sg = SQL(get_db())
sg.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
_where = list()
_where.append('g.type={}'.format(TP_GROUP_HOST))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append('(g.name LIKE "%{}%" OR g.desc LIKE "%{}%")'.format(sql_filter[k], sql_filter[k]))
elif k == 'state':
_where.append('(g.state={filter})'.format(filter=sql_filter[k]))
if len(_where) > 0:
sg.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'name' == sql_order['name']:
sg.order_by('g.name', _sort)
elif 'state' == sql_order['name']:
sg.order_by('g.state', _sort)
else:
log.e('unknown order field.\n')
return TPE_PARAM, sg.total_count, sg.recorder
if len(sql_limit) > 0:
sg.limit(sql_limit['page_index'], sql_limit['per_page'])
err = sg.query()
if err != TPE_OK or len(sg.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
g['member_count'] = 0
g['members'] = []
g['_mid'] = [] # 临时使用，构建此组的前5个成员的id
# 对于本次要返回的用户组，取其中每一个组内成员的基本信息（id/用户名/真实名称等）
groups = [g['id'] for g in sg.recorder]
sgm = SQL(get_db())
sgm.select_from('group_map', ['gid', 'mid'], alt_name='gm')
# sgm.limit(0, 5)
_where = list()
# _where.append('({})'.format(' OR '.join(['gm.gid={}'.format(gid) for gid in groups])))
_where.append('gm.type={}'.format(TP_GROUP_HOST))
_where.append('gm.gid IN ({})'.format(','.join([str(gid) for gid in groups])))
str_where = '( {} )'.format(' AND '.join(_where))
sgm.where(str_where)
err = sgm.query()
if err != TPE_OK or len(sgm.recorder) == 0:
return err, sg.total_count, sg.recorder
for g in sg.recorder:
for gm in sgm.recorder:
if gm['gid'] == g['id']:
g['member_count'] += 1
if len(g['_mid']) < 5:
g['_mid'].append(gm['mid'])
# 将得到的账号id合并到列表中并去重，然后获取这些账号的信息
users = []
for g in sg.recorder:
users.extend(g['_mid'])
users = list(set(users))
su = SQL(get_db())
su.select_from('host', ['id', 'os_type', 'name', 'ip', 'router_ip', 'router_port', 'cid'], alt_name='h')
su.where('h.id IN ({})'.format(','.join([str(uid) for uid in users])))
su.order_by('h.ip')
err = su.query()
if err != TPE_OK or len(su.recorder) == 0:
return err, sg.total_count, sg.recorder
# 现在可以将具体的用户信息追加到组信息中了
for g in sg.recorder:
for u in su.recorder:
for m in g['_mid']:
if u['id'] == m:
g['members'].append(u)
return err, sg.total_count, sg.recorder
def get_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_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 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_users(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('user', [
'id', 'type', 'auth_type', 'username', 'surname', 'role_id', 'state',
'email', 'last_login', 'valid_from', 'valid_to'
],
alt_name='u')
s.left_join('role', ['name', 'privilege'],
join_on='r.id=u.role_id',
alt_name='r',
out_map={'name': 'role'})
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_sql = 'u.id IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
_where.append(
_sql.format(dbtp=dbtp,
gtype=TP_GROUP_USER,
gid=sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'u.id NOT IN ('
'SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
''.format(dbtp=dbtp,
gtype=TP_GROUP_USER,
gid=sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'u.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
''.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_USER))
elif k == 'auditor_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp,
pid=sql_exclude[k],
ptype=TP_POLICY_OPERATOR,
rtype=TP_USER))
elif k == 'auditee_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp,
pid=sql_exclude[k],
ptype=TP_POLICY_ASSET,
rtype=TP_USER))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'role':
_where.append(
'u.role_id={filter}'.format(filter=sql_filter[k]))
elif k == 'type':
_where.append('u.type={filter}'.format(filter=sql_filter[k]))
elif k == 'state':
_where.append('u.state={filter}'.format(filter=sql_filter[k]))
elif k == 'search':
_where.append('('
'u.username LIKE "%{filter}%" '
'OR u.surname LIKE "%{filter}%" '
'OR u.email LIKE "%{filter}%" '
'OR u.desc LIKE "%{filter}%"'
')'.format(filter=sql_filter[k]))
if len(_where) > 0:
s.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('u.username', _sort)
elif 'surname' == sql_order['name']:
s.order_by('u.surname', _sort)
elif 'role_id' == sql_order['name']:
s.order_by('u.role_id', _sort)
elif 'state' == sql_order['name']:
s.order_by('u.state', _sort)
elif 'type' == sql_order['name']:
s.order_by('u.type', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, 0, 0, {}
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
